Backup specific tables in AWS RDS Postgres Instance

Question

I have two databases on Amazon RDS, both Postgres. Database 1 and 2

I need to restore an instance from a snapshot of Database 1 for my Staging environment. (Database 2 is my current Staging DB).

However, I want the data from a few of the tables in Database 2 to overwrite the tables in the newly restored snapshot. What is the best way to do this?

Answer 1

When restoring RDS from a Snapshot, a new database instance is created. If you only wish to copy a portion of the snapshot:

• Restore the snapshot to a new (temporary) database
• Connect to the new database and dump the desired tables using pg_dump
• Connect to your staging server and restore the tables using pg_restore (most probably deleting any matching existing tables first)
• Delete the temporary database

pg_dump actually outputs SQL commands that are then used to recreate tables and restore data. Look at the content of a dump to understand how the restore process actually works.