Database backups: logical, physical and PITR

How pg_dump differs from copying the cluster's files, why you need a WAL archive, and how to recover to the minute before a bad query.

Why you can't just copy a database

A database constantly keeps some data in memory and doesn't write it to disk right away. If you copy the files of a running cluster with plain cp, you get a set of files from different points in time: minutes separate the start of the copy from the end, and everything inside changed during that time.

A copy like that either won't start at all, or it will start and quietly lie to you. That's why databases have their own tools, and there are two classes of them.

Logical backup

A logical backup is a dump of the content: commands and data that let you rebuild the database from scratch.

pg_dump -Fc mydb > mydb-2026-08-23.dump
pg_restore -d mydb_new mydb-2026-08-23.dump

The -Fc flag gives you a compressed format that pg_restore can restore selectively from: schema only, data only, individual tables.

Strengths: the copy is portable across versions, even across machines with different architectures, and it lets you pull out a single table. The -Fp format is also human-readable: it's plain SQL. You look at what's inside a -Fc dump with pg_restore -l: the command prints the dump's table of contents.

Weaknesses: it takes a long time on large databases, loads the server, and restores even more slowly because the database rebuilds the indexes from scratch. A terabyte-sized database takes hours to restore from a dump, and that hits your RTO directly. Parallelism helps somewhat: pg_restore -j 8 spreads the work across several processes. pg_dump -j only works with the -Fd format (directory); with -Fc it refuses: parallel backup only supported by the directory format.

One more thing: pg_dump takes a consistent snapshot by running one long transaction, and that transaction holds back the visibility horizon. VACUUM keeps running during this, but it can't remove row versions that died after the dump started. On a busy database, a dump that runs for hours noticeably bloats the tables.

Physical backup

A physical backup is a copy of the cluster's files, taken the right way:

pg_basebackup -D /backup/base -Ft -z -P

The database itself tracks consistency here: it knows the moment the copy started, and the logs needed to bring the copy into a working state are saved alongside it. With -Ft -z you get compressed tar archives; with -Fp, a plain data directory that you can hand straight to a server.

Strengths: it's faster to take than a logical backup, faster to restore too (the files just get put back in place), and it's the foundation for point-in-time recovery.

Weaknesses: the copy is tied to the server's major version and to the platform (bitness, byte order), you can't pull out a single table selectively, and the uncompressed size equals the size of the whole cluster.

WAL and point-in-time recovery

Postgres writes every change to the write-ahead log (WAL) first, and only then does it land in the data files. If you keep saving these logs continuously, you get the capability that this whole exercise is really about.

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

Two caveats. archive_mode only takes effect when the server starts, so turning it on requires a restart, and wal_level needs to be at least replica (which is the default). And the cp example itself is taken from the documentation as an illustration: it doesn't do an fsync, so in production people use pgBackRest, WAL-G or barman.

The scheme works like this: a physical backup once a day, with continuous WAL archiving in between. When something breaks, you restore the latest base backup and replay the logs on top of it up to the moment you need.

# postgresql.conf of the cluster being restored
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-23 14:29:00'
recovery_target_action = 'promote'

The parameters go into postgresql.conf, and an empty file called recovery.signal gets created in the data directory. There's no separate recovery.conf since PostgreSQL 12, and if you drop one in, the server simply won't start, with the error using recovery command file "recovery.conf" is not supported. A lot of old articles still recommend exactly that file.

recovery_target_action isn't a formality either. The default is pause: the server reaches the target moment and stops there, waiting for your decision. You can read the data at that point but not write to it. With the value promote, it finishes recovery right away and starts accepting load.

This is called PITR, point-in-time recovery. It's exactly what saves you in the scenario "someone ran the wrong DELETE at 14:30": you bring the database up at 14:29 and lose a minute instead of a day.

