PostgreSQL Backups: pg_dump, pg_basebackup and PITR

← Back to the section

Picture this: on Friday evening someone drops the orders table. Or a migration ships that wipes out data. The only thing standing between you and disaster is a backup. Let's look at how it works in PostgreSQL.

Two approaches: logical and physical backup

PostgreSQL has two fundamentally different ways to create a backup.

Logical backup is a dump of your data as SQL commands or a structured format. The tool is pg_dump. You get a file from which you can recreate a specific database, schema or table — including on a different PostgreSQL version.

Physical backup is a copy of the on-disk files that make up a cluster. The tool is pg_basebackup. It is faster and fits production, because it supports recovery to an exact point in time. The downside — the PostgreSQL version at restore time must match.

pg_dump: basic usage

The simplest option is to dump a database into a SQL file:

pg_dump -U user -h host mydb > mydb.sql
psql -U user -h host mydb_new < mydb.sql

Handy for small databases: the file is readable and opens in any editor.

Custom format — the recommended way

For serious use it is better to take the custom format (-Fc):

pg_dump -Fc -U user -h host mydb > mydb.dump
pg_restore -j 4 -d mydb_new mydb.dump   # 4 parallel workers

Such a file is more compact than the text one, and -j 4 at restore time splits the tables between workers and noticeably speeds up large databases. A flat SQL file cannot do that: only custom and directory formats restore in parallel.

Useful flags

pg_dump --schema=public              # only one schema
pg_dump --table=orders               # only one table
pg_dump --schema-only                # structure only, no data
pg_dump --data-only                  # data only, no structure
pg_dump --exclude-table=audit_log    # exclude a table
pg_dump --no-owner --no-acl          # for restore into another cluster

Roles and global settings

pg_dump copies only a single database. Roles, tablespaces and global settings are stored at the cluster level — they are copied by pg_dumpall:

pg_dumpall --globals-only > globals.sql   # roles, tablespaces
pg_dump mydb > mydb.sql                   # database data

Restore a database locally for debugging

A typical scenario: a bug reproduces for a user, and you need a dump of production for local debugging.

createdb -U postgres customer_debug
pg_restore -d customer_debug -j 4 customer-prod-2026-05-01.dump
psql customer_debug

Important: a production dump must be anonymized before it is handed over — strip out personal data. Data-protection laws such as GDPR require it. The tools are postgresql_anonymizer and manual UPDATE scripts; a raw dump with real user data goes nowhere.

pg_basebackup: physical backup of a cluster

A physical backup creates an exact copy of the cluster files:

pg_basebackup -U replicator -h master -D /backup/2026-05-07 -Ft -z -P

• -Ft — pack into tar.
• -z — compress with gzip.
• -P — show progress.

To restore, the archive is unpacked into data_directory and PostgreSQL is started — the same major version, otherwise the cluster will not come up.

PITR: point-in-time recovery

The most powerful capability is Point-In-Time Recovery: take a physical backup and "replay" the WAL journal files on top of it up to the moment you need.

If someone deleted all the orders at 14:30 and the backup was taken at 02:00, PITR brings the database back to 14:29 — one minute lost instead of half a day.

For that you set up WAL archiving on the server:

archive_mode = on
archive_command = 'test ! -f /wal-archive/%f && cp %p /wal-archive/%f'

This means every filled WAL segment is copied into an archive directory mounted from another machine. The test ! -f check keeps the command from overwriting a segment that is already stored, and on failure the command must return a non-zero code — then PostgreSQL keeps the segment and tries again. That is how the chain of changes stays unbroken.

When you need to recover to a specific moment:

# recovery.signal in data_directory + postgresql.conf:
restore_command = 'cp /wal-archive/%f %p'
recovery_target_time = '2026-05-07 14:29:00 MSK'

PostgreSQL takes the base backup, applies the WAL files one by one and stops exactly at 14:29. After that the server pauses by default — you can check whether this is the right point and finish recovery with pg_wal_replay_resume(). If there is nothing to check, set recovery_target_action = 'promote' upfront.

The replay mechanics are visible without a database — on a toy journal of five records:

live example

