Skip to content

pg_dump

Writes a consistent backup of a PostgreSQL database while it keeps running, as SQL or in a compressed archive format.

pg_dump output

Syntax

pg_dump [OPTIONS] DATABASE
pg_restore [OPTIONS] -d DATABASE FILE

Options

Option Description
-Fc Custom format: compressed, restore selectively with pg_restore
-f FILE Write to a file instead of standard output
-t TABLE Only this table
--schema-only, --data-only Only the structure, or only the data
-U USER, -h HOST Connect as another user, to another server
pg_dumpall --globals-only Roles and tablespaces, which pg_dump doesn't include
pg_restore -d DB FILE Restore a custom-format dump; -l lists its content

Examples

Dump a database in custom format:

sudo -u postgres pg_dump -Fc -f /var/backups/db/statusdb-$(date +%F).dump statusdb
ls -lh /var/backups/db/
total 4.2M
-rw-r--r-- 1 postgres postgres 4.2M Oct 26 02:30 statusdb-2026-10-26.dump

What's in the dump:

pg_restore -l /var/backups/db/statusdb-2026-10-26.dump | head -12
;
; Archive created at 2026-10-26 02:30:01 CET
;     dbname: statusdb
;     TOC Entries: 11
;     Compression: gzip
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 17.6 (Debian 17.6-0+deb13u1)
;     Dumped by pg_dump version: 17.6 (Debian 17.6-0+deb13u1)
;

Restore into a test database:

sudo -u postgres createdb statusdb_test
sudo -u postgres pg_restore -d statusdb_test /var/backups/db/statusdb-2026-10-26.dump
sudo -u postgres psql -tA statusdb_test -c 'SELECT count(*) FROM checks'
5000

The restore test: the number of rows matches the production database.

Also back up the roles:

sudo -u postgres pg_dumpall --globals-only > /var/backups/db/globals.sql
grep -c 'CREATE ROLE' /var/backups/db/globals.sql
3

When do I need it?

  • Every night before the file backup, so the dump is part of it
  • Before an upgrade of PostgreSQL or the application
  • To copy a database to a test or staging system

Custom format for backups

-Fc is compressed, and pg_restore can restore single tables or the schema from it. A plain SQL dump (.sql) is easier to read but can only be restored completely.

Roles aren't in the dump

pg_dump contains one database, not the users and their passwords. Back them up with pg_dumpall --globals-only as well, otherwise the restore fails with missing roles.

Distribution differences

Debian Ubuntu RHEL Arch
Package postgresql-client postgresql-client postgresql postgresql

mysqldump · restic · docker exec · crontab