Question
How can you recover safely from a failed PostgreSQL pg_upgrade when using link mode?
Cig Moss
0 reputation · 27 Nov 2021, 00:52 UTC
83.5K views1
Loading…
0 reputation · 27 Nov 2021, 00:52 UTC
After a pg_upgrade operation fails, the administrator may be left with an inaccessible old cluster when link mode was used, or a partially upgraded state that prevents starting the new server. The goal is to determine a safe recovery path that preserves data integrity without requiring a full dump/restore, while considering constraints such as filesystem layout, available backup methods, and the impact on downtime.
What conditions must be verified before attempting to retry pg_upgrade after a failure? How can you confirm that the old data files remain usable for a manual restore or for switching to a different upgrade mode? Is it advisable to proceed with a second pg_upgrade invocation without first addressing the cause of the initial failure?
35275 reputation · 27 Nov 2021, 11:36 UTC
foundation
postgresqlpg_control timestamps remain unchanged.pg_controldata on the original data directory and compare the Latest checkpoint location and State of database system values with those recorded before the upgrade attempt.SELECT version(); and SELECT count(*) FROM pg_tables; to confirm user data is accessible and unchanged.Restore the original data directory from a baseline backup taken before the upgrade (e.g., pg_basebackup or a filesystem snapshot). After restoration, repeat the verification steps above before attempting another pg_upgrade.
Do not run a second pg_upgrade until you have identified and corrected the cause of the first failure (extension incompatibility, insufficient disk space, conflicting configuration, etc.). Retrying without fixing the underlying issue risks repeating the same error or causing corruption.
Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 27 Nov 2021, 02:40 UTC
While the recovery steps mentioned are correct, it is critical to emphasize the specific risk of --link mode: because the new cluster uses hard links to the original data files, any process that modifies these files in the new version can potentially corrupt the old cluster. This is why ensuring all new cluster processes are fully terminated before restarting the old one is non-negotiable.
To prevent these failures from occurring in the first place, always run the upgrade with the --check flag. This performs a compatibility dry run—checking for missing libraries, incompatible extensions, and configuration mismatches—without modifying any data. Verifying the output of --check is the most effective way to avoid the recovery scenarios discussed here.