dataiotech Labs
Home/Checklists/postgresql-19-upgrade-checklist
9 Faz · PostgreSQL 19 Upgrade Kontrol Listesi
10 min duration

PostgreSQL 19 Upgrade Checklist: 9-Phase Senior DBA Migration Playbook

pg_upgrade Blockers, btree_gist Remediation, JIT Evaluation, Sequence Synchronization, and Zero-Data-Loss Rollback

Checklist Progress: 0 / 24
%0
Phase 0: Decision & TimingCRITICAL

1. Current PostgreSQL Version EOL Status Verified?

PostgreSQL 14 reached EOL on November 12, 2026. Transitioning from PG 14 or older is urgent. PG 15 is supported through Nov 2027; PG 16-18 upgrades should be driven by staging validation.

SELECT version();
-- https://www.postgresql.org/support/versioning/
Phase 0: Decision & TimingHIGH

2. Minor Release Cadence & Managed Cloud Provider Support Verified?

Waiting for 19.1/19.2 is not rigid dogma; adjust based on current EOL urgency, cloud provider readiness (RDS/Cloud SQL), and staging trial stability.

Phase 1: Inventory & DependenciesCRITICAL

3. All Installed Extensions Verified for PostgreSQL 19 Binary Compatibility?

C-language extensions require binaries recompiled against PostgreSQL 19 headers due to IndexAmRoutine refactoring and C11 compiler requirements.

SELECT extname, extversion FROM pg_extension;
Phase 1: Inventory & DependenciesHIGH

4. Database Encoding, Locale Provider, and LC_CTYPE Settings Recorded?

Character classification for FTS and pg_trgm now follows the database locale provider (ICU/Builtin). Audit database catalog settings.

SELECT datname, pg_encoding_to_char(encoding), datlocprovider, datctype FROM pg_database;
Phase 2: pg_upgrade Blocker AuditingCRITICAL

5. btree_gist inet/cidr Indexes Audited and Remediated Before pg_upgrade?

pg_upgrade halts if btree_gist indexes exist on inet/cidr columns. Identify constraint dependencies, drop cleanly before dry-run, and recreate on target with native GiST.

SELECT c.relname AS index_name, t.relname AS table_name, con.conname AS constraint_name, pg_get_indexdef(c.oid) AS index_def
FROM pg_index x
JOIN pg_class c ON c.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_am am ON am.oid = c.relam
LEFT JOIN pg_constraint con ON con.conindid = c.oid
WHERE am.amname = 'gist' AND EXISTS (
  SELECT 1 FROM pg_attribute a JOIN pg_type ty ON ty.oid = a.atttypid
  WHERE a.attrelid = c.oid AND ty.typname IN ('inet', 'cidr')
);
Phase 2: pg_upgrade Blocker AuditingCRITICAL

6. No Carriage Return / Newline Characters in Database, Role, or Tablespace Names?

pg_upgrade aborts if database, role, or tablespace names contain carriage returns or newlines.

SELECT datname FROM pg_database WHERE datname ~ '[\r\n]';
SELECT rolname FROM pg_roles WHERE rolname ~ '[\r\n]';
Phase 2: pg_upgrade Blocker AuditingCRITICAL

7. Deprecated RADIUS Authentication and MULE_INTERNAL Encoding Cleaned Up?

PostgreSQL 19 removes UDP RADIUS and MULE_INTERNAL encoding. Migrate auth methods in pg_hba.conf to SCRAM or LDAP.

Phase 2: pg_upgrade Blocker AuditingCRITICAL

8. pg_upgrade --check Dry-Run Simulation Succeeded with Zero Errors?

Non-destructive pre-flight check that validates binary compatibility and catalog integrity before live migration.

pg_upgrade --old-datadir=/var/lib/postgresql/18/data --new-datadir=/var/lib/postgresql/19/data --old-bindir=/usr/lib/postgresql/18/bin --new-bindir=/usr/lib/postgresql/19/bin --check
Phase 3: Configuration & SizingHIGH

9. Retired io_workers Replaced with Dynamic io_min_workers and io_max_workers?

Static io_workers from PG 18 was removed in favor of auto-scaling io_min_workers and io_max_workers.

# postgresql.conf
io_min_workers = 4
io_max_workers = 16
io_worker_idle_timeout = 60s
Phase 3: Configuration & SizingHIGH

10. Custom max_locks_per_transaction Doubled to Retain Lock Table Capacity?

Default raised from 64 to 128. Due to internal lock table changes, double any custom setting to preserve prior headroom.

# postgresql.conf (Örn. eski değer 256 ise):
max_locks_per_transaction = 512
Phase 3: Configuration & SizingHIGH

11. JIT Disabled by Default; Evaluated and Benchmarked for Analytical Workloads?

JIT is disabled by default due to unreliable compilation cost heuristics. Profile analytical queries in staging before enabling.