RPO in this setup is determined by how fast the log makes it into the archive. A WAL segment (16 MB) gets archived once it's full, so on a quiet database it can sit around for a long time. The archive_timeout parameter forces the server to switch segments at least as often as the interval you set, and RPO drops to minutes instead of the time between full backups. Details in the documentation: Continuous Archiving and PITR.

MySQL: the same thing in different words

Logical dump: mysqldump --single-transaction (for InnoDB, it takes a consistent snapshot without locking tables). Consistency breaks if DDL runs against the tables being dumped while the dump is in progress.

Physical copy: for MySQL and Percona Server, that's Percona XtraBackup. MariaDB has its own tool, mariadb-backup (formerly called mariabackup). These are different programs, not synonyms: XtraBackup 8.4 doesn't work with MariaDB at all.

The WAL equivalent: the binary log, binlog. It gives you point-in-time recovery just the same way.

What to choose

A small database with an RTO measured in hours: a scheduled logical dump is enough. Simple, portable, easy to verify.

A large database or a tight RPO: a physical backup plus a log archive. Dumps sometimes get kept on top of that too, for portability and selective restore.

A replica in the setup replaces neither one: it replays the mistaken query within milliseconds.

Check: restore and count

A database dump is verified only by restoring it. The minimal automated test looks like this: bring up an empty instance, restore the copy, compare the row counts in the key tables against what you expect, then tear it down.

createdb checkdb
pg_restore -d checkdb mydb-2026-08-23.dump
psql -d checkdb -c "select count(*) from orders;"

createdb is mandatory here: pg_restore -d connects to a database that already exists and doesn't create it itself. Without it, you get the error database "checkdb" does not exist.

Running this once a week catches both broken archives and surprises like "a table got excluded from the dump a year ago and everyone forgot".

Common mistakes

• Copying the data directory of a running database with cp or rsync instead of pg_basebackup.
• Taking a dump in the -Fp format and trying to restore it with pg_restore. Plain SQL is restored with psql -f; pg_restore works with -Fc, -Fd and -Ft.
• Restoring a dump into a database that doesn't exist, or on top of an old attempt that already has tables in it.
• Putting the recovery settings in recovery.conf because an old article said so.
• Forgetting recovery.signal: then the server starts as a normal one, with data from the moment of the base backup, and the logs never get replayed.
• Not specifying recovery_target_time: the server replays the log all the way to the end, including that very DELETE that caused the problem.
• Leaving recovery_target_action at its default and then being surprised that the database is read-only.
• Restoring on top of the working data directory. It's safer to bring up a copy next to it, on a different directory and port, and pull what you need out of that.

What you'll need for the tasks

The tasks run in a container with PostgreSQL 16. Commands run as the postgres user: docker exec -u postgres <container> <command> or in a shell with docker exec -it -u postgres <container> sh.

• pg_dump -Fc -f <file> <database> takes a dump in custom format into a file.
• pg_restore -l <file> shows the dump's table of contents; pg_restore -d <database> <file> restores it into an existing database.
• createdb, dropdb, psql -l create, drop and list databases.
• pg_ctl -D <directory> -o '-p <port>' -l <log> start starts a second server on a different data directory and port; pg_ctl -D <directory> stop stops it. The startup log gets written to the file from -l.
• psql -p <port> connects to the server on that port; select pg_is_in_recovery() shows whether the server has finished recovery.
• The data directory must be owned by postgres and have permissions 0700 (or 0750), otherwise the server won't start. cp -a preserves both the owner and the permissions.

What to remember

You can't copy the files of a running database with plain cp: you'll end up with a set of files from different moments.

A logical dump is portable and lets you pull out individual tables, but it restores slowly.

A physical backup is faster and serves as the foundation for PITR, but it's tied to the version and the platform.

A WAL archive (in MySQL that's the binlog) gives you point-in-time recovery and brings RPO down to minutes.

A dump counts as working only after you restore it and verify the data.

Next, hands-on practice: file-level backups with restic, with real commands and output.