How to Dump and Restore PostgreSQL Database

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

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.