Upgrades, Majors and Minors

# 1. Verify the new package is available
apt list --upgradeable | grep postgresql-14
 
# 2. Install the new minor version
apt-get install --only-upgrade postgresql-14
 
# 3. Restart to activate (brief downtime)
pg_ctlcluster 14 main restart
 
# 4. Confirm version
psql -c 'SELECT version();'

# Pre-flight: install both the old and new Postgres binaries
apt-get install postgresql-14 postgresql-16
 
# Stop the old cluster
pg_ctlcluster 14 main stop
 
# Run pg_upgrade (--link mode: hard-links data files, fastest, but old cluster unusable after)
pg_upgrade \
  --old-datadir /var/lib/postgresql/14/main \
  --new-datadir /var/lib/postgresql/16/main \
  --old-bindir /usr/lib/postgresql/14/bin \
  --new-bindir /usr/lib/postgresql/16/bin \
  --link
 
# Start the new cluster
pg_ctlcluster 16 main start
 
# Post-upgrade: run ANALYZE (statistics are not migrated)
/usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages
 
# Optionally: rebuild indexes (some may be invalidated across major versions)
# pg_upgrade generates a reindex_hash.sql script when needed

-- On old cluster (source, e.g., Postgres 14)
-- wal_level must be logical
ALTER SYSTEM SET wal_level = 'logical';
-- Restart required
 
CREATE PUBLICATION upgrade_pub FOR ALL TABLES;
 
-- On new cluster (target, e.g., Postgres 16)
-- Apply all DDL first: pg_dump --schema-only | psql new_db
 
CREATE SUBSCRIPTION upgrade_sub
  CONNECTION 'host=old-db user=replicator dbname=app'
  PUBLICATION upgrade_pub;
 
-- Monitor replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS lag;
 
-- When lag is near zero: cutover
-- 1. Put old cluster in read-only mode (or stop writes at app layer)
-- 2. Wait for subscriber to catch up (lag → 0)
-- 3. Drop subscription
-- 4. Switch application connection strings to new cluster

-- On old cluster: dump sequence values
SELECT 'SELECT setval(' || quote_literal(schemaname || '.' || sequencename)
  || ', ' || last_value || ');'
FROM pg_sequences;
 
-- Run the output on the new cluster after replication catches up

-- Check for deprecated features that may break on the target version
-- pg_upgrade --check covers most cases, but also verify:
 
-- 1. Extensions: all must be available in the new version
SELECT name, default_version FROM pg_available_extensions;
 
-- 2. Functions with deprecated signatures (check release notes)
 
-- 3. Replication slots that must be dropped before pg_upgrade
SELECT slot_name, slot_type, active FROM pg_replication_slots;
 
-- 4. Prepared transactions (must be committed/rolled back)
SELECT gid FROM pg_prepared_xacts;
 
-- 5. pg_upgrade check mode
pg_upgrade --check \
  --old-datadir /var/lib/postgresql/14/main \
  --new-datadir /var/lib/postgresql/16/main \
  --old-bindir /usr/lib/postgresql/14/bin \
  --new-bindir /usr/lib/postgresql/16/bin

-- ANALYZE is mandatory after pg_upgrade (statistics not transferred)
-- Use analyze-in-stages for large databases: runs quickly first, then thoroughly
-- vacuumdb --all --analyze-in-stages (run as postgres OS user)
 
-- Rebuild any indexes that pg_upgrade flagged as needing rebuild
-- (Hash indexes before Postgres 10, some GIN/GiST across certain versions)
 
-- Update pg_hba.conf and postgresql.conf for new version defaults
-- Notable changes per version: check the official release notes
 
-- Re-enable autovacuum (sometimes disabled before upgrade for faster pg_upgrade)
ALTER SYSTEM RESET autovacuum;
SELECT pg_reload_conf();
 
-- Verify logical replication subscriptions (if any) are working
SELECT subname, subenabled, subslotname FROM pg_subscription;