Hermes Rodríguez

Posted on Jul 21

pgwd 1.0: Cron Exit Codes for PostgreSQL Connection Alerts

#postgres #devops #go #sre

Follow-up to: Intro · Production runbook
Repository: github.com/hrodrig/pgwd
Release: v1.0.0

This is the 1.0.0 catch-up: what changed at the stability boundary, why exit codes 2/3 matter for cron/Jobs, and what broke since 0.9. For day-2 ops, the runbook post still applies (CLI details in the intro may be pre-1.0).

For a one-page product glance, see the English infographic (also Spanish, German, French, Portuguese). Landing: pgwd.hermesrodriguez.com.

TL;DR

pgwd is a single Go binary that polls pg_stat_activity, counts total / active / idle / stale against max_connections, and notifies Slack, Loki, PagerDuty, Teams, or a generic webhook when thresholds trip. 1.0.0 freezes the CLI / env / YAML contract in SPECIFICATIONS.md and documents exit codes 2 / 3 (connect / one-shot query) so the binary is realistic as a cron wrapper or Kubernetes Job — threshold alerts go over webhooks, not exit codes.

Repo: github.com/hrodrig/pgwd — Go 1.26, MIT, ~30 MB statically linked.

The classic problem (why I wrote this)

Anyone who has run Postgres in production knows the sequence:

The app starts failing intermittently with too many clients already (SQLSTATE 53300).
Someone checks pg_stat_activity and sees 800 open connections, 750 idle, 30 active, and a few open for hours.
Restart the pooler, pg_terminate_backend() the worst offenders, go back to sleep.
Nobody notices the next time until the app cries again.

The metric was never missing — it lives in pg_stat_activity and is trivial to read. The gap is that nobody watches it continuously, and when someone does, it is already late.

What pgwd does

Polls pg_stat_activity every N seconds.
Counts total, active, idle, and stale (connections open longer than stale_age, default 300s, based on backend_start — not “idle for N seconds”). Stale connections are what actually kill the pool: they opened, finished work, never closed.
Compares against thresholds and notifies Slack, Loki, PagerDuty, Teams, or a generic webhook.
3-tier alerting via -db-threshold-levels 75,85,95 (default): yellow (attention), orange (alert), red (danger). Same binary can warn early and page late without rewriting rules.
Treats connect failure as a first-class event: detects SQLSTATE 53300 via pgconn.PgError (works regardless of server lc_messages) and emits too_many_clients tagged URGENT. Not “absence of data”.
Optional long-running query alerts (db.long_query_*) with cooldown so one heavy query does not flood the channel.
Multi-DB in one process via databases:.
Optional /metrics for Prometheus / Alloy / Grafana, and CSV export for ad-hoc analysis.

Three run modes

Mode	Flags	Use case
Daemon	`-interval > 0`	Production. SQLite history, hysteresis + resolution alerts, Prometheus `/metrics`, `/healthz`.
One-shot / Cron	`-interval 0`	`crontab` or `systemd.timer`. Runs once, exits with a code, no state kept.
Dry-run	`-dry-run`	Validation. Reads stats and logs thresholds exactly as in production, but makes no HTTP calls to Slack/Loki/PagerDuty/Teams/webhook. With `-interval 0` it is the canonical way to validate a config before shipping.

Daemon gives the long-term picture (resolution alerts when a state lasts N intervals). One-shot gives cron-friendly exit codes. Dry-run gives confidence to ship a config change without paging anyone at 3am. Same binary, same config — only the flags change.

The feature I actually wanted: exit codes for cron

Threshold alerts go out over Slack / Loki / etc.; they do not change the process exit code. Exit codes are for infra failure modes so a cron wrapper / Job can page on connect death without conflating it with “the pool is busy”:

Code	Meaning
0	OK (incl. threshold events that notified successfully)
1	Config / validation error (`log.Fatal`)
2	Single-target connect failure (after `Ping`)
3	One-shot stats/query failure
4	`-strict`: notifier delivery failed for a threshold event

pgwd -client prod-payments -db-url "$DB_URL" \
     -db-threshold-levels 75,85,95 \
     -notifications-slack-webhook "$SLACK_URL"

# exit 2 → page (DB unreachable / too many clients)
# exit 3 → page (one-shot query failed)
# exit 4 → page if -strict and Slack/Loki/… delivery failed
# exit 0 → check ran (alerts may still have fired over webhooks)

Why a watchdog and not another exporter

postgres_exporter is great if you already run Prometheus. pgwd is for the case where:

You want alerts to fire on the box that runs the check, not from a Prometheus that might also be down.
You want cron-friendly exit codes without standing up a metrics stack.
You want connect failure to be a first-class event, not “absent data”.
You want Slack / Teams / PagerDuty / generic webhook without wiring Alertmanager, Grafana, and a contact point.

