All Case Studies Design Development Interviews Machine Learning Project Management

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.

digital transformation
remote work best practices
Need a successful project?
Estimate project or contact us