I've been finding myself doing restores from db backups to my local development more often recently as I've been building dashboard pages for a client and I want to have real data locally so I can get a better idea of what it will look like in production.

I also have a bunch of projects in production with real users and it's good to test-restore their backups at least once a month to make sure the backups can actually be restored.

I'm just adding these notes here for a quick reference, so this is really more a note to myself.

Note that depending on how you ran your backup with pg_dump, you may either need to use psql (if the backup file is in plain-text SQL-format) or pg_restore (for a custom format, e.g. when you pass in -Fc to pg_dump).

Restoring database on the same machine

psql

psql -U username -d my_db -f backup_file.sql

pg_restore

Note: The -c (clean) flag will drop existing database objects before restoring.

export PGPASSWORD=my_db_password && pg_restore -U username -c -d my_db my_db.dump

Restoring database from a remote machine

psql

psql -h db_host -U username -d my_db -f backup_file.sql

pg_restore

Note: The -c (clean) flag will drop existing database objects before restoring.

export PGPASSWORD=my_db_password && pg_restore -h db_host -U username -c -d my_db my_db.dump