General Postgres HowTo docs

HOW TO...

  • Troubleshoot and mitigate disk space issues
  • Index maintenance
    • find and fix invalid indexes
    • find and remove unused indexes
    • find and remove redundant indexes
    • analyze and mitigate index bloat
  • Analyze and mitigate table bloat
  • Analyze and mitigate bloat of system catalogs
  • Connection pooling
    • ...
  • Use logical replication
  • Load data to Postgres
  • Export data from Postgres
  • Connect to Postgres
  • Install extension
  • Upgrades
    • Minor upgrades
    • Major upgrades
    • Upgrade extension
  • Backups
    • Backups using WAL-G
    • Backups using pgBackRest
    • PITR
    • How to restore
    • How to test backups
  • Corruption
    • Data checksums
    • btree corruption
    • heap corruption
    • VM corruption
    • Silent constraints (UK, FK, etc.) violation
    • monitor for corruption
    • mitigate corruption
  • Analyze lock trees
  • Troubleshoot deadlocks
  • Query analysis and optimization
    • Macro with pgss, pgsk
    • Macro with wait event analysis aka ASH
    • Macro with logs, auto_explain, pgBadger
    • Micro with EXPLAIN
  • XID and multiXID wraparound
    • Monitor and prevent
    • Fix if happened
  • Configure and use Patroni
  • Restart Postgres with minimal or with zero downtime
  • Switchover with minimal or with zero downtime
  • Clone Postgres
  • DB experiments and benchmarks
    • ...
  • Column tetris
  • Long-runnning transactions holding locks
  • xmin horizon affecting vacuum
  • implement automated partitioning
  • perf recipes
  • troubleshoot lockmanager spikes, fastpath
  • handle queue-like workloads in postgres
  • Checkpoint tuning
  • Capacity planning
  • DR planning
  • Postgres doesn't stop
  • Postgres doean't start
  • Troubleshoot segfault
  • troubleshoot slow GIN, fast inserts
  • ...

Good resources