PostgreSQL DBA Cheat Sheet

Here’s a PostgreSQL DBA Cheat Sheet

🐘 PostgreSQL DBA Interview Cheat Sheet

🔹 1. Backup & Recovery

• Logical backup:

  pg_dump -U user dbname > backup.sql
  pg_dumpall -U user > alldb.sql
  

• Physical backup:

  pg_basebackup -D /backup -Fp -Xs -P -U repl_user
  

• Point-in-Time Recovery (PITR): WAL archiving + restore to timestamp.

🔹 2. MVCC

• Readers don’t block writers.
• Each update creates a new row version.
• Old tuples cleared by VACUUM.

🔹 3. Maintenance Commands

• Vacuum:

  VACUUM;
  

• Analyze:

  ANALYZE;
  

• Reindex:

  REINDEX TABLE tablename;
  

• Cluster:

  CLUSTER tablename USING indexname;
  

🔹 4. Replication Setup

• Enable in postgresql.conf:

  wal_level = replica
  max_wal_senders = 5
  archive_mode = on
  

• On standby (recovery.conf or standby settings):

  primary_conninfo = 'host=IP user=replica password=xxxx'
  

• Start replication: pg_basebackup.

🔹 5. Performance Tuning

• Shared buffers: ~25% of RAM

• Work_mem: 4–64MB (per sort/aggregation)

• Effective_cache_size: ~75% of RAM

• Explain query:

  EXPLAIN ANALYZE SELECT ...;
  

• Indexing types:

  • B-tree → Equality & range
  • GIN → Full text / JSON
  • BRIN → Large sequential

🔹 6. WAL (Write Ahead Log)

• Changes written to WAL before data files.
• Used for crash recovery, replication, PITR.

🔹 7. Security

• pg_hba.conf → Client authentication rules.

• Use SCRAM-SHA-256 instead of MD5.

• Enable SSL:

  ssl = on
  

• Role management:

  CREATE ROLE analyst LOGIN PASSWORD 'secret';
  GRANT CONNECT ON DATABASE db TO analyst;
  

🔹 8. Key Views for Monitoring

• Active queries:

  SELECT * FROM pg_stat_activity;
  

• Database stats:

  SELECT * FROM pg_stat_database;
  

• Replication lag:

  SELECT * FROM pg_stat_replication;
  

• Lock monitoring:

  SELECT * FROM pg_locks;
  

🔹 9. Tablespace vs Schema vs Database

• Database = Collection of schemas.
• Schema = Logical namespace inside a DB.
• Tablespace = Physical storage location.

🔹 10. Monitoring Tools

• Extensions: pg_stat_statements, auto_explain.
• Tools: pgAdmin, Prometheus + Grafana, pganalyze.

Always say “I use EXPLAIN/pg_stat_statements to analyze queries, tune configs like shared_buffers, and apply proper indexing strategies” — it sounds very practical to interviewers.