PostgreSQL recipes

• Switch the WAL segmentWAL & Checkpoint
• Run a manual CHECKPOINTWAL & Checkpoint
• Show WAL size and current LSNWAL & Checkpoint
• Show archive lag (WAL not yet archived)WAL & Checkpoint
• Add a new tablespaceStorage
• Move a table to another tablespaceStorage
• Move an index to another tablespaceStorage
• Query database and table sizeStorage
• Estimate table bloatStorage
• VACUUM ANALYZE a specific tableMaintenance
• Run an aggressive VACUUM FREEZEMaintenance
• Rebuild an index without locking (REINDEX CONCURRENTLY)Maintenance
• Tune autovacuum for a specific tableMaintenance
• Increase a column's statistics targetMaintenance
• Change a parameter and reload (ALTER SYSTEM)Parameters
• Change a parameter for the session (SET)Parameters
• Show the current parameter valueParameters
• Build a physical replica (pg_basebackup)Replication
• Promote a replica to primaryReplication
• Show replication lagReplication
• Create and drop a replication slotReplication
• Set up a logical replication publication and subscriptionReplication
• Terminate a backend (pg_terminate_backend)Sessions
• Cancel a running query (pg_cancel_backend)Sessions
• Show active and blocking queriesSessions
• Add an extensionExtensions
• List installed extensionsExtensions
• Show Patroni cluster status, topology and historyPatroni
• Perform a planned switchover with PatroniPatroni
• Perform a manual failover with PatroniPatroni
• Rebuild a broken replica with Patroni (reinit)Patroni
• Change cluster-wide parameters in Patroni (edit-config) and apply pending restartsPatroni
• Put Patroni into maintenance mode and back (pause/resume)Patroni
• Check Patroni DCS (etcd/Consul) and REST API healthPatroni
• Configure WAL archiving for pgBackRest (archive-push/archive-get)Backup & PITR
• Create the pgBackRest stanza and verify archiving (stanza-create/check)Backup & PITR
• Take full, differential and incremental backups with pgBackRestBackup & PITR
• Show the pgBackRest backup catalog and WAL archive range (info)Backup & PITR
• Restore the latest backup with pgBackRestBackup & PITR
• Restore to a point in time with pgBackRest (PITR, --type=time)Backup & PITR
• Quickly rebuild a standby with pgBackRest delta restoreBackup & PITR
• Configure pgBackRest retention and run expireBackup & PITR
• Configure a standby manually (standby.signal + primary_conninfo)Replication
• Monitor WAL receive and replay lag on the standby (pg_stat_wal_receiver)Replication
• Rejoin the old primary as a replica after failover with pg_rewindReplication
• Configure synchronous replication with quorum (ANY) or priority (FIRST)Replication
• Monitor WAL retained by replication slots and safely drop an abandoned slotReplication
• Limit the WAL that slots can retain (max_slot_wal_keep_size)Replication
• Add a table to a publication and refresh the subscription (REFRESH PUBLICATION)Logical Replication
• Monitor logical replication (pg_stat_subscription, apply errors, slot lag)Logical Replication
• Resolve a logical replication conflict (disable_on_error, SKIP)Logical Replication
• Synchronize sequence values to the subscriber in logical replicationLogical Replication
• Remove a subscription and clean up an orphaned logical slot on the publisherLogical Replication
• Major version upgrade with pg_upgrade --link (end-to-end runbook)Upgrade
• Near-zero-downtime major version upgrade with logical replicationUpgrade
• Show PgBouncer pools, statistics and connections (SHOW POOLS/STATS/CLIENTS/SERVERS)PgBouncer
• Hold connections during maintenance with PgBouncer (PAUSE/RESUME) and reload configuration (RELOAD)PgBouncer
• PgBouncer pool_mode choice and basic sizingPgBouncer
• Find the most expensive queries (pg_stat_statements: total and mean time)Performance
• Find queries driving disk I/O, temp files and WAL (pg_stat_statements)Performance
• Reset pg_stat_statements statistics (all or a single query)Performance
• Log execution plans of slow queries with auto_explainPerformance
• Read a plan in depth with EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL)Performance
• Catch plan regressions by queryid (pg_stat_statements snapshot comparison)Performance
• Check parallel query capacity and current worker usagePerformance
• Measure the impact of JIT compilation on query timePerformance
• Build the blocking tree (find the root blocker)Sessions
• Inspect lock waits in detail by object and lock mode (pg_locks)Sessions
• Find long-running and 'idle in transaction' transactionsSessions
• Terminate long 'idle in transaction' sessions in bulk, in a controlled waySessions
• Set statement_timeout / lock_timeout per role and databaseSessions
• Apply DDL safely with lock_timeout and retries (avoid lock queues)Sessions
• Measure connection usage against max_connectionsSessions
• Monitor the progress of running autovacuum/VACUUM operationsMaintenance
• Measure transaction ID wraparound risk (XID age per database and table)Maintenance
• Find the xmin horizon holders that stop VACUUM from removing dead tuplesMaintenance
• Measure table and index bloat with pgstattupleMaintenance
• Reorganize a bloated table without long locks (pg_repack)Maintenance
• Find and clean up INVALID indexesMaintenance
• Create extended statistics for correlated columns (CREATE STATISTICS)Maintenance
• Find tables with stale statistics (ANALYZE strategy)Maintenance
• Check visibility map coverage and Index Only Scan efficiencyMaintenance
• Create a monthly range-partitioned table (generate partitions in a loop)Partitioning
• Attach a prepared table as a partition with a short lock (ATTACH PARTITION)Partitioning
• Detach an old partition without blocking live traffic (DETACH CONCURRENTLY)Partitioning
• Default partition pitfall: new partition fails with 'would be violated by some row'Partitioning
• Verify that partition pruning works (plan time and run time)Partitioning
• Automate partition creation and retention with pg_partmanPartitioning
• Build a least-privilege role hierarchy (GRANT + ALTER DEFAULT PRIVILEGES)Security
• Set up tenant isolation with row-level securitySecurity
• Validate pg_hba.conf rule order and reload safelySecurity
• Enable SSL/TLS and verify encrypted connections (pg_stat_ssl)Security
• Migrate from md5 passwords to scram-sha-256Security
• Keep role- and object-level audit logs with pgauditSecurity
• Review memory settings (shared_buffers, work_mem, maintenance_work_mem)Parameters
• Show parameters pending restart and configuration file errorsParameters
• Review checkpoint behavior (PG17+ pg_stat_checkpointer, PG16 and earlier pg_stat_bgwriter)Parameters
• List table size broken down into heap / index / TOAST, and the largest indexesStorage
• Find extensions with pending updates and update themExtensions
• Analyze where I/O comes from with pg_stat_io (backend type, object, context)Extensions