PostgreSQL Operations - Street-Level Ops

What experienced PostgreSQL operators know that tutorials don't teach.

Quick Diagnosis Commands

# Connect as postgres superuser
sudo -u postgres psql
psql -U postgres -h 127.0.0.1 -p 5432 -d mydb

# Active queries — what's running right now
SELECT pid, now() - query_start AS duration, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

# Blocking queries — find locks
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.cardinality(pg_blocking_pids(blocked.pid)) > 0;

# Simpler lock query (pg 9.6+)
SELECT pid, pg_blocking_pids(pid) AS blocked_by, query, state
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

# Kill a query (graceful)
SELECT pg_cancel_backend(<pid>);
-- Kill only the query, leaves connection open

# Kill a connection (forceful)
SELECT pg_terminate_backend(<pid>);

# Table sizes
SELECT
  schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

# Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY 2 DESC;

# Connection counts
SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname ORDER BY 2 DESC;
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle';
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction';

# Check autovacuum activity
SELECT schemaname, relname, last_autovacuum, last_autoanalyze, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

# Replication lag (on primary)
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
  write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

# Check WAL position
SELECT pg_current_wal_lsn();     -- current WAL write position (primary)
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();  -- replica
SELECT pg_is_in_recovery();       -- true = this is a replica

# Index usage — find unused indexes
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

# Cache hit rate (should be >99% for OLTP)
SELECT
  sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

Common Scenarios

Scenario 1: Connection Limit Exhausted

Application returns "FATAL: sorry, too many clients already" or "remaining connection slots are reserved for non-replication superuser connections."

Diagnosis:

