[FIXED] How to take pg dump from testing server to my pc

Issue

I am trying to take pg dump from testing server to my local machine, i have been logged into my server

root@myproject-staging-development1:~/myproject/current#

Which command should i write in here to get pg dump into my local machine?

Solution

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

Source

Answered By - felunka

Answer Checked By - Cary Denson (PHPFixing Admin)