We have a Postgres RDS instance on Amazon Web Services. We have automatic backups enabled, and we take snapshots on a daily basis. We would like to generate a local 'up-to-date' backup of the RDS instance that we can manage ourselves. Running pg_dump against the instance is not sufficient because we want to be able to restore the database to any point in time. We would prefer to have a local backup of RDS and all WAL files since that backup was taken. Questions:
Is it possible to access the WAL files and backups that RDS is automatically generating in its backup routine? This would be ideal. I would want to download a local copy of them. After initial investigation, I feel the answer to this question is 'no'. It sounds like RDS is storing its WAL files and backups in S3, but it makes them inaccessible to us. I would love confirmation.
Is there any other way to access transactions (WAL files) that have occurred on the RDS instance? I imagine we should be able to create a Postgres database on an EC2 and 'feed' transactions from our primary 'live' RDS instance into this EC2 instance. Once our EC2 instance is updated, we could pull WAL files from there. What a headache, though :/ Is this setup possible? What is the magic to 'feed' from our RDS instance to the EC2 instance so that it is always up to date?