PostgreSQL Support

Major upgrade 14 to 18 with pg_upgrade: inventory, dry run, link mode, statistics

# PostgreSQL support runbook: major upgrade 14 -> 18 with pg_upgrade.
# Run as the postgres OS user. Paths come from variables so nothing is hardcoded.
OLD_BIN=/usr/lib/postgresql/14/bin   OLD_DATA=/var/lib/postgresql/14/main
NEW_BIN=/usr/lib/postgresql/18/bin   NEW_DATA=/var/lib/postgresql/18/main

# 1. Roles still stored as MD5 hashes: md5 auth is deprecated in PostgreSQL 18,
#    so re-set these passwords under scram-sha-256 before or after the move.
psql -X -d postgres -c "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';"

# 2. Extension inventory per database: every one must be installed for 18 first.
psql -X -d app -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;"

# 3. PostgreSQL 18 initdb enables data checksums by default. pg_upgrade needs both
#    clusters to match: check the old one with  SHOW data_checksums;
"$NEW_BIN/initdb" -D "$NEW_DATA" --encoding=UTF8 --locale=C.UTF-8 --no-data-checksums

# 4. Dry run. Changes nothing, reports every incompatibility it finds.
"$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" -d "$OLD_DATA" -D "$NEW_DATA" \
    --jobs=8 --check

# 5. In the window, after a verified backup. --link avoids copying data files;
#    once the new cluster starts, rollback means the backup or the old standby.
"$NEW_BIN/pg_upgrade" -b "$OLD_BIN" -B "$NEW_BIN" -d "$OLD_DATA" -D "$NEW_DATA" \
    --jobs=8 --link

# 6. 18 carries planner statistics across the upgrade; extended statistics are
#    not carried, so only fill what is missing instead of a full re-analyze.
"$NEW_BIN/vacuumdb" --all --analyze-in-stages --missing-stats-only --jobs=8

PostgreSQL support diagnostics: top statements, plan proof and I/O attribution

-- PostgreSQL DBA support diagnostics: which statements own the database time?
-- Requires pg_stat_statements in shared_preload_libraries plus CREATE EXTENSION.
SELECT LEFT(query, 80)                                  AS statement,
       calls,
       ROUND(total_exec_time / 1000)                    AS total_s,
       ROUND(mean_exec_time::numeric, 2)                AS mean_ms,
       shared_blks_read                                 AS blocks_from_disk,
       temp_blks_written                                AS temp_spill_blocks,
       pg_size_pretty(wal_bytes)                        AS wal_generated
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Prove the cause on one execution. In PostgreSQL 18, ANALYZE reports buffers
-- automatically; SETTINGS lists every planner setting that differs from default.
EXPLAIN (ANALYZE, SETTINGS, WAL)
SELECT customer_id, sum(amount)
FROM orders
WHERE created_at >= now() - interval '1 day'
GROUP BY customer_id;

-- I/O by backend type and context (PostgreSQL 16+): is VACUUM or the client reading?
SELECT backend_type, object, context, reads, writes, extends, evictions
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY reads DESC;

PostgreSQL 18 baseline: default, reason and reload or restart per line

