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