Yearly PostgreSQL Continuity and Upgrade Readiness

1. Prove continuity and recovery

Run an approved exercise that restores the service to an isolated environment, validates application behavior, measures actual recovery time and recovery point, and records gaps. Include credentials, encryption keys, DNS/network steps, monitoring, dependent services, and the decision authority for cutover.

1. Select a scenario and target point with the service owner.
2. Confirm backup/WAL availability and protect production from the exercise.
3. Restore to a clean isolated host and record timestamps for every step.
4. Run data-integrity and application smoke checks with the owner.
5. Compare observed recovery against objectives and track remediations to closure.

2. Plan a major-version upgrade

Inventory database size, extensions, collation behavior, drivers, application SQL, replication topology, and external integrations. Review the selected upgrade method and version-specific release notes. Test the migration on a recent restored copy, measure duration and rollback options, and obtain a go/no-go decision before scheduling.

SELECT current_setting('server_version') AS server_version;
SELECT extname, extversion FROM pg_extension ORDER BY extname;
SELECT datname, datcollversion, pg_database_collation_actual_version(oid)
FROM pg_database
WHERE datistemplate = false;

Upgrade commands and collation functions vary by PostgreSQL version and platform; validate the exact process with your hosting/provider documentation and test it end to end.

3. Refresh annual controls

• Confirm supported PostgreSQL version, patch cadence, and extension support.
• Review privileged accounts, role memberships, service ownership, and secret rotation.
• Revalidate backup retention, encryption, restore access, and retention deletion.
• Review data retention, archival, legal holds, and decommissioned services.
• Update the topology diagram, contacts, change runbooks, and emergency access process.

Yearly deliverables

Keep the signed exercise report, measured RTO/RPO, upgrade test evidence, support-lifecycle plan, approved risk register, and dated remediation tickets. Assign owners and target dates; a checklist without evidence is not a recovery test.