PGDATA is the PostgreSQL data directory containing a database cluster’s data files. Treating its filesystem as unlimited is a dangerous operational mistake: PostgreSQL commonly writes logs there by default, so unchecked logging can consume the space needed for database activity. section 7.2 section 7.1
Track the filesystem containing PGDATA because space exhaustion can have different effects:
- Filling the data filesystem can prevent normal application database activities. section 7.1
- Filling the WAL filesystem can prevent journaling and commits, potentially causing PostgreSQL to panic and shut down. section 7.1
- Deleting rows is not an instant recovery: MVCC leaves dead rows, and VACUUM may reclaim space only inside existing data files. section 7.1
- Unchecked error logging can fill PGDATA rapidly, especially when faulty retry code submits a failing query indefinitely. section 7.2
A retry loop fills the database filesystem
A routine repeatedly queries the misspelled table erp.energy_use instead of erp.energy_usage. Every failure is logged, and rapid retries can fill the filesystem containing PGDATA before operators react. section 7.2
Listing 7.1 Routine to log the sum of the previous day’s energy use
import psycopg
from datetime import date, timedelta as td
with psycopg.connect("dbname=frogge user=frogge") as conn:
try:
while True:
with conn.cursor() as cur:
try:
# I have learned my lesson about using BETWEEN!
cur.execute('''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''')
total = cur.fetchone()[0]
cur.execute('''INSERT INTO audit.audit_log
(what, who, tstamp)
VALUES (%s, %s, now())''',
(f"Energy usage for {date.today() - td(days=1)}: "
+ f"{total} kWh", "Frogge Emporium"))
conn.commit()
break
# If this goes wrong, something must be keeping the DB busy
# and we can just retry
except psycopg.errors.Error:
conn.rollback()
finally:
conn.close()
Inspect the routine that combines a retry loop, a failing query, and audit logging to see how a small application error becomes a storage incident. section 7.2
