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 commented Mar 17, 2018

Thanks for this

@adammoisa

This comment has been minimized.

Copy link

adammoisa commented May 15, 2018

Amazingly straightforward and helpful. Thank you!

@Ngahu

This comment has been minimized.

Copy link

Ngahu commented Jul 26, 2018

Really helpful.Thank you!

@rayashi

This comment has been minimized.

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

This comment has been minimized.

Copy link

rtsinani commented Sep 18, 2018

💯

@hrehman200

This comment has been minimized.

Copy link

hrehman200 commented Oct 24, 2018

Very helpful, thanks

@johnrlive

This comment has been minimized.

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.

@ReddyArun

This comment has been minimized.

Copy link

ReddyArun commented Nov 24, 2018

Really helpful.Thank you!

@SREENATHPGS

This comment has been minimized.

Copy link

SREENATHPGS commented Mar 5, 2019

Cool!!,

@mehta-m

This comment has been minimized.

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

This comment has been minimized.

Copy link

agehret commented May 22, 2019

Nice. Thanks.

@gSanchezCiarrocchi

This comment has been minimized.

Copy link

gSanchezCiarrocchi commented Jun 14, 2019

Thanks

@golodenko

This comment has been minimized.

Copy link

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

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.