How to Dump and Restore PostgreSQL Database
Oftentimes, when working with the production/staging environments, you may encounter differences with your localhost database setup causing crashes in your app.
The easiest way to check if it's the database to be blamed is to dump the staging/production database and restore it on the localhost.
Dump Your PostgreSQL Database
Step 1
SSH to the staging/production server.
Step 2
Dump the desired database:
pg_dump database_name > database_name_20160527.sql
You can name your dump as you wish - I'm using dates to distinguish multiple dumps.
Step 3
Leave SSH and download your new SQL file using SCP.
scp login@host:path_to_dir_with/database_name_20160527.sql database_name_20160527.sql
This command logs you into your remote server using SSH and downloads a given file to the local directory specified by you. If you give no path to the local directory, the dump will be saved in your current working dir.
Example:
scp marcin@8.8.8.8:/home/my_app/backups/my_app_database_20160527.sql my_app_database_20160527.sql
Restore Your PostgreSQL Dump
Step 1
If you want to use the current localhost database, you must drop it first:psql template1 -c 'drop database database_name;'
Step 2
Create a new database on the localhost:
psql template1 -c 'create database database_name with owner your_user_name;
Step 3
And write your dump into the database:
psql database_name < database_name_20160527.sql
That's all! You now have the exact copy of production database available on your machine.
