DEV Community

Cover image for Backing Up and Restoring PostgreSQL Databases Using pg_dump and pg_restore
Vultr profile image Sanskriti Harmukh
Sanskriti Harmukh for Vultr

Posted on with Aashish Chaurasiya Originally published at docs.vultr.com

Backing Up and Restoring PostgreSQL Databases Using pg_dump and pg_restore

#postgres #backup #database #devops

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.

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
Enter fullscreen mode Exit fullscreen mode

2. Install the PostgreSQL client:

$ sudo apt install -y postgresql-client
Enter fullscreen mode Exit fullscreen mode

Install PostgreSQL Client on Rocky Linux and AlmaLinux

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

$ sudo dnf install -y postgresql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

$ psql --host=db.example.com --port=5432 --username=db_user --dbname=postgres
Enter fullscreen mode Exit fullscreen mode

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

postgres=# CREATE DATABASE example_db;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

4. Restore a single database using pg_restore:

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

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

postgres=# \dt
Enter fullscreen mode Exit fullscreen mode

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

postgres=# SELECT * FROM app_users;
Enter fullscreen mode Exit fullscreen mode

9. Exit the PostgreSQL client:

postgres=# \q
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)