PostgreSQL — Troubleshooting & Support

A support runbook, not a tutorial. Establish the host first with the detect-platform skill, then work top to bottom: read before you write, and treat every mutating step as deliberate. Commands and output below were run against PostgreSQL 17.11.

Current majors are 13–17 (17 is newest; a major is EOL ~5 years after release — 12 is out, confirm at https://www.postgresql.org/support/versioning/). Postgres numbers one major per year; the ".x" is a patch level, not a feature change.

0. Identify what you are on

psql --version                                   # psql (PostgreSQL) 17.11
psql -U postgres -tAc 'select version();'        # server version — may differ from the client
psql -U postgres -tAc 'show server_version_num;' # e.g. 170011, easy to compare

Where its files and state are (ground truth, not a guess):

psql -U postgres -tAc 'SHOW config_file;'    # /var/lib/postgresql/data/postgresql.conf (in the image)
psql -U postgres -tAc 'SHOW hba_file;'       # pg_hba.conf — who may connect, from where, how
psql -U postgres -tAc 'SHOW data_directory;' # the PGDATA
pg_isready -U postgres                        # "accepting connections" | "no response" | "rejecting"

1. Connect, per deployment

Docker — logs go to stderr → docker logs (logging_collector is off, confirmed):

docker exec -it <c> psql -U postgres
docker logs --tail 200 -f <c>

systemd (bare metal or VM):

systemctl status postgresql        # Debian: postgresql@<ver>-main ; RHEL: postgresql-<ver>
journalctl -u postgresql -n 200 --no-pager
sudo -u postgres psql              # peer auth as the postgres OS user
# Debian PGDATA: /var/lib/postgresql/<ver>/main ; conf under /etc/postgresql/<ver>/main/

Bare metal — the host settings Postgres cares about (detect-platform found the substrate):

cat /proc/sys/kernel/shmmax 2>/dev/null        # SysV shm (mostly historical; PG uses mmap now)
cat /sys/kernel/mm/transparent_hugepage/enabled   # THP=always can cause latency; prefer disabled
free -h                                         # shared_buffers ~25% RAM is a starting point, not a law
# a cgroup memory cap below what shared_buffers+work_mem*connections assumes => OOM (detect-platform)

Managed — no host shell; psql connects, config is the provider's parameter group, and the console owns restarts/failover/upgrades. pg_stat_* views and logs (via the console) still work.

Kubernetes (operator) — kubectl exec -it <pod> -- psql -U postgres; config is the CR, not a file on the pod; the superuser password is in a Secret.

2. Tools and arguments

psql -h <host> -p 5432 -U <user> -d <db>       # -tAc for one clean scalar; -f file.sql to run a script
pg_isready -h <host> -U <user>                 # liveness without logging in
pg_dump -Fc -d <db> -f db.dump                 # -Fc custom format; restore with pg_restore
pg_restore -d <db> --clean --if-exists db.dump
pg_dumpall --globals-only                      # roles + tablespaces (not in a per-db pg_dump)
pg_basebackup -h <primary> -D <dir> -X stream  # base backup for a new replica

Useful psql meta-commands: \l databases, \dt+ tables with size, \dx extensions, \du roles, \d <table>, \di+ indexes, \timing on.

3. Diagnostics — read-only first

SELECT count(*), state FROM pg_stat_activity GROUP BY state;   -- active/idle/idle in transaction
SELECT pid, now()-query_start AS run, state, wait_event_type, left(query,80)
  FROM pg_stat_activity WHERE state<>'idle' ORDER BY run DESC;  -- the long-runners
SELECT * FROM pg_stat_activity WHERE wait_event_type='Lock';    -- who is blocked
SELECT bl.pid AS blocked, ka.pid AS blocking, left(ka.query,60) AS blocking_q
  FROM pg_locks bl JOIN pg_stat_activity a ON a.pid=bl.pid
  JOIN pg_locks kl ON kl.locktype=bl.locktype AND kl.pid<>bl.pid AND NOT kl.granted=false
  JOIN pg_stat_activity ka ON ka.pid=kl.pid WHERE NOT bl.granted;  -- blocked/blocking pairs
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY 2 DESC;
SELECT schemaname, relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables
  ORDER BY n_dead_tup DESC LIMIT 10;            -- bloat / autovacuum keeping up?

Slow queries: enable log_min_duration_statement = 200 (ms) or install pg_stat_statements and SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; Plans: EXPLAIN (ANALYZE, BUFFERS) <query>; — look for Seq Scan on a big table, and rows estimated ≫ actual (stale stats → ANALYZE).

4. Common problems → resolution

Cannot connect

• FATAL: no pg_hba.conf entry for host … — the client/db/user/SSL combo is not permitted; SHOW hba_file, add the line, and reload (SELECT pg_reload_conf();) — hba changes need only a reload, not a restart.
• FATAL: password authentication failed — wrong password or wrong auth method (md5 vs scram-sha-256; a client with an md5-only driver fails against a scram-only entry).
• could not connect … Connection refused — listen_addresses is localhost (needs a restart to change, unlike hba), or the port, or a firewall. listen_addresses='*' opens it — pair with hba.
• FATAL: sorry, too many clients already — max_connections reached; see the pooling note below.

"Too many connections" / idle in transaction

• idle in transaction sessions hold locks and an old snapshot, blocking vacuum. Find them in pg_stat_activity; the app is not committing/rolling back. idle_in_transaction_session_timeout caps them. Raising max_connections is usually the wrong fix — a connection pooler in front is the right one; each backend is a process with real memory cost.

Disk filling

• WAL can pile up if a replication slot is inactive (a replica gone away) or archive_command is failing — those pin WAL forever. SELECT slot_name, active, pg_size_pretty( pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots; — drop a dead slot.
• Bloat: dead tuples autovacuum has not reclaimed. VACUUM (VERBOSE) reclaims to reuse; space is returned to the OS only by VACUUM FULL (takes an exclusive lock — deliberate, §6) or a rebuild.

Deadlocks / blocking

• ERROR: deadlock detected — Postgres already broke the cycle by killing one transaction; the fix is in the app (consistent lock ordering), not the server. The blocked/blocking query above finds a live block; SELECT pg_cancel_backend(pid) cancels a query, pg_terminate_backend(pid) drops the whole session (§6).

Replication lag / replica not catching up

-- on the primary:
SELECT client_addr, state, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
  FROM pg_stat_replication;
-- on the replica:
SELECT now()-pg_last_xact_replay_timestamp() AS replay_delay;

Lag is usually the replica's disk/CPU, or a long query on the replica holding replay (max_standby_streaming_delay). A replica that fell off entirely and lost its slot needs a fresh pg_basebackup.

Autovacuum "not running" It is, but may be outrun by write volume or blocked by a long transaction / idle-in-transaction holding an old xmin. Check SELECT max(age(datfrozenxid)) FROM pg_database; — a large, rising value warns of wraparound, which is an emergency (Postgres will refuse writes to protect data).

5. pgvector (the vector extension)

pgvector is a Postgres extension, not a separate server — everything above still applies; this is only what is specific to it. Verified: extension version 0.8.6 on PG 17.

CREATE EXTENSION IF NOT EXISTS vector;                 -- must run in EACH database that needs it
SELECT extname, extversion FROM pg_extension WHERE extname='vector';   -- vector 0.8.6
\dx                                                    -- confirm it is installed

The problems that are actually pgvector

• ERROR: type "vector" does not exist — the extension was created in another database (or not at all). It is per-database; run CREATE EXTENSION in the one you are querying. In Docker, add it to an init script so a fresh volume has it.
• ERROR: could not open extension control file … vector.control — the extension binary is not installed on the server, only the SQL was attempted. Install the pgvector/vector package for the exact PG major, or use an image that ships it.
• Dimension mismatch: ERROR: expected N dimensions, not M — the column is vector(N) and the embedding is length M. The model's output size must equal the column's declared dimension.
• The index is not used (queries do a seq scan and are slow):
  • The query operator must match the index's opclass. Build with the distance you query: vector_l2_ops for <->, vector_cosine_ops for <=>, vector_ip_ops for <#>. An HNSW index built for L2 does nothing for a cosine query.
  • ORDER BY embedding <=> $1 LIMIT k is the shape the index accelerates; a WHERE distance filter or a different ORDER BY will not use it.
• Index choice / recall:
  • HNSW (build: USING hnsw (embedding vector_cosine_ops)) — better recall/latency, larger and slower to build; tune query recall with SET hnsw.ef_search = 100;.
  • IVFFlat (USING ivfflat (…) WITH (lists = N)) — build after data is loaded (it clusters existing rows); lists ≈ rows/1000 to start, and SET ivfflat.probes = 10; trades recall for speed. An IVFFlat built on an empty table has one list and gives terrible recall — a common "why are results bad" cause.
• Building an index on many rows is memory-hungry; raise maintenance_work_mem for the build, and expect an HNSW build to take real time on millions of rows.

6. Before you run anything that writes

Config changes, VACUUM FULL, terminating backends, dropping slots, and restarts change data, availability, or config, and on a nanoinfra deployment resolve to a mutate.remote capability — approval interactively, a standing grant unattended.

• Know which changes need a reload (pg_reload_conf() — hba, most log_*, work_mem) vs a restart (shared_buffers, max_connections, listen_addresses). SELECT name, pending_restart FROM pg_settings WHERE pending_restart; lists what is waiting on a restart.
• pg_terminate_backend drops a session mid-transaction (it rolls back); pg_cancel_backend only cancels the current query — prefer cancel.
• VACUUM FULL and REINDEX take exclusive locks — the table is unavailable for the duration.
• Dropping a replication slot that is merely paused (replica briefly down) breaks that replica's resume — confirm it is truly gone first.