Vultr DocsLatest Articles

How to Backup and Restore PostgreSQL Databases with pg_dump

PostgreSQLpg_dumpDatabase Backup
HM
Updated on November 21, 2023
How to Backup and Restore PostgreSQL Databases with pg_dump header image

Introduction

Pg_dump is a PostgreSQL backup utility tool that allows database administrators to export databases in all forms such as full, copy, incremental, and differential backups. Together with pg_dumpall, the tool can export one or many databases in a single command to either a SQL script file in formats such as .sql, .dump, or an archive file such as .tar, .tgz.

This article explains how you can backup PostgreSQL databases using pg_dump, or pg_dumpall, then restore the databases using the pg_restore tool on a local or remote server such as a Vultr managed database for PostgreSQL cluster.

Prerequisites

Before you begin, you may need to:

Example

In this section, you'll install the PostgreSQL client tool that activates the pg_dump utilities. Then, create an example database to use for the backup and restoration steps described in this article.

  1. Install the PostgreSQL client tool on your computer.

    Debian/Ubuntu

      $ sudo apt install postgresql-client

    CentOS/Rocky Linux

         $ sudo dnf install postgresql-client

    Windows

    Download and install the PostgreSQL installer for Windows.

  2. Check the installed pg_dump version.

      $ pg_dump --version

    Output:

      pg_dump (PostgreSQL) 9.5.25

  3. Log in to your PostgreSQL server. Replace 5432, example.vultrdb.com with your actual Vultr database details.

      $ psql --host=example.vultrdb.com --port 5432 --username=example-user --password --dbname=postgres

  4. Create an example database.

      CREATE DATABASE example_db;

  5. Add some example tables to the database. For example, the following command creates the table app_users.

      CREATE TABLE app_users (
      user_id serial PRIMARY KEY,
      username VARCHAR ( 50 ) NOT NULL,
      password VARCHAR ( 50 ) NOT NULL,
      email VARCHAR ( 255 )  NOT NULL
      );

  6. Exit the PostgreSQL client.

      \q

Backup the PostgreSQL Database

To perform PostgreSQL database backups, pg_dump uses the following syntax.

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

  1. Export the example database. Replace backup.dump with your desired filename.

      $ pg_dump -Fd -v --host=example.vultrdb.com --port=5432--username=example-user --dbname=example_db -f backup.dump

  2. To export all PostgreSQL databases available on the server, run the following command.

      $ pg_dumpall -Fd -v --host=example.vultrdb.com --port=5432 --username=example-user --dbname=example_db -f full-backup.dump

  3. When complete, verify that the backup file exists in your working directory.

      $ ls

Restore the PostgreSQL Database

To restore your PostgreSQL database, you can either use psql or pg_restore to import the database using the following syntax.

     pg_restore --host=[host] --port=<port> --username=[user] --dbname=[database-name] database.backup

  1. Log in to your PostgreSQL database server.

      $ psql --host=example.vultrdb.com --port 5432 --username=example-user --password --dbname=postgres

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

      CREATE DATABASE example_db;

    If you backed up a single database, you're required to re-create it (in case it does not exist) before restoring it to the server. For full backups, this is not required as all databases are automatically recreated and restored on the server.

  3. Exit the PostgreSQL client.

      \q

  4. Import the database backup file to the server.

      pg_restore --host=example.vultrdb.com --port=5432 --username=example-user --dbname=example_db backup.dump

  5. When complete, re-log in to the server and switch to the database.

      \c example_db

  6. Verify that all database tables restore successfully.

      \dt

  7. Verify that table data is available.

      SELECT * FROM app_users;

  8. Exit the PostgreSQL client.

      \q

Conclusion

In this article, you have backed up and restored a PostgreSQL database using the pg_dump tool. This process is useful when migrating from one PostgreSQL server to another, for example, when migrating your on-premises server to a Vultr managed database for PostgreSQL cluster. For more information, please visit the following resources: