13

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?

Improve this question
2
  • 1
    Nope, that's a fine way of handling this use case.
    – AdamKG
    Oct 31, 2018 at 15:49
  • 1
    you can use --format=directory --jobs=... for fastening process
    – Sahap Asci
    Oct 31, 2018 at 17:22

1 Answer 1

Reset to default
20

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.

Improve this answer
2
  • But this doesn't stream right? So if the database is really large, like 100GB+ I suppose you want streaming. Consider github.com/jameshy/pgdump-aws-lambda
    – Christiaan Westerbeek
    Jun 3, 2022 at 16:44
  • 2
    It streams STDOUT from "pg_dump" to STDIN of "aws s3 cp -" (that's what the "-" means). To restore, use "aws s3 cp s3://... - | gunzip | psql"
    – GraduateOfAcmeU
    Jan 13 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.