# postgresql.conf -- PostgreSQL support baseline, PostgreSQL 18, dedicated 64 GB / 16 vCPU host, NVMe (illustrative)
# Format: parameter = proposed     # default -> why ; reload or restart
shared_buffers                   = 16GB      # 128MB -> ~25% of RAM, the OS cache does the rest ; restart
effective_cache_size             = 48GB      # 4GB -> planner hint only, allocates nothing ; reload
work_mem                         = 32MB      # 4MB -> per sort/hash node, multiply by concurrency ; reload
maintenance_work_mem             = 2GB       # 64MB -> faster VACUUM and CREATE INDEX ; reload
io_method                        = worker    # worker -> keep; io_uring only on tested Linux kernels ; restart
io_workers                       = 8         # 3 -> more async I/O workers for scans and VACUUM ; reload
effective_io_concurrency         = 64        # 16 -> NVMe sustains deep queues ; reload
random_page_cost                 = 1.1       # 4.0 -> SSD random reads cost close to sequential ; reload
max_wal_size                     = 16GB      # 1GB -> fewer forced checkpoints under write bursts ; reload
checkpoint_timeout               = 15min     # 5min -> smoother I/O, longer crash recovery ; reload
wal_compression                  = lz4       # off -> smaller full-page images in WAL ; reload
autovacuum_max_workers           = 6         # 3 -> reload-able in 18, capped by autovacuum_worker_slots ; reload
autovacuum_vacuum_scale_factor   = 0.05      # 0.2 -> vacuum large tables before bloat compounds ; reload
autovacuum_vacuum_cost_limit     = 2000      # -1 (uses 200) -> let workers keep up on fast storage ; reload
idle_in_transaction_session_timeout = '5min' # 0 -> stop forgotten sessions pinning the xmin horizon ; reload
max_slot_wal_keep_size           = 100GB     # -1 -> cap WAL a broken slot can retain ; reload
track_io_timing                  = on        # off -> I/O time in EXPLAIN and pg_stat_statements ; reload
shared_preload_libraries         = 'pg_stat_statements'  # evidence for every diagnosis ; restart

PgBouncer in transaction mode with SCRAM and auth_query

; /etc/pgbouncer/pgbouncer.ini -- PostgreSQL support baseline for PgBouncer 1.26
; 1.26.0 (September 2026) fixes three CVEs: upgrade older poolers first.
[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
listen_addr          = 0.0.0.0
listen_port          = 6432
pool_mode            = transaction   ; one server connection per transaction, not per client
max_client_conn      = 5000          ; clients are cheap here, backends are not
default_pool_size    = 40            ; ~2-4x vCPU per database/user pair; measure, then adjust
reserve_pool_size    = 5
max_prepared_statements = 200        ; protocol-level prepared statements in transaction mode
auth_type            = scram-sha-256
auth_user            = pgbouncer_auth ; looks up hashes via auth_query, no userlist secrets
auth_query           = SELECT usename, passwd FROM pgbouncer.get_auth($1)
client_tls_sslmode   = require
server_tls_sslmode   = verify-full
server_reset_query   =               ; empty in transaction mode

SCRAM, group roles, pg_hba rules and an audit of powerful roles

-- Enterprise PostgreSQL support hardening baseline (PostgreSQL 15+).
-- 1. SCRAM only. Default since PostgreSQL 14; re-set passwords to rehash old MD5.
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
-- psql:  \password app_rw      (prompts, never echoes the secret)

-- 2. Group roles own privileges; login roles only inherit them.
CREATE ROLE app_read NOLOGIN;
GRANT CONNECT ON DATABASE app TO app_read;
GRANT USAGE ON SCHEMA sales TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_read;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales
  GRANT SELECT ON TABLES TO app_read;
CREATE ROLE svc_reporting LOGIN IN ROLE app_read CONNECTION LIMIT 20;

-- 3. pg_hba.conf: TLS plus SCRAM for named networks, explicit reject last.
--   hostssl  app  svc_reporting  10.20.0.0/16  scram-sha-256
--   host     all  all            0.0.0.0/0     reject

-- 4. Audit: roles that bypass everything, and pgaudit coverage.
SELECT rolname, rolsuper, rolbypassrls, rolcreaterole
FROM pg_roles
WHERE rolsuper OR rolbypassrls OR rolcreaterole;
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements,pgaudit'
--                  pgaudit.log = 'ddl, role'

Wait events, blocking chains and archiver health in one pass

-- PostgreSQL support, Severity 1 step 2: what is the instance waiting on?
SELECT wait_event_type, wait_event, state, count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle'
GROUP BY 1, 2, 3
ORDER BY sessions DESC;

-- Step 3: blocking chains. The root blocker is a pid that blocks others
-- but is not blocked itself; its xact_start shows how long it has held locks.
SELECT a.pid,
       pg_blocking_pids(a.pid)      AS blocked_by,
       a.wait_event_type, a.wait_event,
       now() - a.xact_start         AS xact_age,
       LEFT(a.query, 70)            AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY xact_age DESC;

-- Step 4: WAL archiving health (failures here fill pg_wal and stop the primary).
SELECT archived_count, failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;