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
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/
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.
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;
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;
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')
);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]';
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.
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
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
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
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>;
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;
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;
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, ''));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)
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.
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>);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;
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;
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.
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
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
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;
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;