How to Backup and Restore 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, .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 article explains how to back up PostgreSQL databases using pg_dump and pg_dumpall, and how to restore them using the pg_restore tool on a local or remote PostgreSQL server.

Prerequisites

Before you begin, you need to:

• Have access to an existing PostgreSQL server.

Install PostgreSQL Client

In this section, you will 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 allow you to interact with remote or local PostgreSQL servers, export data in various formats, and perform database migrations or recovery tasks efficiently.

Backup the PostgreSQL Database

You can 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.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   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.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   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.

   CONSOLE

   Copy

   $ ls

   Explain Code

   Powered by Vultr AgentBeta

Restore the PostgreSQL Database

You can 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.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

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

   PSQL

   Copy

   postgres=# CREATE DATABASE example_db;

   Explain Code

   Powered by Vultr AgentBeta

   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 is not necessary as all databases are recreated automatically.

3. Exit the PostgreSQL prompt.

   PSQL

   Copy

   postgres=# \q

   Explain Code

   Powered by Vultr AgentBeta

4. Restore a single database using pg_restore.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

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

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   In this 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.

   PSQL

   Copy

   postgres=# \c example_db

   Explain Code

   Powered by Vultr AgentBeta

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

   PSQL

   Copy

   postgres=# \dt

   Explain Code

   Powered by Vultr AgentBeta

8. Verify that table data is available.

   PSQL

   Copy

   postgres=# SELECT * FROM app_users;

   Explain Code

   Powered by Vultr AgentBeta

   Replace app_users with the table name present in your database.

9. Exit the PostgreSQL client.

   PSQL

   Copy

   postgres=# \q

   Explain Code

   Powered by Vultr AgentBeta

Conclusion

In this article, you learned how to back up and restore PostgreSQL databases using the pg_dump, pg_dumpall, and pg_restore utilities. These tools offer a reliable way to export individual databases or entire PostgreSQL clusters for migration, testing, or disaster recovery. You installed the PostgreSQL client, performed single and full database backups, and restored data on a target server using both custom and plain SQL formats. For advanced backup strategies, refer to the official PostgreSQL Documentation.