# 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**](https://docs.vultr.com/how-to-backup-and-restore-postgresql-databases-using-pgdump-and-pgrestore). * * * ## 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:** ```console sudo apt update ``` **2\. Install the PostgreSQL client:** ```console sudo apt install -y postgresql-client ``` ### Install PostgreSQL Client on Rocky Linux and AlmaLinux **1\. Install the PostgreSQL client on RPM-based distributions:** ```console sudo dnf install -y postgresql ``` ### Install PostgreSQL Client on Windows Visit the [PostgreSQL download page](https://www.postgresql.org/download/windows) 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`: ```plaintext pg_dump --host= --port= --username= --dbname= -f .dump ``` **1\. To back up a specific PostgreSQL database, use the following command.** Replace the placeholder values as needed: ```console 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:** ```console 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:** ```console 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`: ```plaintext pg_restore --host= --port= --username= --dbname= .dump ``` **1\. Connect to your PostgreSQL server using the** `psql` **client:** ```console 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:** ```psql 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:** ```psql postgres=# \q ``` **4\. Restore a single database using** `pg_restore`**:** ```console 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:** ```console 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:** ```psql postgres=# \c example_db ``` **7\. List all the tables to confirm that the restoration is completed:** ```psql postgres=# \dt ``` **8\. Verify that table data is available.** Replace `app_users` with the table name present in your database: ```psql postgres=# SELECT * FROM app_users; ``` **9\. Exit the PostgreSQL client:** ```psql 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](https://www.postgresql.org/docs/current/backup.html) for advanced backup strategies For the full guide with additional tips, visit the original article on [**Vultr Docs**](https://docs.vultr.com/how-to-backup-and-restore-postgresql-databases-using-pgdump-and-pgrestore).