Backup and Restore a Postgres Database Using pg_dump and psql

Backing up and restoring a postgreSQL database is easy from the CLI. Some people prefer using pgAdmin III however through the GUI and over a network will use a lot more resources so it is recommended to backup and restore via your Linux CLI. Follow the simple steps below to backup and restore one Postgres database from a Linux server to another Linux server.

1. Backup One PostgreSQL Database: Login to your server and issue the below command from the CLI. In the below syntax change username to the correct username (such as postgres), the database_name to the proper database, and the filename to something meaningful. The filename should end in .sql so you know it is a postgreSQL backup file which will eventually end in .gz once you compress it using gzip.
   bash

   
   

   1. pg_dump -U username db_name -f filename
2. Compress the Database Backup: use gzip to compress the database backup.
   bash

   
   

   1. gzip filename

   If the above file was originally named db-backup_01-01-2009.sql it will be named db-backup_01-01-2009.sql.gz after it is compressed using gzip.

3. Move the Database Backup: Using scp copy the file from the current server to the server you want to restore the database file on.
   bash

   
   

   1. scp filename user@server:/path/filename

   You will be required to enter a password to start the file copy process which will happen over an encrypted connection.

4. Restore the PostgreSQL Database: Now on the new server uncompress and then restore the database using the below syntax.
   bash

   
   

   1. gzip -d filename
   2. psql -U username -d destination_db -f filename

   This will restore the contents of the backup file into the database you specify after the -d switch.

You have now backed up a database on one server and restored it on another server. Using the same philosophy you can create shell scripts to automate this process and even store backups for various amounts of time so your data is always protected.