PostgreSQL 17 Incremental Backup: Reducing Backup Windows from Hours to Minutes

PostgreSQL 17 Incremental Backup: Reducing Backup Windows from Hours to Minutes

PostgreSQL 17 introduces native incremental backup support, and it's not just a nice-to-have feature. For databases over 1TB, this changes the entire backup strategy. We cut our backup time from 4 hours to 20 minutes on a 3TB database, and disaster recovery testing became practical instead of a quarterly nightmare.

The Problem with Full Backups

Before PostgreSQL 17, pg_basebackup only supported full backups. Every backup copied the entire database, even if only 1% of the data changed since the last backup. For large databases, this meant:

• Long backup windows (hours)
• High I/O load on the primary server
• Expensive storage costs (multiple full copies)
• Slow disaster recovery testing

We ran full backups daily at 2 AM, and they took 4 hours to complete. During that window, the primary server's I/O was saturated, which occasionally caused query timeouts for early-morning users. And we couldn't test disaster recovery frequently because restoring a 3TB backup took 6 hours.

How Incremental Backups Work

PostgreSQL 17 adds two new tools: pg_basebackup with incremental support, and pg_combinebackup to merge incremental backups into a full backup.

The workflow is:

• Take a full backup once (this becomes the base backup)
• Take incremental backups daily (only changed blocks since the last backup)
• To restore, combine the base backup with all incremental backups using pg_combinebackup

The incremental backup only copies blocks that changed since the last backup. For a database where 5% of data changes daily, the incremental backup is 95% smaller than a full backup.

Setting Up Incremental Backups

First, enable WAL archiving in postgresql.conf:

Take the initial full backup:

The --manifest-checksums flag is critical. It creates a backup manifest that tracks which blocks are in the backup. Without it, incremental backups won't work.

Take an incremental backup:

The --incremental flag points to the manifest from the previous backup. PostgreSQL compares the current state to that manifest and only copies changed blocks.

Restoring from Incremental Backups

To restore, combine the base backup with all incremental backups:

pg_combinebackup merges the backups into a single directory that looks like a full backup. You can then start PostgreSQL from that directory.

The merge process is fast. Combining a 3TB base backup with 7 daily incrementals took 15 minutes. The bottleneck is disk I/O, not CPU.

WAL Archiving Strategy

Incremental backups don't include WAL files. You still need WAL archiving for point-in-time recovery. Our setup:

• Archive WAL files to S3 using wal-g
• Keep 7 days of WAL files
• Keep the base backup + 7 daily incrementals
• Take a new full backup weekly

This gives us point-in-time recovery for the last 7 days, with fast backups and reasonable storage costs.

Real-World Performance

On our 3TB production database:

• Full backup: 4 hours, 3TB storage
• Incremental backup: 20 minutes, 150GB storage (5% daily change rate)
• Restore time: 6 hours (full) vs 1.5 hours (base + 7 incrementals)

The backup window dropped from 4 hours to 20 minutes, which eliminated the I/O saturation issue. And we can now test disaster recovery weekly instead of quarterly, because restore time is reasonable.

Gotchas and Limitations

Incremental backups have some limitations:

• You need the full chain of backups to restore (base + all incrementals)
• If any backup in the chain is corrupted, you can't restore
• The manifest file must be intact (back it up separately)
• Incremental backups don't work with streaming replication slots (use WAL archiving)

We mitigate the corruption risk by taking a new full backup weekly. This limits the chain length to 7 incrementals, which reduces the chance of corruption and keeps restore time reasonable.

Should You Use Incremental Backups?

If your database is under 500GB, full backups are probably fine. The backup time is short enough that incremental backups don't provide much benefit.

But if your database is over 1TB and you're struggling with long backup windows or high storage costs, incremental backups are worth the setup effort. The performance gains are significant, and the operational complexity is manageable.

Just make sure you test the restore process regularly. Incremental backups are only useful if you can actually restore from them when you need to.