How to Dump and Restore PostgreSQL Database

Marcin Górczyński

May 27, 2016 • 2 min read
postgres restore dump

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.

More posts by this author

Marcin Górczyński

A husband, a father and a developer with loads of love for every of those three things. Marcin is...
codestories