Database backup strategies: full, incremental, logical, physical and point-in-time

Database backup strategies in one table

The main database backup strategies are full backups, incremental backups, logical dumps, physical file copies, and continuous log archiving for point-in-time recovery. They are not rivals. A real plan combines a kind of copy (logical or physical) with a rhythm (full or incremental) and, where needed, a log archive. The table has four rows because the full backup shown is the physical one; a logical dump is always a full copy.

Logical versus physical: what each can and cannot restore

A logical backup is a description of the data. In PostgreSQL, pg_dump writes a file that recreates one database as it was when the dump began, without blocking normal reads and writes. The manual notes its key advantage: the output can generally be loaded into a newer PostgreSQL version, and it is the only method that moves a database to a different machine architecture. In MySQL, mysqldump plays the same role.

A physical backup copies the files. PostgreSQL's pg_basebackup always copies the entire cluster; the manual says individual databases or objects cannot be selected. The MySQL manual makes the matching point that restoring physical files is much faster than replaying a logical dump. The trade is flexibility for speed. pg_dump versus pg_basebackup walks through that choice.

How full, incremental and point-in-time recovery fit together

1. Take a full base backup. This is the starting point every later step depends on.
2. Archive the write-ahead log. PostgreSQL records every change in WAL segment files, normally 16 MB each. With archiving on, each finished segment is copied somewhere safe.
3. Optionally add incrementals. Since PostgreSQL 17, pg_basebackup --incremental saves only changed blocks. Restoring one requires the full backup and every incremental before it, combined with pg_combinebackup.
4. Recover. Restore the base backup, then replay the archive. Replay can stop at a date and time or a named restore point, which is what point-in-time recovery means.

The PostgreSQL manual is explicit that a dump cannot do this: pg_dump output does not contain enough information for WAL replay. MySQL offers the same idea through its binary log, which the 8.4 manual says is enabled by default.

A worked schedule: weekly full, daily differential, continuous WAL

The pgBackRest user guide gives a concrete schedule. A full backup runs at 06:30 on Sunday, and a differential backup runs at 06:30 Monday through Saturday, with repo1-retention-full=2 keeping two full backups. WAL archiving runs all week through archive_command.

With that schedule, a table dropped on Thursday afternoon is recovered from Sunday's full backup, Thursday morning's differential and the WAL written since. The data you can lose is limited to the WAL not yet archived. For a 2 GB application database with no recovery-time pressure, the same team could instead run one nightly pg_dump -Fc and accept losing up to a day. Set the numbers first using recovery objectives, then pick the strategy.

The 3-2-1 rule and the mistakes that break a strategy

The 3-2-1 rule comes from a paper published by US-CERT, now on the CISA website, titled Data Backup Options. It says to keep 3 copies of any important file (1 primary and 2 backups), on 2 different media types, with 1 copy off-site. For a database that means the backup must not sit only on the database server's own disk.

• Treating a dump as PITR. A nightly dump restores to last night, not to 14:59 today.
• Losing one link of an incremental chain. PostgreSQL has no built-in way to tell you which earlier backups are still needed; you must track that.
• Forgetting what is outside the database. WAL archiving does not capture postgresql.conf or pg_hba.conf, and pg_dump skips roles.
• Never restoring. Schedule a restore into an isolated target.

When a paid backup service is worth it

Native tools are enough when one person owns one or two databases and checks the job. A paid service earns its fee when nobody has time to watch cron, when several databases sit on different providers, or when someone else must be able to restore. SimpleBackups starts at $0 for one job and $49 per month for five, as read on its pricing page on 3 October 2026. It schedules backups and stores them off-site; if you need minute-level recovery, use WAL archiving or your provider's own point-in-time feature alongside it. Managed versus scripted backups compares the effort.