PostgreSQL is undoubtedly one of the most popular and efficient open-source relational database management system powering millions of applications.

These days a database backup mechanism is essential for any project onboard. Luckily PostgreSQL comes with built-in utilities for creating and restoring backups to make our life simpler.

Backup and Restore Databases in PostgreSQL

PostgreSQL allows a great degree of flexibility for backup and restoration of data, In this article one by one we will go through the most used utilities.

Backing up a database in a tar file

The pg_dump utility is used for backing up data of a single database or table in PostgreSQL.

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

Here we used the following options:

• -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:
  1. p – plain-text SQL script
  2. c – custom-format archive
  3. d – directory-format archive
  4. t – tar-format archive

In case you encounter the following error.

 pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL: Peer authentication failed for user "postgres"

Please follow this guide - How To Fix – FATAL: Peer authentication failed for user “postgres” Error

Restoring a database from a tar file

Similarly, pg_restore utility is used for restoring a database from a dump created by pg_dump.

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

The -C flag is for creating a database before restoring data into it.

Backing up a database in SQL format

A tar backup may cause problems while restoring the dump to a different version of PostgreSQL. Therefore I always recommend taking backups in-plane SQL format.

pg_dump -U db_user database_name > path/to/backup.sql

Restoring a database from SQL file

SQL files can be restored using psql as follows.

 psql -U db_user < path/to/backup.sql

Backing up a specific table

Postgres also allow us to backup specific tables instead of the entire database.

pg_dump -U db_user -d db_name -t table_name > path/to/backup.sql

Restoring a table from SQL file

psql -U db_user -d db_name < path/to/backup.sql

Backing up all the databases at once

We can also backup all the database belonging to a user using the pg_dumpall utility.

pg_dumpall -U db_user > path/to/backup.sql

Restoring all the databases from the backup file

psql -U db_user < path/to/backup.sql

Backing up a Remote database

pg_dump -h host_name -p port_number db_name > path/to/backup.sql

Restoring a Remote database

psql -h host_name -p port_number db_name < path/to/backup.sql

Take a compressed backup of PostgreSQL database

pg_dump -U db_user -d db_name | gzip > path/to/backup.sql.gz

Restoring from a compressed backup

gunzip -c path/to/backup.sql.gz | psql -U db_user -d db_name