PostgreSQL logical backup and restore (pg_dump / pg_restore)

Logical backup and restore with pg_dump, pg_dumpall and pg_restore at table, schema, database or whole-cluster level; structure only (DDL) or data only, moving to another server/version, restoring a single table from a dump, and scheduled, verified backups with retention through systemd/cron. Every path ends with a verification that compares row counts. In streaming replication and Patroni clusters the dump is taken from a replica (HAProxy read port or the standby), and restores go to the primary/leader only.

A logical backup returns to the moment of the dump and gives no point-in-time recovery (PITR); in production the main backup must be physical: pgBackRest (recommended) or, without extra tools, pg_basebackup + WAL archive. To move a large database to a new version with seconds of downtime use logical replication; for an in-place upgrade on the same server use pg_upgrade.

Topology: 1 server, streaming replication (primary + standby) or a Patroni cluster; source + target for a migration, plus a backup server for remote scheduled backups

Scenarios

• Export / import single table(s) — The structure and data of the selected tables (including indexes, constraints, triggers) are dumped and loaded into another database.
• Export / import a schema — All objects in the schema (tables, views, functions, sequences) are dumped.
• Back up / restore a single database — A whole database (structure + data) is dumped to a single dump and restored to the same or another database. Roles (globals) are not included.
• Whole cluster (roles + tablespaces + every database) — Globals with pg_dumpall -g, a separate pg_dump for each database; the restore is rehearsed on a temporary test cluster.
• Schema structure only (DDL) — Table, index, view and function definitions without data; for review, version control or building an empty environment.
• Data only — Only the rows (COPY) are transferred while the structure is already in place on the target.
• Move to another server / version — On the target server, the pg_dump of the new version connects to the source over the network; the data is transferred through a pipe or a file.
• Restore a single table / object from a dump — From an existing custom/directory dump, only one table with its indexes, constraints and data is restored using a list file (-L).
• Scheduled backup (systemd timer / cron) — Daily globals + database backup, readability check, retention, logging and failure notification.

Before you start

• …The Patroni cluster is running; the PostgreSQL … client tools (…) are installed on this server and the HAProxy/VIP (…) write (…)… and read (…)… ports are reachable. You have the password of the postgres role (with Patroni, PATRONI_SUPERUSER_PASSWORD in /etc/patroni/patroni.env).…PostgreSQL … is running on the primary and the postgres user can connect locally (peer); the standby … is in streaming state and its port … is reachable from the primary. The dump connects to the standby over the network, so the postgres role must have a password (if not, run \password postgres on the primary; the password reaches the standby through WAL).…PostgreSQL … is running and the operating system user postgres can connect locally (peer). If it was installed with the DBAtlas PostgreSQL single server installation guide, the paths are the same.…
• …The cluster is healthy: one Leader, at least one replica streaming, and low lag. Check it on one of the Patroni nodes.…The standby … is connected to the primary and its lag is low (the dump takes the standby's image at that moment).…
• The backup directory … is on a disk separate from the data disk and has enough free space: a compressed custom/directory dump is usually 10–30% of the database size (indexes go into the dump only as definitions); plain format and uncompressed dumps are larger.
• pg_dump is the same or a newer major version than the server being backed up (a newer pg_dump can read an older server; the reverse does not work). pg_restore must be the same version as or newer than the pg_dump that created the dump.
• pg_dump/pg_restore on the target (…) are the same or a newer major version than the source server (…): the migration is done with the target's newer tools.
• The software of the extensions used on the source (postgis, pgvector, pg_partman…) is also installed on the target server. A logical backup carries only the CREATE EXTENSION line, not the extension itself.
• If there are user-defined tablespaces (the query below returns rows), the same directories must be prepared on the target, or the restore must go to the default tablespace with --no-tablespaces.
• PostgreSQL … is installed, running and empty on the target server … (only postgres, template0, template1). The target can reach port … on the source.
• The backup server … can reach …port … of the HAProxy/VIP address ……port … of the standby ……port … of the database server… and has access to the PGDG repository (or an internal mirror).
• The server can send e-mail: the local MTA (postfix) relays to the corporate SMTP relay.
• A maintenance window is planned for a large restore or migration; if the target will be overwritten, a backup/snapshot of the target has been taken.

Phases of the guide

• Preparation — Create the backup directory · Store the connection password in .pgpass · Allow access in pg_hba.conf on the standby · Verify which role the connections reach · Check database sizes and long-running transactions
• Restore (import) — Prepare the target database · Prepare the target: extensions, schemas and trigger functions · Restore · Restore overwriting the target · Gather statistics (ANALYZE) · Compare row counts
• Schema structure only (DDL) — Take the DDL dump · Create the target database · Apply the DDL to the target · Verify that the structures are identical
• Data only — Take the data dump · Create the target database · Create the table structure on the target · Empty the target tables · Load the data · Gather statistics and compare row counts
• Whole cluster backup — Back up the globals and all databases · Verify that the backup is readable
• Restore rehearsal (temporary test cluster) — Prepare the test directory · Create and start the temporary cluster · Load the globals · Load the databases · ANALYZE and row count comparison · Stop and remove the temporary cluster
• Access permission on the source server — Allow the target server in pg_hba.conf · Allow the target server in pg_hba (Patroni) · Open the port to the target in the firewall · Stop the application (cutover point)
• Transfer on the target server — Store the source connection password in .pgpass · Check version and extension compatibility · Move the roles (globals) · Transfer the databases (through files, in parallel) · Transfer the databases (through a pipe) · Gather statistics (ANALYZE) …
• After cutover — Remove the temporary access on the source · Remove the temporary access on the source (Patroni) · Point the application to the target
• Restore a single table from a dump — Inspect the dump contents · Create the list file (-L) · Prepare the target database · Restore the table · Restore the table into the original database · Compare the row count …
• Backup server and backup role — Create the backup role · Grant read access to large objects · Set a password for the backup role · Allow the backup server in pg_hba.conf · Allow the backup server in pg_hba (Patroni) · Install the PostgreSQL client tools (backup server) …
• Backup script — Verify the full paths of the tools · Install the notification tool · Write the backup script · Make the script executable · Take the first backup by hand
• Scheduling (systemd timer) — Write the systemd service · Write the systemd timer · Enable the timer and trigger the service by hand
• Scheduling (cron) — Prepare the cron service · Write the cron job · Set the cron file permissions and test in a cron environment · Verify that cron runs the job
• Verification and monitoring — Restore the latest backup into a test database · Monitor the age of the latest backup
• Next step: physical backup — Close the gaps of logical backup