postgres-troubleshooting-cli

name

postgres-troubleshooting-cli

description

Diagnose and resolve Azure Database for PostgreSQL Flexible Server issues via CLI. Use when the user wants to: (1) troubleshoot connection failures or timeouts, (2) identify and resolve lock contention or deadlocks, (3) find and terminate long-running or blocking queries, (4) diagnose high CPU, memory, or IO usage, (5) detect and fix table or index bloat, (6) debug replication lag on read replicas, (7) analyze PostgreSQL error logs, (8) investigate autovacuum issues, (9) resolve Azure-specific server health problems. Triggers: "postgres not connecting", "connection timeout", "deadlock postgres", "blocked queries", "long running query", "kill query postgres", "high CPU postgres", "table bloat", "index bloat", "replication lag", "autovacuum not running", "postgres error", "troubleshoot postgres", "debug postgres", "postgres slow", "out of connections", "too many connections", "lock wait timeout".

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

PostgreSQL Troubleshooting — CLI Skill

Table of Contents

Task	Reference	Notes
Azure PostgreSQL Topology	COMMON-CORE.md § Azure PostgreSQL Topology
Authentication	COMMON-CLI.md § Authentication Recipes	Entra ID or native auth
SQL via psql	COMMON-CLI.md § SQL via psql	Connect, query, execute
Server Management	COMMON-CLI.md § Server Management	az postgres flexible-server
Gotchas	COMMON-CORE.md § Gotchas	Common issues and fixes

---

Tool Stack

Tool	Role	Install
psql	Primary: Execute diagnostic SQL queries.	apt-get install postgresql-client / brew install libpq / winget install PostgreSQL.psql
az CLI	Server health, metrics, logs, parameter inspection.	Pre-installed in most dev environments
jq	Parse JSON from az commands	Pre-installed or trivial

Agent check — verify before first operation:

psql --version 2>/dev/null || echo "INSTALL: apt-get install postgresql-client OR brew install libpq"

---

Agentic Troubleshooting Workflow

Start here — always gather server state before diagnosing.

Step 1: Verify Connectivity

# Test basic TCP connectivity
psql "host=$FQDN port=5432 dbname=$DB user=$USER sslmode=require" \
  -c "SELECT version();"

Step 2: Check Server Health (Azure)

# Server status
az postgres flexible-server show \
  --resource-group $RG --name $SERVER \
  --query "{state:state, version:version, sku:sku.name, ha:highAvailability.mode}" -o table

# Recent metrics (CPU, memory, IO)
az monitor metrics list \
  --resource "/subscriptions/$SUB/resourceGroups/$RG/providers/Microsoft.DBforPostgreSQL/flexibleServers/$SERVER" \
  --metric "cpu_percent" "memory_percent" "iops" \
  --interval PT5M --start-time $(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ) \
  -o table

Step 3: Run Targeted Diagnostics

Based on symptoms, jump to the relevant section below.

---

Connection Issues

Too Many Connections

-- Current connection count vs limit
SELECT
    current_setting('max_connections')::int AS max_connections,
    (SELECT count(*) FROM pg_stat_activity) AS current_connections,
    current_setting('max_connections')::int - (SELECT count(*) FROM pg_stat_activity) AS available;

-- Connections by state
SELECT state, count(*) AS count
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

-- Connections by application and user
SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
GROUP BY usename, application_name, client_addr, state
ORDER BY count(*) DESC;

Idle Connections Consuming Slots

-- Find long-idle connections (idle > 5 minutes)
SELECT pid, usename, application_name, client_addr, state,
       now() - state_change AS idle_duration,
       query
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '5 minutes'
ORDER BY idle_duration DESC;

Connection Timeout Diagnosis

# Check firewall rules
az postgres flexible-server firewall-rule list \
  --resource-group $RG --name $SERVER -o table

# Check if PgBouncer is enabled (connection pooling)
az postgres flexible-server parameter show \
  --resource-group $RG --server-name $SERVER \
  --name pgbouncer.enabled --query value -o tsv

# Check SSL enforcement
az postgres flexible-server parameter show \
  --resource-group $RG --server-name $SERVER \
  --name require_secure_transport --query value -o tsv

---

Lock Contention and Deadlocks

Find Blocked Queries

-- pg_locks is restricted on Azure; use pg_blocking_pids() instead
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    now() - blocked.query_start AS blocked_duration,
    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.query AS blocker_query,
    blocker.state AS blocker_state
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
  ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocked_duration DESC;

