Its very important to backup and restore any kind of database. Suppose your testing server or production server have an issue but you can’t find the issue locally. So how can you check the issue and solve it. For solve the issue you need to backup the server database and restore it locally. After restoring you can check the issue and solve it. This problem have raised as your local and server configuration are not same always.

Database Backup

We will use pg_dump tool to backup database. pg_dump dumps a database as a text file or to other formats. It will dump all the contents of a selected database into a single file. We need to run pg_dump in the command line on the computer where the database is stored. So if the database is stored on a remote server, you will need to SSH to that server. Here I will discuss two of of backup database:

First Way

We backup database as a plain-text file format containing SQL script. Run the following command in the remote server terminal:

sudo pg_dump -U <db_user> -f </path/to/your/file/name_of_dump_file.sql> <db_name>

Here,

  • -U to specify which user will connect to the PostgreSQL database server.
  • db_user means database username
  • -f is used to specify the output format of file.
  • db_name means database name
  • name_of_dump_file means backup database file name
  • .sql means backup database file as plain-text file containing SQL script.

If your db_user is khan, db_name is test_db and want to save the database in Downloads folder then

sudo pg_dump -U khan -W -F t test_db > /home/khan/Downloads/bk_test_db_02_05_2018.sql

bk_test_db_02_05_2018.sql contains all the SQL queries that are required to restore your database. You will find your dump file in the path you provide. Database backup part is done, lets move on next restoring part.

Second Way

Run the following command in the remote server terminal:

sudo pg_dump -U db_user -W -F t db_name > /path/to/your/file/name_of_dump_file.tar

Here,

  • -U to specify which user will connect to the PostgreSQL database server.
  • db_user means database username
  • -W will force pg_dump to prompt for a password before connecting to the server.
  • -F is used to specify the format of the output file, which can be one of the following:
    • p - plain-text SQL script
    • c - custom-format archive
    • d - directory-format archive
    • t - tar-format archive
  • db_name means database name
  • name_of_dump_file.tar means backup database file name

To see list of command for pg_dump use pg_dump -? in the terminal

If your db_user is khan, db_name is test_db and want to save the database in Downloads folder then

sudo pg_dump -U khan -W -F t test_db > /home/khan/Downloads/bk_test_db_02_05_2018.tar

bk_test_db_02_05_2018.tar contains all the SQL queries that are required to restore your database. You will find your dump file in the path you provide.

Backup Database Copy to Local

To restore database we need to copy the remote server backup dump file locally. For this run the following command in the local terminal:

scp username@remote:/file/to/send /where/to/put

If your username is user and remote server is 171.16.220.227 then

scp user@171.16.220.227:/home/khan/Downloads/bk_test_db_02_05_2018.sql /home/khan/Downloads

Now go to your Downloads folder you will see this bk_test_db_02_05_2018.sql file.

Database Restore

There are two ways to restore a PostgreSQL database:

  • psql for restoring from a plain SQL script file created with pg_dump,
  • pg_restore for restoring from a .tar file, directory, or custom format created with pg_dump.

First Way: Restore a database with psql

Run the following command in the local terminal to restore your plain-text file containing SQL script:

sudo psql -U <db_user> -d <new_created_db_name> -f </path/to/your/file/dump file that you want to restore>

Here,

  • -U to specify which user will connect to the PostgreSQL database server.
  • db_user means database username
  • -d to specify which new created database you will restore your backup database.
  • -f is used to specify the output format of file.

If your db_user is khan, newly created db is new_test_db and backup file is bk_test_db_02_05_2018.sql then run following command

sudo psql -U khan -d new_test_db -f /home/khan/Downloads/bk_test_db_02_05_2018.sql

After entering the command you will see some tamil/telegu programming UI. Now go to your pgadmin view and you will see the restore database.

Second Way: Restore a database with pg_restore

Here we use pg_restore to restore our database locally. If you choose custom, directory, or archive format when creating a backup file, then you will need to use pg_restore in order to restore your database. Using the following command locally:

sudo pg_restore -d <new_created_db_name> </path/to/your/file/dump file that you want to restore> -c -U <db_user>

Here,

  • new_created_db_name means new created database where you wanna store backup database

If you use pg_restore you have various options available, for example:

  • -c to drop database objects before recreating them,
  • -C to create a database before restoring into it,
  • -e exit if an error has encountered,
  • -F format to specify the format of the archive.

Use pg_restore -? to get the full list of available options.

Restore our bk_test_db_02_05_2018.tar database command will be

sudo pg_restore -d new_test_db /home/user/Downloads/bk_database_02_05_2018.tar -c -U user

Now go to your management tool for PostgreSQL like pgadmin, [datagrip](https://www.jetbrains.com/datagrip/ “”) view and you will see the restore database.

See the next tutorial How to Backup and Restore a AWS RDS PostgreSQL Database

Some useful link that I have followed:

  • https://www.axiomq.com/blog/backup-and-restore-a-postresql-database/

Note: If you have any query or any upgradation of my writing or any mistakes please comment and suggest me. You are warmly welcomed always.