Backing Up and Restoring PostgreSQL Databases Using pg_dump and pg_restore

pg_dump is a PostgreSQL backup utility that allows database administrators to export databases in various forms, including full, copy, incremental, and differential backups. Alongside pg_dumpall, this tool can export one or multiple databases in a single command, saving the output as a SQL script such as .sql, .dump, or as an archive file such as .tar or .tgz. These backups can be used to migrate databases between servers, recover from failures, or create point-in-time snapshots for development and testing. This guide covers backing up PostgreSQL databases using pg_dump and pg_dumpall, and restoring them using pg_restore on a local or remote PostgreSQL server. By the end, you'll have working backup and restore workflows for both individual databases and full PostgreSQL clusters, following PostgreSQL backup and recovery practices documented in Vultr Docs.

Prerequisites

• Have access to an existing PostgreSQL server.

Install PostgreSQL Client

Install the PostgreSQL client, which includes essential command-line tools such as pg_dump and pg_restore used for backing up and restoring PostgreSQL databases. These tools let you interact with remote or local PostgreSQL servers, export data in various formats, and perform database migrations or recovery tasks efficiently.

Install PostgreSQL Client on Ubuntu and Debian

1. Update the APT package index:

sudo apt update

2. Install the PostgreSQL client:

sudo apt install -y postgresql-client

Install PostgreSQL Client on Rocky Linux and AlmaLinux

1. Install the PostgreSQL client on RPM-based distributions:

sudo dnf install -y postgresql

Install PostgreSQL Client on Windows

Visit the PostgreSQL download page and download the latest PostgreSQL client compatible with your Windows system. The installer includes pg_dump, pg_restore, and other command-line tools.

Backup the PostgreSQL Database

Use the pg_dump and pg_dumpall utilities to create backups of individual databases or all databases on a PostgreSQL server. Below is the general syntax for pg_dump:

pg_dump <options> --host=<database-server-host> --port=<port> --username=<user> --dbname=<database-name> -f <database-name>.dump

1. To back up a specific PostgreSQL database, use the following command. Replace the placeholder values as needed:

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

In the above command:

• -Fd: Specifies the directory format for the output (you can use -Fc for a compressed custom format).

• -v: Enables verbose output.

• -f: Specifies the output file or directory.

2. To back up all databases on the PostgreSQL server, use the pg_dumpall utility:

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

Note: pg_dumpall only supports plain SQL format. For custom formats or parallel dumps, use pg_dump individually per database.

3. After the backup completes, verify that the backup file or directory exists in your working location:

ls

Restore the PostgreSQL Database

Use pg_restore or psql to restore PostgreSQL database backups, depending on the format of your backup. Below is the general syntax for pg_restore:

pg_restore --host=<database-server-host> --port=<port> --username=<user> --dbname=<database-name> <database-name>.dump

1. Connect to your PostgreSQL server using the psql client:

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

2. Create a new target database with the same name as the source database:

postgres=# CREATE DATABASE example_db;

Note: If you're restoring a single database backup, you must manually create the target database before running pg_restore. For full backups using pg_dumpall, this step isn't necessary, since all databases are recreated automatically.

3. Exit the PostgreSQL prompt:

postgres=# \q

4. Restore a single database using pg_restore:

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

5. To restore all databases from a pg_dumpall full backup, use the psql utility:

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

In the above command:

• -f: Specifies the SQL file to restore.

• --username: A superuser is typically required to restore roles and permissions.

• This command restores all databases and roles as they existed during the backup.

Note: Ensure the target PostgreSQL database server is empty or does not contain conflicting roles or databases before restoring a pg_dumpall backup.

6. After the database restoration is complete, connect to the PostgreSQL server and switch to the restored database to verify the restoration:

postgres=# \c example_db

7. List all the tables to confirm that the restoration is completed:

postgres=# \dt

8. Verify that table data is available. Replace app_users with the table name present in your database:

postgres=# SELECT * FROM app_users;

9. Exit the PostgreSQL client:

postgres=# \q

Next Steps

You now have working backup and restore workflows for PostgreSQL using pg_dump, pg_dumpall, and pg_restore. From here, you can:

• Schedule regular backups with cron and store the output on remote or object storage

• Automate backup verification by periodically restoring dumps to a scratch database

• Explore parallel dumps and restores with pg_dump -j and pg_restore -j for larger databases

• Review the official PostgreSQL Documentation for advanced backup strategies

For the full guide with additional tips, visit the original article on Vultr Docs.