Automate the PostgreSQL DBA Runbook

1. Set the operating calendar

2. Create a repeatable daily SQL check

Save read-only checks in a reviewed SQL file and run them with the approved monitoring role. PostgreSQL's psql can stop on SQL errors; use the configured service file or secret manager instead of putting passwords in the command line.

-- dba_daily_check.sql
SELECT now() AS checked_at,
       current_database() AS database_name,
       current_setting('server_version') AS server_version,
       pg_is_in_recovery() AS is_standby;

SELECT count(*) AS connections,
       current_setting('max_connections')::int AS configured_limit
FROM pg_stat_activity;

SELECT count(*) AS blocked_sessions
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;

psql -X -v ON_ERROR_STOP=1 --dbname=service=prod-readonly --file=dba_daily_check.sql

Use the connection string/service name format supported by your environment. Send output to an access-controlled monitoring destination, attach a run ID, and alert on approved thresholds; a script that silently exits or produces no result is not a health check.

3. Capstone: resolve a rising connection alert

1. Open an incident with alert ID, affected service, start time, and user impact.
2. Check connection count and group sessions by user/application/state; check waits and transaction age.
3. Correlate findings with application deploys, pooler metrics, logs, and host capacity.
4. Agree a mitigation with the service owner. Avoid terminating sessions until ownership and transaction impact are known.
5. Apply one approved change, then compare connections, latency, errors, and blocked work to baseline.
6. Record root cause, evidence, rollback status, and a preventive action with an owner/date.

SELECT usename, application_name, state, count(*) AS sessions,
       max(now() - xact_start) AS oldest_transaction
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY usename, application_name, state
ORDER BY sessions DESC;

4. Automation safety checklist

• Use a dedicated least-privilege role, timeouts, and explicit error handling.
• Keep credentials out of source, SQL files, logs, and shell history.
• Make read-only checks the default; require approvals for changes and deletions.
• Log start/end time, target, command version, exit status, and alert/ticket reference.
• Test on a non-production instance, monitor the scheduler, and alert if jobs stop reporting.
• Review thresholds and access periodically; keep manual run and rollback steps documented.