View All Locks on a Table

-- Sessions waiting on locks (Azure-compatible via wait_event_type)
SELECT
    a.pid,
    a.usename,
    a.wait_event_type,
    a.wait_event,
    a.state,
    now() - a.query_start AS duration,
    left(a.query, 200) AS query_preview
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
  AND a.pid != pg_backend_pid()
ORDER BY duration DESC;

Detect Lock Chains (Blocking Trees)

-- Blocking chain using pg_blocking_pids() (Azure-compatible)
WITH RECURSIVE lock_chain AS (
    SELECT
        pid AS blocked_pid,
        pg_blocking_pids(pid) AS blocker_pids,
        query AS blocked_query,
        1 AS depth
    FROM pg_stat_activity
    WHERE wait_event_type = 'Lock'
      AND cardinality(pg_blocking_pids(pid)) > 0

    UNION ALL

    SELECT
        lc.blocked_pid,
        pg_blocking_pids(blocker.pid),
        blocker.query,
        lc.depth + 1
    FROM lock_chain lc
    JOIN pg_stat_activity blocker ON blocker.pid = ANY(lc.blocker_pids)
    WHERE lc.depth < 10
      AND blocker.wait_event_type = 'Lock'
)
SELECT blocked_pid, blocker_pids, blocked_query, depth
FROM lock_chain
ORDER BY depth, blocked_pid;

Check for Deadlocks

-- Recent deadlocks from pg_stat_database
SELECT datname, deadlocks, conflicts
FROM pg_stat_database
WHERE datname = current_database();

# Enable deadlock logging (if not already)
az postgres flexible-server parameter show \
  --resource-group $RG --server-name $SERVER \
  --name log_lock_waits --query value -o tsv

# Set lock wait timeout logging
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name log_lock_waits --value "on"

---

Long-Running and Blocking Queries

Find Long-Running Queries

-- Active queries running longer than 1 minute
SELECT
    pid,
    usename,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    left(query, 200) AS query_preview
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '1 minute'
  AND pid != pg_backend_pid()
ORDER BY duration DESC;

Find Queries Holding Locks

-- Active sessions holding or waiting on locks (Azure-compatible)
SELECT
    a.pid,
    a.usename,
    a.query,
    a.state,
    now() - a.query_start AS duration,
    a.wait_event_type,
    a.wait_event
FROM pg_stat_activity a
WHERE a.wait_event_type = 'Lock'
  AND a.pid != pg_backend_pid()
ORDER BY duration DESC;

Terminate a Query

-- Cancel a running query (graceful)
SELECT pg_cancel_backend(pid);

-- Terminate a backend connection (forceful — use as last resort)
SELECT pg_terminate_backend(pid);

Warning: pg_terminate_backend kills the entire connection, not just the query. The client application will receive a connection error.

---

High Resource Usage

CPU Diagnosis

-- Top CPU-consuming queries (requires pg_stat_statements)
SELECT
    left(query, 100) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Currently active queries (CPU consumers are typically in 'active' state)
SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS duration, left(query, 200)
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid()
ORDER BY duration DESC;

Memory Diagnosis

-- Current memory-related settings
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
    'shared_buffers', 'work_mem', 'maintenance_work_mem',
    'effective_cache_size', 'temp_buffers', 'hash_mem_multiplier'
);

-- Temp file usage (indicates work_mem too low)
SELECT datname,
       temp_files AS temp_files_created,
       pg_size_pretty(temp_bytes) AS temp_bytes_used
FROM pg_stat_database
WHERE datname = current_database();

IO Diagnosis

-- Buffer cache hit ratio (should be > 99%)
SELECT
    round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_ratio
FROM pg_stat_database;

-- Per-table cache hit ratio
SELECT
    schemaname, relname,
    heap_blks_read, heap_blks_hit,
    round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 20;

-- Checkpoint frequency (too frequent = IO pressure)
SELECT checkpoints_timed, checkpoints_req,
       buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;

---

Table and Index Bloat

Detect Table Bloat

-- Estimated table bloat (using pgstattuple for precise measurement)
-- Quick estimate via pg_stat_user_tables:
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,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Precise bloat measurement with pgstattuple extension
-- CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('your_table');

Detect Index Bloat

-- Index bloat estimation
SELECT
    schemaname, tablename, indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 1024 * 1024  -- > 1 MB
ORDER BY pg_relation_size(indexrelid) DESC;

Remediate Bloat

