Replication, Pooling & Operations

-- On primary: check replication status
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- On standby: check how far behind
SELECT
  now() - pg_last_xact_replay_timestamp() AS replay_lag;

-- On publisher (source):
CREATE PUBLICATION my_pub FOR TABLE orders, users;
-- Or replicate all tables:
-- CREATE PUBLICATION my_pub FOR ALL TABLES;

-- On subscriber (target):
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=primary dbname=mydb user=replicator'
  PUBLICATION my_pub;

-- Monitor subscription status
SELECT * FROM pg_stat_subscription;

-- Promote a standby to primary
SELECT pg_promote();

-- Or from command line:
-- pg_ctl promote -D /var/lib/postgresql/data

-- After promotion, old primary cannot rejoin directly
-- Must be re-basebackuped as a new standby

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
default_pool_size = 50
max_client_conn = 5000
server_idle_timeout = 300

# Application connects to PgBouncer on port 6432
# PgBouncer maintains 50 actual connections to PostgreSQL on port 5432

-- Check autovacuum status per table
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Autovacuum triggers when:
-- dead_tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * n_live_tup)
-- Default: 50 + (0.2 * n_live_tup) — 20% of table must be dead

-- For high-churn tables, lower the threshold:
ALTER TABLE hot_events SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- trigger at 1% dead
  autovacuum_vacuum_threshold = 1000
);

-- Check if autovacuum is keeping up
SELECT relname, n_dead_tup,
  n_dead_tup::float / nullif(n_live_tup, 0) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_ratio DESC;

-- Check transaction ID age per database
-- WARNING threshold: age > 500 million
-- CRITICAL threshold: age > 1 billion
SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  CASE
    WHEN age(datfrozenxid) > 1000000000 THEN '🔴 CRITICAL'
    WHEN age(datfrozenxid) > 500000000 THEN '🟡 WARNING'
    ELSE '🟢 OK'
  END AS status
FROM pg_database
ORDER BY xid_age DESC;

-- Check per-table (which table is blocking freeze?)
SELECT
  relname,
  age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY xid_age DESC
LIMIT 10;

-- Anti-wraparound VACUUM is triggered automatically when age > autovacuum_freeze_max_age (default: 200M)
-- But if it can't complete (long transactions blocking it), age keeps growing

# Initial full backup
pgbackrest --stanza=main --type=full backup

# Daily incremental backup (only changed blocks)
pgbackrest --stanza=main --type=incr backup

# Restore to a specific point in time
pgbackrest --stanza=main --type=time \
  --target="2024-03-15 15:46:00+00" \
  --target-action=promote \
  restore

# List available backups
pgbackrest --stanza=main info