postgres-health-check

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 — the postgres-dba agent never executes them.

Connection:

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

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

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

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.