SHOW jit;
-- Staging testi: SET jit = on; EXPLAIN (ANALYZE, BUFFERS) <query>;
Phase 3: Configuration & SizingMEDIUM

12. log_lock_waits Enabled by Default; Disk Log Ingestion Volume Assessed?

Transactions waiting longer than deadlock_timeout now emit log entries by default. Verify log storage budgets.

SHOW log_lock_waits;
SHOW deadlock_timeout;
Phase 4: Data, Index & Stemmer AlignmentHIGH

13. FTS and pg_trgm Indexes Scheduled for REINDEX Post-Upgrade?

Because character classification follows the database locale provider, schedule REINDEX on text search GIN indexes.

REINDEX INDEX CONCURRENTLY idx_articles_fts_gin;
Phase 4: Data, Index & Stemmer AlignmentHIGH

14. Multilingual Stemmer Updates: Persisted tsvector Columns Recalculated?

Stemmer logic updated for Turkish, English, German, and others. Recompute persisted tsvector table columns via UPDATE.

UPDATE articles SET fts_vector = to_tsvector('turkish', coalesce(title, '') || ' ' || coalesce(body, ''));
Phase 5: Application & ReplicationHIGH

15. json_array() Now Returns [] on Empty Results; Audited for Application IS NULL Checks?

Calling json_array() on empty subqueries now returns an empty array ([]) instead of NULL. Update application assertions.

SELECT json_array((SELECT 1 WHERE false)); -- Returns [] in PG 19 (was NULL)
Phase 5: Application & ReplicationHIGH

16. postgres_fdw Sessions Strictly Inherit Local READ ONLY Transaction Context?

Foreign transactions now inherit READ ONLY / DEFERRABLE state. Ensure write calls are not wrapped in read-only blocks.

Phase 5: Application & ReplicationCRITICAL

17. 18 -> 19 Logical Replication Cutover: Sequence Counters Synchronized Explicitly?

Logical replication does not stream per-nextval increments. In 18 -> 19 cutovers, drain writes, verify zero lag, and align sequences via setval().

-- Kaynakta: SELECT last_value, is_called FROM order_id_seq;
-- Hedefte: SELECT setval('order_id_seq', <last_value>, <is_called>);
Phase 6: Observability & TelemetryMEDIUM

18. Renamed Wait Events and Subscription Telemetry Updated in Monitoring Dashboards?

Wait events BUFFERPIN -> BUFFER, BufferPin -> BufferCleanup, and sync_error_count -> sync_table_error_count. Update monitoring queries.

SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY 1, 2;
Phase 6: Observability & TelemetryMEDIUM

19. Transaction ID Wraparound Alert Thresholds Adjusted for the New 100M Headroom?

Wraparound warnings now trigger at 100M remaining transactions (up from 40M). Review on-call paging thresholds.

SELECT datname, age(datfrozenxid), 2147483648 - age(datfrozenxid) AS remaining_xids FROM pg_database;
Phase 7: Staging Rehearsal & BenchmarksCRITICAL

20. End-to-End Migration Rehearsal Completed on Masked Production Snapshot?

Measure upgrade downtime, disk overhead, and rollback execution using realistic data volumes on a dedicated staging host.

Phase 8: Cutover Execution & RollbackCRITICAL

21. Pre-Cutover Validated Cold/Hot Backup Verified with Proven Rollback Path?

Hard-link upgrades mutate old data files; snapshot or independent verified backup is strictly mandatory for rollback safety.

pgbackrest --stanza=db-prod --type=full backup
Phase 8: Cutover Execution & RollbackCRITICAL

22. Optimizer Statistics Refreshed Rapidly via vacuumdb --analyze-in-stages?

pg_upgrade leaves statistics empty. Run staged analyze immediately after upgrade to prevent query execution plan regressions.

/usr/lib/postgresql/19/bin/vacuumdb --all --analyze-in-stages
Phase 9: Post-Cutover & Staged RolloutHIGH

23. REPACK (CONCURRENTLY) Tested on PK Tables with Safe lock_timeout Limits?

Requires Primary Key or unique replica identity. The final file swap takes a brief ACCESS EXCLUSIVE lock; enforce lock_timeout and ensure 2x disk headroom.

SET lock_timeout = '5s';
REPACK (CONCURRENTLY) orders;
SELECT * FROM pg_stat_progress_repack;
Phase 9: Post-Cutover & Staged RolloutHIGH

24. WAIT FOR LSN Implemented with MODE standby_replay, Finite TIMEOUT & Primary Fallback?

Avoid hanging queries during replica lag spikes. Specify finite TIMEOUT with MODE standby_replay, and implement graceful fallback to primary.

-- Standby session:
WAIT FOR LSN '0/3B19A40' MODE 'standby_replay' TIMEOUT '5000ms';
SELECT * FROM user_orders WHERE user_id = 101;