Best way to pg_dump PostgreSQL on RDS to S3

I need to dump my PostgreSQL on RDS to a file on S3 periodically (to use it elsewhere than AWS).

Is there a better way than running a script that runs pg_dump and uploads results to S3 using e.g. boto3?

You can directly upload the (gzipped) pg_dump results to s3 using the aws cli:

pg_dump -Z 9 -v -h ${DATABASE_HOST} -U ${DATABASE_USER} -d ${DATABASE_NAME} | aws s3 cp --storage-class STANDARD_IA --sse aws:kms - s3://my-bucket/dump.sql.gz

At work we wrapped this in a script which additionally fetches the required credentials from parameter store and then stores the backup in s3. The script runs within a docker container as a scheduled ecs task. Maybe the code in this repository will help you.

StackExchange Database Administrators Q#221454, answer score: 22