All Case Studies Design Development Interviews Our Way Project Management

How To Dump And Restore PostgreSQL Database

Oftentimes, when working with production/staging environments, you may encounter differences with your localhost database setup causing your app to crash. The easiest way to check if it's database to blame is to dump staging/production database and restore it on localhost.

 

Dump your PostgreSQL database

 

Step 1

SSH to staging/production server

Step 2

Dump 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 given file to local directory specified by you. If you give no path to local directory, 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 current localhost database, you must drop it first:
psql template1 -c 'drop database database_name;'

Step 2

Create new database on localhost:

psql template1 -c 'create database database_name with owner your_user_name;

Step 3

And write your dump into database:

psql database_name < database_name_20160527.sql

 

That's all! You now have the exact copy of production database available on your machine.

Follow Netguru
Join our Newsletter

READ ALSO FROM PostgreSQL
Read also
Need a successful project?
Estimate project or contact us