Skip to content

Instantly share code, notes, and snippets.

@syafiqfaiz
Last active December 8, 2024 22:08
Show Gist options
  • Save syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b to your computer and use it in GitHub Desktop.
Save syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b to your computer and use it in GitHub Desktop.
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>

#####ref

  1. http://stackoverflow.com/questions/31881786/how-to-pg-dump-an-rds-postgres-database
  2. http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/
@rayashi
Copy link

rayashi commented Jul 31, 2018

How can I proceed this step "Add your ip to the security group to acces the instance via Postgres" ?

@rtsinani
Copy link

💯

@hrehman200
Copy link

Very helpful, thanks

@johnrlive
Copy link

johnrlive commented Nov 7, 2018

How can I proceed this step "Add your ip to the security group to access the instance via Postgres" ?

The IP's are added on the security group tied to the VPC that the RDS is attached to.

@arunk-r
Copy link

arunk-r commented Nov 24, 2018

Really helpful.Thank you!

@SREENATHPGS
Copy link

Cool!!,

@mehta-m
Copy link

mehta-m commented Mar 28, 2019

The format of the pg_dump command that worked for me is -
pg_dump -h <public dns> -p <port> -U <my username> -f <name of dump file .sql> <name of my database>

@agehret
Copy link

agehret commented May 22, 2019

Nice. Thanks.

@gSanchezCiarrocchi
Copy link

Thanks

@nik-holo
Copy link

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

@mattbryanswan
Copy link

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.

@Danilka
Copy link

Danilka commented Dec 27, 2019

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

@neodcalgary
Copy link

Works perfectly!

@AmarjagpreetS
Copy link

Thanks guys. It worked.

@HemanthMudalaiah
Copy link

Thanks, works perfectly.

@jcharnley
Copy link

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

@jonathanestabillo
Copy link

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

@PoombavaiS
Copy link

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

Have you solved it?

@jcharnley
Copy link

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

@zahydo
Copy link

zahydo commented Nov 11, 2020

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

@Andrew-Chen-Wang
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: https://aws.amazon.com/premiumsupport/knowledge-center/instance-vpc-troubleshoot/ because last time it was pointing to an old one that didn't allow me to connect to anything.

@burakuytun-rightsoft
Copy link

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

Any ideas?

@nikhilshinday
Copy link

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.

@daphn3k
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

@parikshit223933
Copy link

Thanks @kinduff :)

@tre2man
Copy link

tre2man commented Sep 27, 2022

👍

@rogerfar
Copy link

If you want to restore a dump into a differently named database, make sure not use the --create option on pg_dump as it will force create the DB under that name.

If you use the --format c flag you can use pg_restore and save some bandwidth.

psql -h localhost -U user -d postgres -c "DROP DATABASE IF EXISTS newdb (FORCE)"
psql -h localhost -U user -d postgres -c "CREATE DATABASE newdb WITH OWNER = postgres ENCODING = 'UTF8'"
pg_dump --format c --blobs --no-owner --no-acl --quote-all-identifiers --verbose --dbname=postgresql://user:password@domain.rds.amazonaws.com/databasename --file outputfile.bak
pg_restore --verbose -h localhost -U user -d newdb outputfile.bak

@niz11
Copy link

niz11 commented Jan 16, 2023

Could add here the step to close all connections to the current running DB, before deletion:
psql -h localhost -U user -d postgres -c "select pg_terminate_backend(pid) from pg_stat_activity where datname='newdb'"

@swapnilj01
Copy link

I am getting "AccessShareLock" on a few tables when I try to generate the dump as there are multiple active connections to the db. Is there any way to clone it to a read replica even when the connections are active?

@JanKanis
Copy link

@swapnilj01 You could try passing the --serializable-deferrable option to pg_dump. That gives you a consistent read-only snapshot of the database. I'm not entirely sure how that interacts with locks but I expect it should work.

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