postgres-health-check

Files (2)

• SKILL.md
• reference-migration-ops.md

SKILL.md (first lines)

# PostgreSQL Instance Health Check

**Target version:** `postgres:16-alpine` — this repository's `gc-platform-postgres` container (db `gcplatform`, owner role `gcplatform`, runtime role `gc_kourou_app_login`).

**Contract:** every check below is a read-only diagnostic (SELECT against `pg_stat_*`/`pg_catalog`, `pg_isready`, `docker` read commands). Each check gives: **query → interpretation → severity → remediation**. Remediations that mutate state are emitted ONLY as `⚠️ HUMAN CONFIRMATION REQUIRED` blocks — if `postgres-dba` is installed, that agent never executes them; otherwise `database-reviewer` or the main agent does not either.

**Connection:**

```bash
docker exec gc-platform-postgres psql -U gcplatform -d gcplatform -c "<query>"
```

**Progressive disclosure:** for the OPS side of applying EF Core migrations (lock windows at apply time, `CREATE INDEX CONCURRENTLY` vs the migration transaction, post-migration bloat/vacuum watch, live model-drift check), load `reference-migration-ops.md` in this skill's directory; code-side migration/schema correctness stays with `database-reviewer`.

---

## 1. Connectivity & uptime

```sql
SELECT version();
SELECT now() - pg_postmaster_start_time() AS uptime;
SELECT pg_is_in_recovery();
```

```bash
docker exec gc-platform-postgres pg_isready -U gcplatform -d gcplatform
```

**Interpretation:** `pg_isready` non-zero exit = server not accepting connections (CRITICAL). Very short uptime + no deploy = crash-loop (check `docker logs --tail 200 gc-platform-postgres`). `pg_is_in_recovery() = true` on the primary = unexpected (this stack has no replicas).

**Severity:** CRITICAL if unreachable; LOW note if uptime < 1h (statistics views were reset — treat other checks' counters cautiously).

---

## 2. Cache hit ratio

```sql
SELECT
  datname,
  blks_hit,
  blks_read,
  ROUND(blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) * 100, 2) AS cache_hit_ratio_pct
FROM pg_stat_database
WHERE datname = current_database();
```

**Interpretation:** ratio = shared-buffer hits / (hits + disk reads). Healthy OLTP ≥ 99%. < 95% on a warm instance means working set exceeds `shared_buffers` (default 128MB here — no `command:` override in compose) or queries do large sequential scans. Stats accumulate since last reset — judge the trend, not a snapshot.

**Severity:** MEDIUM (< 95%), HIGH (< 90% with user-visible latency).

**Remediation:** size `shared_buffers` per the `postgres-performance-tuning` skill; first confirm with EXPLAIN that low ratio is not just batch analytics scans.

---

## 3. Dead tuples & autovacuum hotspots

```sql
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, last_analyze, last_autoanalyze,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
```

**Interpretation:** `n_dead_tup` grows with UPDATE/DELETE; autovacuum reclaims them. Hotspots: high `dead_pct` (> 20%) AND (`last_autovacuum` NULL or old) = autovacuum not keeping up (long transactions hold back the xmin horizon; check check 5). `n_dead_tup` is an *estimate* from the stats collector, not an exact count. Bloat also lives in indexes — compare `pg_total_relation_size` vs `pg_relation_size`.

**Severity:** HIGH (dead_pct > 50% on a hot table, or never autovacuumed); MEDIUM (20–50%).

**Remediation:**

⚠️ HUMAN CONFIRMATION REQUIRED

```sql
-- Reclaim space without the exclusive lock of VACUUM FULL (safe online):
VACUUM (ANALYZE, VERBOSE) <schema>.<table>;

-- For tables that bloat repeatedly, tighten per-table autovacuum:
ALTER TABLE <schema>.<table> SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);
```

`VACUUM FULL` rewrites the whole table under an `ACCESS EXCLUSIVE` lock — only as last resort, off-hours, with a fresh backup. Source: https://www.postgresql.org/docs/16/routine-vacuuming.html

---

## 4. XID wraparound distance

```sql
-- Per-database age:
SELECT datname, age(datfrozenxid) AS xid_age,
       ROUND(age(datfrozenxid)::numeric / 2147483647 * 100, 2) AS pct_to_wraparound
FROM pg_database WHERE datname = current_database();

-- Oldest per-table age (finds the table holding the horizon back):
SELECT schemaname, relname, age(relfrozenxid) AS rel_xid_age
FROM pg_stat_user_tables
ORDER BY age(relfrozenxid) DESC LIMIT 10;
```

**Interpretation:** transaction IDs are 32-bit; at age ≈ 2^31 (≈ 2.147 billion) the database **stops accepting writes** to prevent wraparound data loss. PostgreSQL warns when a database comes within **40 million** XIDs of wraparound and refuses new transactions when only **3 million** remain. Autovacuum normally prevents this (`autovacuum_freeze_max_age`, default 200M, triggers aggressive freeze vacuums) — except when autovacuum is blocked (long-lived transactions, stuck replication slots, `idle in transaction` sessions). Source: https://www.postgresql.org/docs/16/routine-vacuuming.html

**Severity:** CRITICAL (age > 2.1B, i.e. within 40M of wraparound); HIGH (> 1B); MEDIUM (> 500M — investigate why freeze vacuums lag).

**Remediation:**

⚠️ HUMAN CONFIRMATION REQUIRED

```sql
-- First remove whatever holds the xmin horizon back (check 5): kill the stuck session/slot.
-- Then database-wide freeze vacuum (I/O-heavy; run off-hours):
VACUUM (FREEZE, VERBOSE) <schema>.<table>;   -- per worst table
-- or, full database (long):
-- VACUUMFREEZE via: VACUUM (FREEZE); on each database

From fmflurry/settings-opencode, MIT.

More from fmflurry/settings-opencode