Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
How to copy production database on AWS RDS(postgresql) to local development database.
  1. Change your database RDS instance security group to allow your machine to access it.
    • Add your ip to the security group to acces the instance via Postgres.
  2. Make a copy of the database using pg_dump
    • $ pg_dump -h <public dns> -U <my username> -f <name of dump file .sql> <name of my database>
    • you will be asked for postgressql password.
    • a dump file(.sql) will be created
  3. Restore that dump file to your local database.
    • but you might need to drop the database and create it first
    • $ psql -U <postgresql username> -d <database name> -f <dump file that you want to restore>
    • the database is restored
  4. pg_restore -h <host> -U <username> -c -d <database name> <filename to be restored>


Copy link

mattbryanswan commented Sep 26, 2019

There are no such thing as security groups for DB instance.

There is indeed a security group for the VPC your RDS DB is attached to.

Once you're in the admin console, looking at your RDS instance, the third column under the "Connectivity & Security" tab will show and link to the current security group.

If you scroll down just a little more, it'll also actually display to you the current inbound and outbound rules on the current group.

Copy link

Danilka commented Dec 27, 2019

#2-3 worked great, thanks!
What is the #4 for though?

Copy link

neodcalgary commented Feb 5, 2020

Works perfectly!

Copy link

AmarjagpreetS commented Apr 20, 2020

Thanks guys. It worked.

Copy link

HemanthMudalaiah commented Jun 5, 2020

Thanks, works perfectly.

Copy link

jcharnley commented Jun 12, 2020

has anyone had issues relating to RDSAdmin when importing the dump file ?
Screenshot 2020-06-12 at 16 29 28

Copy link

jonathanestabillo commented Jul 1, 2020

Im stuck on "pg_dump: saving search_path =" :(

Copy link

PoombavaiS commented Sep 30, 2020

has anyone had issues relating to RDSAdmin when importing the dump file ?
Screenshot 2020-06-12 at 16 29 28

Have you solved it?

Copy link

jcharnley commented Sep 30, 2020

yeah, even tho there is an error it didnt stop the restore. You could manually add it I guess, but seems to be ok for me

Copy link

zahydo commented Nov 11, 2020

Excellent, but how can I do the same for a multi-tenant architecture with DB per tenant?

Copy link

Andrew-Chen-Wang commented Dec 9, 2020

Just a note: if you're using a custom VPC, make sure you enable "Publicly Accessible" in your DB instance and that your VPC has an internet gateway (by attaching the gateway to the VPC ID).

Future me: also make sure your route table is actually pointing to your internet gateway: because last time it was pointing to an old one that didn't allow me to connect to anything.

Copy link

burakuytun-rightsoft commented Jan 21, 2021

I get this error "pg_dump: error: could not open output file "test_dump_20210121.sql": Permission denied"

Any ideas?

Copy link

nikhilshinday commented Jan 21, 2021

Hey @burakuytun-rightsoft it might be because your current user doesn't have the right permissions. Try running sudo chmod 666 test_dump_20210121.sql.

Copy link

daphn3k commented Mar 4, 2021

Thanks for this. #4 was not needed.
The extra step I had to do before restoring the dump, was create the DB, as well as the users and roles associated with it.
Adding a user for rdsadmin is necessary if it's the owner of any objects in your DB. If not, then you should be fine ignoring the error.

PS. Depending on the size (and purpose) of your DB, it might make sense to change the location of postgres' data directory

Copy link

parikshit223933 commented Jun 17, 2022

Thanks @kinduff :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment