Postgresql – Best way to create a daily snapshotted read-only db in RDS

amazon-rdsawspostgresqlreplicationsnapshot

We have a PostgreSQL db instance in RDS. In our application we need to run a daily export in the early hours of every day, the export and transformation may take some time and one issue we've found is it can export incorrect data if the data is being modified while the export is running (we use limit/offset as our table has ~100 million rows). This is probably a common issue.

To get over this we want to create a snapshot of our instance at midnight and use this for the export.

What's the best way to do this in RDS? Ideally the snapshot shouldn't take too long to create.

I've researched some things to try:

  • I tried to create a stored procedure to duplicate tables, but then it takes time to reindex etc, and the table name has to be different (which I want to keep the same so the query, which is used in other areas of the app, doesn't have to change).
  • RDS has read only replicas, but from brief reading they have a lag time so it seems to me they are constantly updated from the master instance. Is there any way to stop updates, or update once a day at midnight?
  • We have daily snapshots ran at midnight already, and I think we could create an AWS lambda to restore this snapshot (which I believe creates a new instance). It seems clunkier than the read-only replica but doable.
  • Use pg_dump, but this seems more work than using the AWS snapshot.

Best Answer

What's wrong with bullet 3? Schedule a Lambda function written in your preferred language to run at midnight that calls the RDS API to perform a snapshot.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html

You want this part near the bottom:

https://rds.us-east-1.amazonaws.com/
    ?Action=CreateDBSnapshot
    &DBInstanceIdentifier=mydbinstance
    &DBSnapshotIdentifier=mydbsnapshot
    &SignatureMethod=HmacSHA256
    &SignatureVersion=4
    &Version=2013-09-09
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140423/us-east-1/rds/aws4_request
    &X-Amz-Date=20140423T161105Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=e9649af6edcfbab4016f04d72e1b7fc16d8734c37477afcf25b3def625484ed2
Related Question