Daily PostgreSQL Health Checks and Monitoring

1. Connection and database-size check

Run these from a monitoring role with catalog access. Compare the results to your own alert thresholds; the configured connection limit includes reserved slots and should not be treated as a target.

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

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

2. Find long transactions and blocked work

Review age and query text with the service owner. Long idle transactions can prevent vacuum from reclaiming dead tuples, but the age alone does not prove that a backend is safe to stop.

SELECT pid, usename, application_name, client_addr, state,
       now() - xact_start AS transaction_age,
       wait_event_type, wait_event, left(query, 200) AS query_sample
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start;

SELECT pid AS blocked_pid,
       pg_blocking_pids(pid) AS blocking_pids,
       now() - query_start AS waiting_for,
       left(query, 160) AS waiting_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Check replication and alerts

On a primary, review connected replicas and their write, flush, and replay positions. On a standby, check the receiver and replay timestamp. Interpret lag alongside workload and your recovery-point objective; a quiet system may not generate fresh WAL.

-- Primary server
SELECT application_name, state, sync_state,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- Standby server
SELECT status, receive_start_lsn, latest_end_lsn,
       latest_end_time
FROM pg_stat_wal_receiver;

Record the run time, host/environment, metric, expected range, observed value, and ticket or alert ID. Escalate based on the service's agreed thresholds rather than a universal number.

Daily operating sequence

1. Check monitoring alerts, disk headroom, service availability, and backup job outcome.
2. Run connection, transaction, blocking, database-size, and replication checks.
3. Compare results with the previous day and identify sudden changes.
4. Create or update an incident/ticket for anomalies; include evidence and a named owner.
5. Verify any approved intervention and leave a hand-off note for the next shift.