Skip to content

Instantly share code, notes, and snippets.

@syafiqfaiz

syafiqfaiz/how-to-copy-aws-rds-to-local.md

Last active October 8, 2025 11:34
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.
Download ZIP
How to copy production database on AWS RDS(postgresql) to local development database.
Raw
how-to-copy-aws-rds-to-local.md
  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:[email protected]/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