Skip to content

Instantly share code, notes, and snippets.

Embed
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>

#####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/
@kinduff

This comment has been minimized.

Copy link

@kinduff kinduff commented Mar 17, 2018

Thanks for this

@adammoisa

This comment has been minimized.

Copy link

@adammoisa adammoisa commented May 15, 2018

Amazingly straightforward and helpful. Thank you!

@Ngahu

This comment has been minimized.

Copy link

@Ngahu Ngahu commented Jul 26, 2018

Really helpful.Thank you!

@rayashi

This comment has been minimized.

Copy link

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

This comment has been minimized.

Copy link

@rtsinani rtsinani commented Sep 18, 2018

💯

@hrehman200

This comment has been minimized.

Copy link

@hrehman200 hrehman200 commented Oct 24, 2018

Very helpful, thanks

@johnrlive

This comment has been minimized.

Copy link

@johnrlive 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.

@ReddyArun

This comment has been minimized.

Copy link

@ReddyArun ReddyArun commented Nov 24, 2018

Really helpful.Thank you!

@SREENATHPGS

This comment has been minimized.

Copy link

@SREENATHPGS SREENATHPGS commented Mar 5, 2019

Cool!!,

@mehta-m

This comment has been minimized.

Copy link

@mehta-m 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

This comment has been minimized.

Copy link

@agehret agehret commented May 22, 2019

Nice. Thanks.

@gSanchezCiarrocchi

This comment has been minimized.

Copy link

@gSanchezCiarrocchi gSanchezCiarrocchi commented Jun 14, 2019

Thanks

@golodenko

This comment has been minimized.

Copy link

@golodenko golodenko commented Sep 11, 2019

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

@mattbryanswan

This comment has been minimized.

Copy link

@mattbryanswan 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.

@Danilka

This comment has been minimized.

Copy link

@Danilka Danilka commented Dec 27, 2019

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

@neodcalgary

This comment has been minimized.

Copy link

@neodcalgary neodcalgary commented Feb 5, 2020

Works perfectly!

@AmarjagpreetS

This comment has been minimized.

Copy link

@AmarjagpreetS AmarjagpreetS commented Apr 20, 2020

Thanks guys. It worked.

@HemanthMudalaiah

This comment has been minimized.

Copy link

@HemanthMudalaiah HemanthMudalaiah commented Jun 5, 2020

Thanks, works perfectly.

@jcharnley

This comment has been minimized.

Copy link

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

@jonathanestabillo

This comment has been minimized.

Copy link

@jonathanestabillo jonathanestabillo commented Jul 1, 2020

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

@PoombavaiS

This comment has been minimized.

Copy link

@PoombavaiS 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?

@jcharnley

This comment has been minimized.

Copy link

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

@zahydo

This comment has been minimized.

Copy link

@zahydo zahydo commented Nov 11, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.