Skill

PostgreSQL

postgresql · current version v1

Download v1

Troubleshoot and support a PostgreSQL server — connect, diagnose, and resolve problems across 13/14/15/16/17, on Docker, bare metal, systemd, a managed service, or Kubernetes. Covers psql, pg_isready, config and hba files, connections, locks, slow queries, autovacuum/bloat, replication, WAL/disk, and a pgvector section. Use when someone reports Postgres is down, slow, refusing connections, out of disk, deadlocking, bloated, lagging a replica, or when a pgvector query is slow or wrong.

16 downloads · published 2026-09-02

What this grants

Skill Card

Security Audits

Version history

VersionPublishedStatus
v1 2026-09-02 published

Files

SKILL.md

raw | preview 

---
name: postgresql
description: Troubleshoot and support a PostgreSQL server — connect, diagnose, and resolve problems across 13/14/15/16/17, on Docker, bare metal, systemd, a managed service, or Kubernetes. Covers psql, pg_isready, config and hba files, connections, locks, slow queries, autovacuum/bloat, replication, WAL/disk, and a pgvector section. Use when someone reports Postgres is down, slow, refusing connections, out of disk, deadlocking, bloated, lagging a replica, or when a pgvector query is slow or wrong.
---

# 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

```bash
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):
```bash
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):
```bash
docker exec -it <c> psql -U postgres
docker logs --tail 200 -f <c>
```
**systemd (bare metal or VM)**:
```bash
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):
```bash
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

```bash
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

```sql
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**
```sql
-- 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.

```sql
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.