-- Regular VACUUM (reclaims space for reuse, non-blocking)
VACUUM VERBOSE your_table;

-- VACUUM with index cleanup
VACUUM (VERBOSE, INDEX_CLEANUP ON) your_table;

-- REINDEX concurrently (non-blocking index rebuild)
REINDEX INDEX CONCURRENTLY idx_your_index;

-- REINDEX entire table concurrently
REINDEX TABLE CONCURRENTLY your_table;

Warning: Avoid VACUUM FULL on production during peak hours — it takes an exclusive lock on the table.

---

Autovacuum Issues

Check Autovacuum Status

-- Tables approaching autovacuum threshold
SELECT
    schemaname, relname,
    n_dead_tup,
    n_live_tup,
    last_autovacuum,
    last_autoanalyze,
    current_setting('autovacuum_vacuum_threshold')::int +
        (current_setting('autovacuum_vacuum_scale_factor')::float * n_live_tup)::int AS vacuum_threshold
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Currently running autovacuum workers
SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY duration DESC;

Autovacuum Configuration

-- Current autovacuum settings
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

Per-Table Autovacuum Tuning

-- Make autovacuum more aggressive for a high-churn table
ALTER TABLE your_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_threshold = 50,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_analyze_threshold = 50
);

---

Replication Lag

Check Replica Lag (Primary)

-- Replication status from primary
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_pretty
FROM pg_stat_replication;

Check Replica Lag (Replica)

-- On the replica: check how far behind
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_delay,
    pg_is_in_recovery() AS is_replica,
    pg_last_wal_receive_lsn() AS received_lsn,
    pg_last_wal_replay_lsn() AS replayed_lsn;

Azure Replica Status

# List replicas and their status
az postgres flexible-server replica list \
  --resource-group $RG --name $SERVER -o table

---

Azure-Specific Diagnostics

Server Logs

# List available log files
az postgres flexible-server server-logs list \
  --resource-group $RG --server-name $SERVER \
  --query "[].{name:name, sizeInKb:sizeInKb, createdTime:createdTime}" -o table

# Download a log file
az postgres flexible-server server-logs download \
  --resource-group $RG --server-name $SERVER \
  --name your-log-file.log

Enable Diagnostic Logging

# Enable slow query logging
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name log_min_duration_statement --value "1000"

# Enable query logging
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name log_statement --value "ddl"

# Enable lock wait logging
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name log_lock_waits --value "on"

# Enable auto_explain for slow queries
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name auto_explain.log_min_duration --value "1000"

Check Server Parameters

# Show all non-default parameters
az postgres flexible-server parameter list \
  --resource-group $RG --server-name $SERVER \
  --query "[?source!='system-default'].{name:name, value:value, source:source}" -o table

---

Quick Diagnostic Cheat Sheet

Symptom	First Query	Section
Can't connect	Connection count vs max	Connection Issues
Query hangs	Blocked queries	Lock Contention
High CPU	pg_stat_statements top queries	CPU Diagnosis
High memory / temp files	Memory settings + temp usage	Memory Diagnosis
Slow queries	EXPLAIN ANALYZE + cache hit ratio	IO Diagnosis
Table growing unexpectedly	Dead tuple count + bloat	Table Bloat
Replica falling behind	pg_stat_replication	Replication Lag
Autovacuum not keeping up	Autovacuum status + thresholds	Autovacuum Issues

---

Must

• Gather current server state before making changes
• Check pg_stat_activity before terminating backends
• Use pg_cancel_backend before pg_terminate_backend
• Enable log_lock_waits and log_min_duration_statement for ongoing diagnostics
• Use sslmode=require in all connections
• Verify the query/PID is correct before terminating

Prefer

• VACUUM over VACUUM FULL (non-blocking vs exclusive lock)
• REINDEX CONCURRENTLY over REINDEX on production
• pgstattuple for precise bloat measurement beyond pg_stat_user_tables estimates
• pg_squeeze or pg_repack over VACUUM FULL for online compaction
• pgaudit for compliance-grade audit logging of SQL activity
• Azure Monitor metrics for trend analysis
• pg_stat_statements for historical query analysis
• Per-table autovacuum tuning for high-churn tables

Avoid

• VACUUM FULL during peak hours
• Terminating autovacuum workers unless absolutely necessary
• Changing max_connections without evaluating PgBouncer
• Ignoring bloat on frequently-updated tables
• Running diagnostic queries without LIMIT on busy servers