Question
PostgreSQL PITR Recovery Time Objective After Failed Major Version Upgrade
Cig Nea
0 reputation · 17 Jun 2023, 05:55 UTC
49.7K views1
Loading…
0 reputation · 17 Jun 2023, 05:55 UTC
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?
35775 reputation · 17 Jun 2023, 09:22 UTC
The optimal strategy is to take a base backup roughly every six hours while continuously archiving WAL. This limits the amount of WAL that must be replayed during restore, keeping storage overhead reasonable and ensuring the I/O load during recovery does not exceed the system’s capacity.
PostgreSQL 16 point‑in‑time recovery requires a complete base backup plus every WAL segment generated after that backup. Recovery time is driven by the size of the WAL volume and the storage I/O performance during the restore phase.
postgresqlpg_basebackup or a physical replication slot.These steps balance storage consumption against the amount of WAL that must be replayed, which directly influences the achievable recovery time.
To fine‑tune the interval, please provide the expected WAL volume (size) generated during the upgrade period or the target system’s restore I/O throughput, as this information may shift the optimal backup frequency.
Use comments to ask for clarification. Post a solution as an answer.
35,775 reputation · 17 Jun 2023, 14:18 UTC
Restoring a PostgreSQL 15 base backup with its archived WAL recovers a PostgreSQL 15 cluster only; it does not produce a usable PostgreSQL 16 data directory. Cross-major-version WAL replay is not supported, so after any failed pg_upgrade to version 16, the restored 15 cluster must be upgraded again before production use.
SELECT version(); to confirm the restored cluster reports PostgreSQL 15.pg_upgrade against the restored 15 cluster, then measure total restore-plus-upgrade time against the RTO.If pg_upgrade --link was used and failed, verify the old cluster can still start before retrying, because hard-linked files share storage between versions.