A fuller comparison is in docs/compare.md.

Kubernetes

-kube-postgres namespace/svc/name or pod/name uses client-go to port-forward and connect on localhost. No kubectl binary required.
kube.password_from_secret reads the password from a Secret in the Postgres namespace. Legacy DISCOVER_MY_PASSWORD / pods/exec was removed in 0.9.0 — it needed exec into the DB pod, surface area you do not want in a watchdog.
Helm chart and raw manifests live in pgwd-selfhosted. This repo ships the binary, packages, and image.

Storage: SQLite by default, Postgres / MySQL available

The metrics store (history, hysteresis, resolution alerts, CSV export) defaults to SQLite — zero-setup. Prefer reusing existing Postgres or MySQL? Switch the backend selector to postgres or mysql (Postgres-compatible drivers including TimescaleDB work). Rows are keyed by (client, cluster, database) — URL host is not part of the key, so give each databases: entry a unique client when the same DB name exists on different hosts.

Supply chain

Cosign v3 on checksums.txt and ghcr.io/hrodrig/pgwd. 1.0 consolidates to one checksums.txt.sigstore.json bundle.
SPDX + CycloneDX SBOMs on every release.
distroless/static-debian13:nonroot image: no shell, no apk, no BusyBox. Static binary + CA certs.
make release-check runs lint + test + cover-check (≥80% on library packages) + test-integration + test-e2e-kube + docker-scan before any tag.

Telemetry (anonymous; collector off, update-check on)

Two switches on daemon start (interval > 0 only):

enable_collector / PGWD_ENABLE_COLLECTOR — off by default (opt-in). Once per daemon start, POSTs a tiny payload to https://collect.gghstats.com/a1b2c3d4e5f6a7b8.
enable_update_check / PGWD_ENABLE_UPDATE_CHECK — on by default (opt-out). GETs GitHub releases for a newer tag.

Neither endpoint receives DSNs, hostnames, database names, client, cluster/namespace, webhook URLs, file paths, Loki labels, or secrets. See README [Anonymous usage] for the payload shape.

Platforms

Linux (glibc + Alpine/musl), Windows, macOS, FreeBSD (rc.d), Solaris 11.4+ (SMF). Docker image ghcr.io/hrodrig/pgwd:v1.0.0 is distroless. Helm / Compose live in pgwd-selfhosted.

What’s new in 1.0

Stable contract. CLI flags, PGWD_* env vars, and documented YAML keys are frozen.
Exit codes 2 and 3 (see above). 0/1/4 semantics unchanged.
Brand mark: docs/logo.svg — cyan pg monogram with green status dot.
Cosign v3 single bundle.

What’s removed in 1.0

If you are coming from 0.9.x:

Top-level db: → use databases: (one entry for a single target). Loading db: fails with a migration hint.
-notify-on-connect-failure (and env/YAML equivalents). Connect failure notifications always send when notifiers are configured.
-db-threshold-total / -db-threshold-active. Use -db-threshold-levels.

Full notes: docs/UPGRADE-0.9-to-1.0.md.

Install

# recommended (Unix / macOS / WSL)
curl -fsSL https://get.pgwd.hermesrodriguez.com/install.sh | sh

# or go install
go install github.com/hrodrig/pgwd/cmd/pgwd@latest

# Cosign-verified image (tag v1.0.0)
cosign verify ghcr.io/hrodrig/pgwd:v1.0.0 \
  --certificate-oidc-issuer https://token.actions.githubusercontent.com \
  --certificate-identity-regexp '^https://github\.com/hrodrig/pgwd/\.github/workflows/release\.yml@refs/tags/v'

More options (Homebrew, .deb/.rpm, Docker): pgwd.hermesrodriguez.com/install.

Minimal config:

client: prod-payments
databases:
  - url: postgres://app@db.prod:5432/payments
    stale_age: 300s
notifications:
  slack:
    webhook: https://hooks.slack.com/services/...

Ever hit the classic “pool was fine, server was fine, app was fine — and suddenly nothing could connect”? How do you catch it today? I’m especially curious whether 3-tier alerting feels natural, or whether you still prefer flat thresholds.

Clone / traffic for this repo lives on gghstats — hrodrig/pgwd — raw GitHub traffic, no vanity smoothing.

Thank you for reading — and if you already run pgwd somewhere, thank you for trusting it with real Postgres. A star on github.com/hrodrig/pgwd, an issue, or a comment with your runbook helps more than you might think. See you in the next post.

Disclosure

Written by Hermes Rodríguez. AI tools helped with drafting, diagrams, and editing; technical claims were checked against SPECIFICATIONS.md, CHANGELOG, and the v1.0.0 tag. Always verify behavior on the release you install.

DEV Community