How to create a PostgreSQL database backup

Creating regular backups in PostgreSQL protects applications from data loss caused by operator mistakes, failed deployments, corruption, or infrastructure failures. A current dump also enables fast migration to a new host when storage or compute needs to be replaced.

Logical backups produced by pg_dump read schema and rows through a consistent snapshot, writing them into a portable archive. The custom archive format compresses efficiently and supports selective restore and inspection through pg_restore, which suits most application databases.

Commands below assume local access on Linux using the postgres system account, which is common on distribution packages. A pg_dump archive covers a single database and does not include cluster-wide objects such as roles, role memberships, and tablespace definitions. Capture global objects separately (for example, pg_dumpall --globals-only). Use a pg_dump binary from the same major version as the server when possible, keep backups access-controlled because they contain sensitive data, and store copies off-host so the database host is not a single point of failure.

1. Choose the database name and a destination directory for the dump file.

   Example destination: /var/backups/postgresql.

2. Create the backup directory with restricted permissions.

   $ sudo install --directory --mode=0750 --owner=postgres --group=postgres /var/backups/postgresql

3. Check available space in the backup filesystem.

   $ df -h /var/backups/postgresql
   Filesystem      Size  Used Avail Use% Mounted on
   overlay         1.8T   14G  1.7T   1% /

4. Create a compressed custom-format dump using pg_dump.

   $ sudo -u postgres pg_dump --format=custom --compress=9 --file=/var/backups/postgresql/appdb-$(date +%F_%H%M%S).dump appdb

   Replace appdb with the database name. Add --username and --host for remote connections.

5. Confirm that pg_dump completed successfully.

   $ echo $?
   0

6. Restrict dump file permissions to administrative users.

   $ sudo chmod 600 /var/backups/postgresql/appdb-*.dump

   Database dumps contain sensitive data and should not be world-readable.

7. Verify that the dump file exists and is not empty.

   $ ls -lh /var/backups/postgresql/appdb-*.dump
   -rw------- 1 postgres postgres 25K Dec 29 09:04 /var/backups/postgresql/appdb-2025-12-29_090421.dump

8. Validate the dump archive by listing its table-of-contents using pg_restore with the filename from the previous step.

   $ sudo -u postgres pg_restore --list /var/backups/postgresql/appdb-2025-12-29_090421.dump
   ;
   ; Archive created at 2025-12-29 09:04:22 UTC
   ;     dbname: appdb
   ;     TOC Entries: 61
   ;     Compression: gzip
   ;     Dump Version: 1.15-0
   ;     Format: CUSTOM
   ;     Integer: 4 bytes
   ;     Offset: 8 bytes
   ;     Dumped from database version: 16.11 (Ubuntu 16.11-0ubuntu0.24.04.1)
   ;     Dumped by pg_dump version: 16.11 (Ubuntu 16.11-0ubuntu0.24.04.1)
   ##### snipped #####

9. Copy the dump file to off-host storage.

   $ sudo rsync --archive --compress /var/backups/postgresql/appdb-2025-12-15_021512.dump backup@backup1:/srv/backups/postgresql/

   Off-host storage prevents a single VM or disk failure from taking both the database and its backups.

10. Record the backup file name and storage location so restores are predictable.

    Custom-format dumps restore with pg_restore.