PostgreSQL Backup and Restore with pg_dump & pg_restore

pg_dump is a PostgreSQL utility that enables administrators to export databases in multiple formats such as full, copy, incremental, and differential backups. Together with pg_dumpall, this tool can export one or several databases in a single command, generating a SQL script like .sql, .dump, or an archive file such as .tar or .tgz. These backups are useful for migrating databases across servers, recovering after failures, or creating snapshots for testing and development.

This guide outlines how to back up PostgreSQL databases using pg_dump and pg_dumpall, and how to restore them with pg_restore on both local and remote PostgreSQL servers.

Prerequisites

Before you proceed, make sure you:

• Have access to an existing PostgreSQL server.

Install the PostgreSQL Client

This section shows how to install the PostgreSQL client, which includes command-line tools like pg_dump and pg_restore for backing up and restoring PostgreSQL databases. These utilities allow you to connect to local or remote servers, export data in different formats, and handle migrations or recovery tasks effectively.

Follow these steps for APT-based systems such as Ubuntu and Debian:

Update the APT package index.

$ sudo apt install -y postgresql-client

Install the PostgreSQL client.

$ sudo apt install -y postgresql-client

Run the following command on RPM-based systems like Rocky Linux and AlmaLinux:

$ sudo dnf install -y postgresql

On Windows, visit the PostgreSQL download page and get the latest PostgreSQL client installer. It includes pg_dump, pg_restore, and other command-line utilities.

Back Up a PostgreSQL Database

Use pg_dump and pg_dumpall to back up individual databases or all databases on a PostgreSQL server. General syntax for pg_dump is as follows:

pg_dump --host= --port= --username= --dbname= -f .dump

To back up a specific PostgreSQL database, use the following command and adjust placeholders as needed:

$ pg_dump -Fd -v --host=db.example.com --port=5432 --username=db_user --dbname=example_db -f example_db.dump

Explanation of parameters:

• -Fd: Outputs in directory format (use -Fc for compressed format).
• -v: Activates verbose output.
• -f: Defines the output file or directory.

To back up all databases, run pg_dumpall:

$ pg_dumpall -v --host=db.example.com --port=5432 --username=db_user > full-backup.sql

Note: pg_dumpall only supports plain SQL format. For parallel or custom backups, use pg_dump individually for each database.

After completion, confirm that the backup file exists:

$ ls

Restore a PostgreSQL Database

To restore backups, use pg_restore or psql depending on the backup format. General syntax for pg_restore:

pg_restore --host= --port= --username= --dbname= .dump

Connect to your PostgreSQL server with psql:

$ psql --host=db.example.com --port=5432 --username=db_user --dbname=postgres

Create a target database:

postgres=# CREATE DATABASE example_db;

Note: For single database backups, you need to create the target database manually. For pg_dumpall backups, databases are recreated automatically.

Exit PostgreSQL prompt:

postgres=# \q

Restore a single database with pg_restore:

$ pg_restore --host=db.example.com --port=5432 --username=db_user --dbname=example_db backup.dump

Restore all databases from pg_dumpall backup using psql:

$ psql --host=db.example.com --port=5432 --username=db_user -f full-backup.sql

Explanation:

• -f: Defines the SQL file to restore.
• --username: A superuser is generally required to restore roles and permissions.

Note: Make sure the target server is clean and has no conflicting roles or databases before restoring a pg_dumpall backup.

After restoration, connect and switch to the restored database:

postgres=# \c example_db

List tables to confirm restoration:

postgres=# \dt

Verify table data:

postgres=# SELECT * FROM app_users;

Replace app_users with your actual table name.

Exit PostgreSQL client:

postgres=# \q

Conclusion

This guide demonstrated how to back up and restore PostgreSQL databases using pg_dump, pg_dumpall, and pg_restore. These tools provide reliable methods for exporting individual databases or full clusters for migration, testing, and disaster recovery. You installed the PostgreSQL client, created both single and complete backups, and restored data using custom and plain SQL formats. For more advanced strategies, consult the official PostgreSQL Documentation.