PostgreSQL PITR Recovery Time Objective After Failed Major Version Upgrade

Point-in-Time Recovery Configuration for PostgreSQL 16 After Failed Upgrade

When planning a major version upgrade from PostgreSQL 15 to 16, organizations must establish recovery time objectives (RTO) and recovery point objectives (RPO) using continuous WAL archiving and base backups. The PITR mechanism relies on a complete base backup combined with all WAL segments generated since that backup.

The key constraint is determining acceptable data loss window: whether to perform frequent base backups (every 6 hours) or rely on continuous WAL archiving with less frequent base snapshots. Storage I/O performance during the restore phase directly impacts achievable RTO, particularly when WAL volume exceeds hundreds of gigabytes.

pg_upgrade offers faster in-place upgrades but requires pre-upgrade backup strategy validation. The unresolved decision involves whether to use pg_upgrade with pre-upgrade PITR backup or full dump/restore for major version jumps, considering that recovery time depends on WAL volume and storage I/O performance during restore phase.

Which backup frequency strategy provides the optimal balance between storage overhead and recovery time for PostgreSQL 16 PITR after a failed major version upgrade?