Backup Your PostgreSQL Database with pg_dump

It's always good to have a backup of your database handy.

One of the easiest ways to produce a backup is using pg_dump.

This short guide will walk you through the process of using pg_dump to back up your PostgreSQL database.

Prerequisites and Installation

For non-Mac users you'll have to find another guide on installing PostgreSQL. But for Mac users:

Ensure you have Homebrew installed on your MacOS, as it simplifies the installation of PostgreSQL and pg_dump. Run brew --version to check. If Homebrew is not installed, visit Homebrew's official site for installation instructions.

Install PostgreSQL

If pg_dump is not already available, you'll likely need to install PostgreSQL. Install it via Homebrew with the following command:

brew install postgresql

After installation, verify that pg_dump is ready for use by checking its version:

pg_dump --version

If the command does not work, ensure PostgreSQL's binary directory is in your system's PATH by adding it to your shell profile (~/.zshrc, etc.).

Taking a Backup with pg_dump

To back up your PostgreSQL database, execute the following command in your terminal:

pg_dump "postgresql://<username>@<hostname>:5432/<database_name>" -F c -b -v -f "backup.dump"

This command will prompt you for the database's password. See the next section to automate the process without password prompts.

Replace <username>, <hostname>, <database_name> with your database's specific details. The filename can be whatever you like in this example, I chose backup.dump just for clarity.

Example

For a database named mydb hosted locally with a user admin:

pg_dump "postgresql://admin@localhost:5432/mydb" -F c -b -v -f "mydb_backup.dump"

Now that you have backups let's look at some of those command line options and some other useful ones:

Key pg_dump Command Arguments

• -F, --format=format: Specifies the output format of the dump. Options include:
  • p (plain): Outputs a plain-text SQL script file.
  • c (custom): Outputs a custom-format archive suitable for input into pg_restore.
  • d (directory): Outputs a directory-format archive suitable for input into pg_restore, supports parallel dumps.
  • t (tar): Outputs a tar-format archive suitable for input into pg_restore.
• -j, --jobs=njobs: Enables parallel dumping by dumping njobs tables simultaneously. Applicable only with the directory format.
• -a, --data-only: Dumps only the data, excluding the schema (data definitions).
• -s, --schema-only: Dumps only the schema, excluding the data.
• -b, --blobs, -B, --no-blobs: Controls whether large objects (blobs) are included in the dump.
• -v, --verbose: Enables verbose mode, providing detailed object comments and progress messages.
• -c, --clean: Includes commands to drop database objects before creating them.
• -C, --create: Includes commands to create and connect to the database.

Additional Useful Options

• -E, --encoding: Specifies the character set encoding for the dump file.
• -n, --schema=pattern: Dumps only schemas matching the pattern. Multiple schemas can be selected by using multiple -n options.
• -t, --table=pattern: Dumps only tables matching the pattern. This can include views, materialized views, and sequences.
• --inserts: Dumps data as INSERT commands instead of using the COPY command. Useful for loading dumps into non-PostgreSQL databases.
• -Z, --compress=method[:detail]: Specifies the compression method (gzip, lz4, zstd, or none) and/or level for the dump file. Applies to custom and directory formats.
• --no-owner: Omits commands to set ownership of objects to match the original database.
• --no-acl, --no-privileges: Prevents dumping of access privileges (GRANT/REVOKE commands).
• --if-exists: Uses DROP ... IF EXISTS commands to drop objects in clean mode, suppressing errors for non-existent objects.