What's a good way to backup a (AWS) Postgres DB

Question

what's a good way to backup a Postgres DB (running on Amazon RDS). The built in snapshoting from RDS is by default daily and you can not export the snapshots. Besides that, it can take quite a long time to import a snapshot.

Is there a good service that takes dumps on a regular basis and stores them on e.g. S3? We don't want to spin up and maintain a ec2 instance which does that.

Thank you!

Answer 1

I want the backups to be automated, so I would prefer to have dedicated service for that.

Your choices:

• run pg_dump from an EC2 instance on a schedule. This is a great use case for Spot instances.
• restore a snapshot to a new RDS instance, then run pg_dump as above. This reduces database load.

Want to run a RDS snapshot more often than daily? Kick it off manually.

These are all automateable. For "free" (low effort on your part) you get daily snapshots. I agree, I wish they could be sent to S3.

Answer 2

SOLUTION: Now you can do a pg_dumpall and dump all Postgres databases on a single AWS RDS Instance.

It has caveats and so its better to read the post before going ahead and compiling your own version of pg_dumpall for this. Details here.