import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class PitrDemo {

    record Wal(String time, String orderId, String status) {}

    static final List<Wal> JOURNAL = List.of(
            new Wal("08:15", "ord-3", "NEW"),
            new Wal("12:40", "ord-3", "PAID"),
            new Wal("14:30", "ord-1", null),
            new Wal("14:30", "ord-2", null),
            new Wal("14:30", "ord-3", null));

    public static void main(String[] args) {
        System.out.println("backup at 02:00:    " + replay("02:00"));
        System.out.println("replay up to 14:29: " + replay("14:29"));
        System.out.println("replay to the end:  " + replay("23:59"));
    }

    static Map<String, String> replay(String target) {
        Map<String, String> db = new TreeMap<>(Map.of("ord-1", "PAID", "ord-2", "PAID"));
        for (Wal entry : JOURNAL) {
            if (entry.time().compareTo(target) > 0) {
                break;
            }
            if (entry.status() == null) {
                db.remove(entry.orderId());
            } else {
                db.put(entry.orderId(), entry.status());
            }
        }
        return db;
    }
}

The second line is the replay up to 14:29: the order created and paid during the day is still there. The third one is the replay with no limit — the last journal records repeat the deletion, and the database is broken again. That is why PITR asks for a target time instead of "apply everything you have".

How much an hour between the backup and the incident is worth, a plain query answers:

live example

SELECT date_trunc('hour', created_at) AS lost_hour,
       count(*) AS orders_lost,
       sum(total_amount) AS amount_lost
FROM orders
WHERE created_at > timestamp '2026-05-07 02:00'
GROUP BY date_trunc('hour', created_at)
ORDER BY lost_hour;

How long to keep backups

A typical retention scheme:

• daily copies — 7 days;
• weekly — 4 weeks;
• monthly — 12 months;
• continuous WAL archive — the last 7 days.

All copies live separately from the server that holds the data — on S3, a NAS, or cloud storage: when the disk dies, everything on it dies too.

And the golden rule: a copy you have never restored from is not a copy. A corrupted file shows up only when someone checks it — better that this is a scheduled check.

Backups in a multi-tenant architecture

When a single database serves several customers, the strategy depends on how the data is separated:

• All customers in shared tables (tenant_id column) — back up the whole database; one customer's data is exported with a query when needed: COPY (SELECT * FROM orders WHERE tenant_id = 42) TO '/tmp/t42.csv' CSV.
• Schema per customer (schema-per-tenant) — pg_dump --schema=tenant_X gives you a backup of a specific customer.
• Database per customer (db-per-tenant) — each database has its own backup schedule.

What to do when "everything is broken"

The plan for lost or corrupted data:

1. Stop the changes — no new INSERT, UPDATE, DELETE. Every change after the disaster makes recovery harder.
2. Call the DBA or the infrastructure owner.
3. Make a copy of the current state before restoring anything.
4. Recover via PITR to a point before the problem — if WAL archiving is set up.
5. If there is no PITR — the last good backup plus manual recovery of the lost data.

An important nuance: if the table was dropped inside an open transaction, the data is still there — find that transaction in pg_stat_activity. A rollback in the same session, or pg_terminate_backend() for its process, brings the table back without any backup at all.

Common mistakes

--inserts for large tables. The --inserts mode generates one INSERT per row — tens of times slower than the standard COPY-based mode. Use it only when you truly need to (for example, compatibility with other database systems).

Restoring a physical backup into a different major PostgreSQL version. It does not work: to move between versions — only pg_dump.

In short

• Two kinds of backup: logical (pg_dump) — flexible, for debugging; physical (pg_basebackup) — fast, for production.
• pg_dump -Fc produces a compact file, pg_restore -j 4 restores in parallel, pg_dumpall --globals-only picks up cluster roles and global settings.
• Physical backup + WAL archive = PITR — recovery to any moment in the past; without the archive there is a single point, the time of the backup.
• Backups must go to separate storage — a local backup is no protection.
• A backup without a tested restore is not a backup.
• Anonymize a production dump before handing it over.

What to read next

• WAL in PostgreSQL — how the journal works and why it is needed for PITR.
• Replication in PostgreSQL — pg_basebackup is also used to create replicas.
• Data anonymization — how to prepare a production dump for handover.
• Multi-tenancy in PostgreSQL — strategies for separating data by customer.