Postgresql – Best practice for securely backing up RDS Postgres “offsite”

amazon-rdsawsbackuppostgresql

We're running Postgres on RDS, which is largely great. The big issue with it, however, is to do with AWS' security model, which allows anyone with various permissions to delete everything – your DBs, your backups, the whole lot.

In particular, you can't prevent someone with access to create IAM users and groups from being able to then give a new user more permissions than they themselves have, so either compromised credentials or a disgruntled employee could destroy everything if you rely on RDS' own backups.

EDIT:

Just in case you're wondering what the issue might be, have a quick read of http://www.infoworld.com/article/2608076/data-center/murder-in-the-amazon-cloud.html

So, the "sensible" thing to do seems to be to have a separate AWS account on which you have basically no one having any access, and have a key which can write stuff up to S3 (and possibly read it back if you fancy, though this is probably optional).

This way, you can back things up to an account from which your main AWS admins can't, by accident or design, delete stuff, and then use lifecycle rules to manage it.

Sorry for the long build up – I am literally amazed that people don't seem to have asked/answered this before, as it seems such an obvious thing for almost anyone using RDS (or indeed just AWS) to need to do, but…

How do I backup Postgres in a sensible fashion for this?

Some things to consider:

  • Storage space and so on aren't infinite, so ideally don't want to be doing a full pg_dump/gzip/encrypt/upload to S3, which is the obvious solution, as it would mean hundreds of Gb a day going up there which is probably overkill.
  • We don't have access to the "core" servers, so can't do differential type backups as would be more "normal"

Would it work to do a pg_dump, then use some sort of diff program to only upload/store the diffs? Since I don't think pg_dump produces things in a specific order, I'm not sure this would work (in the way intended)?

Any other ideas?

I'm aware by the way of the use of manual snapshot sharing – https://aws.amazon.com/blogs/aws/amazon-rds-update-cross-account-snapshot-sharing/ – which works great if you're using unencrypted RDS – but we aren't, for reasons I can't fully explain except that "it sounds good".

Best Answer

I think your way of thinking with a separate machine in AWS which has access to the db is generally the right way to do it, and would just add that the "normal" way of making incremental backups (with any "real" database) is named PITR, for Point-in-time-recovery. If you search for this term and PostgreSQL, you will find tutorials on how to do generic incremental database backups, and it's up to you to implement them using AWS.