Monitoring keeps a PostgreSQL installation observable before faults become outages. Track disk capacity, temporary-file use, WAL volume, table and index statistics, query slowdowns, resource spikes, maintenance pressure, and unusual activity. Set alerts well before capacity becomes critical, and review suspicious conditions manually rather than waiting for an incident. section 7.1.3 section 7.4
Logs are a near-real-time monitoring signal, not merely post-mortem evidence. They expose configuration errors, performance symptoms, integrity warnings, failed connections, and suspicious queries. Regular review can reveal a problem while there is still time to contain it. section 7.3 section 7.3.5
A retry loop can exhaust the database filesystem
A routine that repeatedly queries the misspelled relation erp.energy_use generates an error for every retry. When PostgreSQL logs share PGDATA, rapid error logging can consume the same filesystem required by the database, potentially causing an outage. Interrupt the faulty process, identify the space consumer, and keep logs on a separate filesystem with rotation and size limits. section 7.2 section 7.8
2024-09-20 12:26:19.606 BST [3055342] frogge@frogge ERROR: relation
"erp.energy_use" does not exist at character 148
2024-09-20 12:26:19.606 BST [3055342] frogge@frogge STATEMENT: WITH
yesterday AS (
SELECT *
FROM erp.energy_use
WHERE reading_time >=
date_trunc('d', now())
- interval '1d'
AND reading_time <
date_trunc('d', now())),
perbranch AS (
SELECT first_value(reading) OVER w,
last_value(reading) OVER w,
row_number() OVER w
FROM yesterday
WINDOW w AS (
PARTITION BY branch_id
ORDER BY reading_time
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING))
SELECT sum(last_value - first_value)
FROM perbranch
WHERE row_number=1
2024-09-20 12:26:19.606 BST [3055342] frogge@frogge ERROR: relation
"erp.energy_use" does not exist at character 148
2024-09-20 12:26:19.606 BST [3055342] frogge@frogge STATEMENT: WITH
[...]
Repeated identical errors within one millisecond make the runaway logging pattern visible: the error, failing statement, timestamp, and session identify what must be stopped. section 7.2
Use monitoring signals to act before symptoms escalate:
- Alert on disk and temporary-file usage early; filesystem performance may degrade before it is completely full. section 7.1.3
- Inspect logs for corruption-like read failures, then investigate filesystem or hardware causes and consider diagnostic tools such as pg_checksums. section 7.3.4
- Detect repeated authentication failures, irregular logins, and suspicious queries; summarize high-volume logs with pgBadger or scan ERROR and FATAL lines with grep. section 7.3.5
- Track long-term PostgreSQL statistics to identify trends and support capacity planning; pg_statviz can capture and visualize them with lightweight overhead. section 7.8
- Investigate slowdowns with pg_stat_activity and pg_stat_statements, tracing clues to application code, database design, configuration, or data ingestion. section 11.3.3
Monitoring is preventive operations. It provides advance warning of resource exhaustion, degraded performance, security threats, and maintenance needs such as VACUUM, ANALYZE, or inadequate autovacuum settings. Treat alerts as prompts to identify the cause, not as permission for rash cleanup such as deleting files whose purpose is unknown. section 7.4 section 7.8
