Monitoring PostgreSQL

-- Add to postgresql.conf or ALTER SYSTEM
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

-- Restart PostgreSQL, then create the extension
CREATE EXTENSION pg_stat_statements;

-- Configure tracking
ALTER SYSTEM SET pg_stat_statements.track = top;  -- all, top, none
ALTER SYSTEM SET pg_stat_statements.max_statements = 10000;

-- Current connections by state
SELECT
    state,
    COUNT(*) AS count
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

-- Find long-running queries
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC
LIMIT 10;

-- Find queries waiting on 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 wait_duration
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid;

-- Top 10 queries by total time
SELECT
    LEFT(query, 80) AS query,
    calls,
    ROUND(total_exec_time::numeric, 1) AS total_ms,
    ROUND(mean_exec_time::numeric, 1) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 1) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Top queries by calls (hot queries)
SELECT
    LEFT(query, 80) AS query,
    calls,
    ROUND(total_exec_time::numeric, 1) AS total_ms,
    ROUND(mean_exec_time::numeric, 1) AS avg_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

-- Find queries with high variance (unstable performance)
SELECT
    LEFT(query, 80) AS query,
    calls,
    ROUND(mean_exec_time::numeric, 1) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 1) AS stddev_ms,
    ROUND(stddev_exec_time / GREATEST(mean_exec_time, 0.001)::numeric, 2) AS cv
FROM pg_stat_statements
WHERE calls > 100
ORDER BY cv DESC
LIMIT 10;

-- Current lock summary
SELECT
    locktype,
    mode,
    granted,
    COUNT(*) AS count
FROM pg_locks
GROUP BY locktype, mode, granted
ORDER BY count DESC;

-- Find relation-level lock contention
SELECT
    relation::regclass AS table_name,
    mode,
    COUNT(*) AS count,
    SUM(CASE WHEN NOT granted THEN 1 ELSE 0 END) AS waiting
FROM pg_locks
WHERE relation IS NOT NULL
GROUP BY relation, mode
HAVING COUNT(*) > 1
ORDER BY waiting DESC;

-- Detect deadlocks (check PostgreSQL logs)
-- Or monitor for near-deadlocks by checking lock wait chains
SELECT
    blocked.pid,
    blocked.query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON blocked.pid = bl.pid AND NOT bl.granted
JOIN pg_locks gl ON bl.locktype = gl.locktype
    AND bl.relation = gl.relation AND gl.granted
JOIN pg_stat_activity blocking ON gl.pid = blocking.pid;

-- Primary: replication status
SELECT
    client_addr,
    state,
    sent_lsn,
    replay_lsn,
    replay_lag,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- Primary: replication slot status
SELECT
    slot_name,
    active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_pretty
FROM pg_replication_slots;

-- Archive status
SELECT
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time,
    last_failed_wal,
    last_failed_time
FROM pg_stat_archiver;

# Install postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz

# Create monitoring user
psql -c "CREATE USER pg_monitor WITH PASSWORD 'secure_password';"
psql -c "GRANT pg_monitor TO pg_monitor_role;"
psql -c "GRANT CONNECT ON DATABASE mydb TO pg_monitor;"

# Start exporter
DATA_SOURCE_NAME="postgresql://pg_monitor:secure_password@localhost:5432/mydb?sslmode=disable" \
./postgres_exporter --web.listen-address=:9187