Log rotation controls PostgreSQL log growth by closing or replacing log files and limiting how much history remains on disk. Without it, a runaway query or configuration error can fill the log filesystem; if logs share storage with the database, the database can crash. section 7.2
Treat rotation as part of storage safety, not merely housekeeping. Keep logs on a separate filesystem or partition, enforce size limits, and choose retention that preserves useful error history without accumulating unmanageable files. section 7.2 section 7.8
A practical PostgreSQL setup should:
- Separate PostgreSQL log storage from the main database storage. section 7.2
- Enforce log size limits and configure log_rotation_size, log_rotation_age, and log_truncate_on_rotation when operating-system facilities are not used. section 7.2
- Monitor disk usage and logs before deleting files or resizing volumes; identify what consumes space first. section 7.8
- Balance retention against scanability and the need to preserve useful error history. section 7.2
When logging becomes the outage
A repeatedly failing routine emits errors rapidly. If its logs share a disk with PGDATA, continued execution can exhaust space and bring down the database. Separate log storage limits the blast radius, while rotation and size limits keep the logging stream bounded. section 7.2
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
[...]
Inspect the captured PostgreSQL ERROR output for a missing relation and its logged SQL statement, illustrating why retained logs support diagnosis. section 7.2
