ruby-on-railspostgresqlubuntupgpg-dump

How to take pg dump from testing server to my pc

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