-- Current connection count vs limit
SELECT count(*) AS current, max_conn AS limit
FROM pg_stat_activity, (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') s;

-- Who's holding connections
SELECT datname, usename, state, count(*) FROM pg_stat_activity
GROUP BY 1,2,3 ORDER BY 4 DESC;

-- Idle connections wasting slots
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle';
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction';

Fix:

# Short-term: kill idle connections
# (Do this carefully — don't kill active queries)
psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
         WHERE state = 'idle' AND query_start < now() - interval '30 minutes';"

# Long-term: deploy PgBouncer (connection pooler) in front of PostgreSQL
# postgresql.conf: reserve some headroom
# max_connections = 200        # default 100, increase carefully
# superuser_reserved_connections = 5  # emergency access

# PgBouncer key settings (pgbouncer.ini)
# pool_mode = transaction       # reuse connections per-transaction (most efficient)
# max_client_conn = 10000       # clients can connect freely
# default_pool_size = 20        # but only N backend connections per db/user

Scenario 2: Table Bloat from Dead Tuples

Queries on a table are slow. Table is 20 GB but only contains 2 GB of real data. Dead tuples from updates/deletes haven't been reclaimed.

Diagnosis:

-- Check dead tuple ratio
SELECT schemaname, relname, n_live_tup, n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Physical size vs estimated live data size
SELECT
  pg_size_pretty(pg_relation_size('mytable')) AS table_size,
  n_live_tup,
  pg_size_pretty(n_live_tup * 200) AS estimated_live  -- rough estimate
FROM pg_stat_user_tables
WHERE relname = 'mytable';

Fix:

-- Manual VACUUM (doesn't lock table, just reclaims dead tuples)
VACUUM mytable;

-- ANALYZE to update statistics after vacuum
VACUUM ANALYZE mytable;

-- Full vacuum — reclaims space to OS, but locks the table exclusively
-- Only use during maintenance windows
VACUUM FULL mytable;

-- pg_repack (preferred over VACUUM FULL — online, no exclusive lock)
-- Install: apt install postgresql-<ver>-repack
pg_repack -d mydb -t mytable

-- Tune autovacuum for high-churn tables
ALTER TABLE mytable SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- vacuum at 1% dead tuples (default 20%)
  autovacuum_analyze_scale_factor = 0.005
);

Scenario 3: Replication Lag Spiking

Read replicas are serving stale data. Replication lag is climbing.

Diagnosis:

-- On primary: check replication state
SELECT client_addr, state, write_lag, flush_lag, replay_lag,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_bytes
FROM pg_stat_replication;

-- On replica: check recovery status
SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
  now() - pg_last_xact_replay_timestamp() AS replication_delay;

Common causes:

1. Replica is running a long query that blocks replay:
   - Check pg_stat_activity on replica for long-running queries
   - hot_standby_feedback = on can cause this (replica prevents primary vacuum)
   - Fix: cancel the query on replica or set max_standby_streaming_delay

2. Network bandwidth exhausted:
   - iftop / nethogs on replica machine
   - Consider WAL compression: wal_compression = on in postgresql.conf

3. Replica disk I/O saturated:
   - iostat -x 1 on replica
   - Tune recovery_min_apply_delay or add replica capacity

4. Primary generating WAL faster than replica can apply:
   - Bulk load or large migration happening
   - Wait it out — lag will self-correct once load drops

Scenario 4: Identifying and Killing Long-Running Queries

-- Queries running > 5 minutes
SELECT pid, now() - query_start AS duration, state, wait_event_type, wait_event,
  substring(query, 1, 100) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Terminate specific query gracefully
SELECT pg_cancel_backend(12345);   -- SIGINT: cancels query, connection stays
-- If that doesn't work:
SELECT pg_terminate_backend(12345); -- SIGTERM: kills connection entirely

-- Kill all queries matching a pattern (USE CAREFULLY)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query ILIKE '%DELETE FROM logs%'
  AND state = 'active'
  AND pid <> pg_backend_pid();

-- Statement timeout to prevent future runaway queries
ALTER ROLE myapp_user SET statement_timeout = '300s';
-- Or per-session:
SET statement_timeout = '300000';  -- milliseconds

Key Patterns

pg_dump / pg_restore

# Dump a single database (custom format, parallel-restorable)
pg_dump -U postgres -h localhost -Fc mydb > mydb.dump

# Dump with parallel jobs (faster for large DBs)
pg_dump -U postgres -Fd mydb -j 4 -f mydb_dir/

# Restore (custom format)
pg_restore -U postgres -h localhost -d mydb mydb.dump

# Restore with parallel jobs
pg_restore -U postgres -h localhost -d mydb -j 4 mydb_dir/

# Dump only schema (no data)
pg_dump -U postgres -s mydb > schema.sql

# Dump only specific table
pg_dump -U postgres -t mytable mydb > mytable.dump

# Dump all databases (plain SQL format, for all-in-one restore)
pg_dumpall -U postgres > all_databases.sql

# Restore all databases
psql -U postgres < all_databases.sql

# Test restore without actually restoring (list archive contents)
pg_restore --list mydb.dump | head -50

pg_hba.conf Connection Troubleshooting

Gotcha: pg_hba.conf is evaluated top-to-bottom, first match wins. A broad host all all 0.0.0.0/0 reject early in the file will block everything below it. When debugging auth failures, always check whether a more restrictive rule above is matching before your intended rule.

# /etc/postgresql/<ver>/main/pg_hba.conf
# TYPE  DATABASE  USER      ADDRESS          METHOD
local   all       postgres                   peer         # unix socket as postgres OS user
host    all       all       127.0.0.1/32     scram-sha-256
host    mydb      myapp     10.0.0.0/8       scram-sha-256
hostssl all       all       0.0.0.0/0        cert         # require TLS + cert

# After editing pg_hba.conf, reload (no restart needed)
sudo systemctl reload postgresql
# or:
SELECT pg_reload_conf();

# Common errors:
# "Ident authentication failed" — user connecting via TCP but method=ident; change to md5/scram
# "peer authentication failed" — connecting via socket with wrong OS user
# "password authentication failed" — wrong password or method mismatch
# "no pg_hba.conf entry" — client IP/user/db combination not in any rule

postgresql.conf Key Tuning Parameters

Default trap: work_mem is per-sort-operation, not per-connection. A single complex query with 5 sorts and 10 hash joins can use 15x work_mem. With 200 connections, work_mem=64MB can theoretically consume 192 GB. Start low (16-32 MB), monitor with log_temp_files=0, and increase selectively for specific queries with SET work_mem.

# Memory
shared_buffers = 25% of total RAM      # e.g., 8GB for 32GB server
effective_cache_size = 75% of total RAM # hint to planner, not an allocation
work_mem = 64MB                         # per-sort/hash, can multiply by connections!
maintenance_work_mem = 512MB            # for VACUUM, index builds

# WAL / Checkpoints
wal_buffers = 64MB
checkpoint_completion_target = 0.9     # spread checkpoint writes over 90% of interval
checkpoint_timeout = 15min             # default 5min, increase for write-heavy loads
max_wal_size = 4GB                     # WAL grows to this before forcing a checkpoint

# Query planner
random_page_cost = 1.1                 # default 4; lower for SSD storage
effective_io_concurrency = 200         # SSD: 200, HDD: 2

# Logging
log_slow_queries (use log_min_duration_statement):
log_min_duration_statement = 1000      # log queries taking > 1s
log_lock_waits = on
log_autovacuum_min_duration = 250ms    # log autovacuum taking > 250ms
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# Autovacuum
autovacuum_vacuum_scale_factor = 0.05  # vacuum at 5% dead tuples (default 20%)
autovacuum_analyze_scale_factor = 0.02
autovacuum_max_workers = 5             # default 3; increase for many tables
autovacuum_vacuum_cost_delay = 2ms     # default 2ms; reduce for faster vacuum

pg_upgrade (Major Version Upgrade)

# Example: upgrade from 14 to 16
# 1. Install new PostgreSQL version alongside old
apt install postgresql-16

# 2. Stop both clusters (or just new one if freshly installed)
sudo systemctl stop postgresql@14-main
sudo systemctl stop postgresql@16-main

# 3. Run pg_upgrade as postgres user
sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/16/bin \
  -d /var/lib/postgresql/14/main \
  -D /var/lib/postgresql/16/main \
  -o '-c config_file=/etc/postgresql/14/main/postgresql.conf' \
  -O '-c config_file=/etc/postgresql/16/main/postgresql.conf'

# 4. Check upgrade (--check flag, no actual changes)
# Add --check to the above command first

# 5. Start new cluster
sudo systemctl start postgresql@16-main

# 6. Run analyze on new cluster (statistics not copied)
sudo -u postgres vacuumdb --all --analyze-in-stages

# 7. Remove old cluster after verifying
./delete_old_cluster.sh   # generated by pg_upgrade

Extension Management

-- List installed extensions
SELECT name, default_version, installed_version FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

-- Install extension (must be in shared_preload_libraries if it needs it)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

-- pg_stat_statements — find slow queries
-- Requires: shared_preload_libraries = 'pg_stat_statements' in postgresql.conf + restart
SELECT query, calls, total_exec_time/calls AS avg_ms, rows/calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Reset statistics
SELECT pg_stat_statements_reset();

Pages that link here

• Anti-Primer: Postgresql
• Postgresql