PostgreSQL 17 Block-Level Incremental Backup: Streamlining Data Protection

Introduction​
PostgreSQL’s traditional pg_basebackup tool excels at full backups but struggles with incremental scenarios. Prior to v17, achieving incremental backups required third-party tools or manual WAL parsing. PostgreSQL 17 introduces ​block-level incremental backups, leveraging WAL summarization to track data changes at the block level, significantly reducing backup sizes and recovery times.

Key Features of PostgreSQL 17 Incremental Backup

1. ​Block-Level Tracking: Records modified data blocks since the last backup, avoiding full tablespace copies.
2. ​WAL Summarization: Enabled via summarize_wal = on, generates metadata about WAL segments to identify changed blocks.
3. ​Integration with pg_basebackup: Supports incremental backups using the -i flag and manifest files for validation.

Workflow Demonstration

1. Create Test Data

CREATE TABLE bigdata (last_updated timestamptz);
INSERT INTO bigdata VALUES (now());
UPDATE bigdata SET last_updated = now(); -- Generate WAL activity

‍

2. Initial Full Backup

# Create a full backup with manifest for integrity checks
pg_basebackup -Ft -D /backups/full_backup_$(date +%F)

‍

3. Trigger WAL Activity

-- Simulate data changes to generate WAL logs
UPDATE bigdata SET last_updated = now();

‍

4. Configure WAL Summarization

-- Enable WAL summarization (required for incremental backups)
ALTER SYSTEM SET summarize_wal = ON;
SELECT pg_reload_conf();

‍

5. Perform Incremental Backup

# Use the previous backup's manifest as a reference
pg_basebackup -i /backups/full_backup_2025-07-07/backup_manifest \
              -Ft -D /backups/incremental_backup_$(date +%F)

‍

6. Verify Backup Contents

# Compare file sizes (incremental backups are significantly smaller)
ls -lh /backups/full_backup_*/base.tar
ls -lh /backups/incremental_backup_*/base.tar

‍

7. Merge Backups (Optional)

Combine full and incremental backups into a unified archive:

pg_combinebackup -o /backups/combined \
                /backups/full_backup_2025-07-07/ \
                /backups/incremental_backup_2025-07-07/

‍

Technical Insights

• ​Manifest Files: Store checksums and metadata for backup validation (backup_manifest).
• ​Storage Efficiency: Incremental backups reduce WAL retention overhead by focusing on changed blocks.
• ​Recovery Workflow: Use pg_restore or pg_rewind to apply incremental backups to a primary/secondary cluster.

Best Practices

• ​Enable WAL Summarization: A prerequisite for incremental backups.
• ​Test Backup Integrity: Regularly validate manifests and restore operations.
• ​Archive WAL Segments: Ensure WAL files are preserved until backups are safely stored.

By adopting PostgreSQL 17’s block-level incremental backups, organizations can achieve faster, more efficient disaster recovery while minimizing storage costs. Experiment with these features in your environment to unlock new levels of data resilience.

‍