Fix PostgreSQL 'No space left on device' Error: could not write to file base/...

TL;DR

Your PostgreSQL data partition is full. Free disk space immediately, then restart PostgreSQL if it crashed.

# Check which partition is full
df -h

# Check PostgreSQL data directory size
du -sh /var/lib/postgresql/*/main

# Quick wins — clear old logs and temp files
sudo find /var/log/postgresql -name '*.log' -mtime +7 -delete
sudo find /tmp -name 'pgsql_tmp*' -delete

# Reload/restart PostgreSQL if it's stuck
sudo systemctl restart postgresql

What's Actually Happening

PostgreSQL writes everything — rows, indexes, WAL segments, temp sort files — to the filesystem. When the partition hosting the data directory (/var/lib/postgresql by default) hits 100%, any write attempt fails immediately:

ERROR: could not write to file "base/pgsql_tmp/pgsqltmp12345.0": No space left on device
# or
ERROR: could not write to file "base/16384/1259": No space left on device
# or
PANIC: could not write to file "pg_wal/000000010000000000000001": No space left on device

The path after base/ tells you exactly what failed: a temp file for a sort/hash join, a heap file, or a WAL segment. WAL failures are the worst of these. PostgreSQL panics and shuts down entirely to protect data integrity — so act fast.

Step 1 — Diagnose What's Eating Space

# See all partitions and current usage
df -h

# Find the biggest directories under the postgres data dir
sudo du -sh /var/lib/postgresql/*/main/* | sort -rh | head -20

# Check WAL accumulation specifically
sudo du -sh /var/lib/postgresql/*/main/pg_wal

# Temp files (can balloon fast during bad queries)
sudo du -sh /var/lib/postgresql/*/main/base/pgsql_tmp 2>/dev/null || echo "no temp dir"

# Check PostgreSQL log directory
du -sh /var/log/postgresql/

Common culprits, roughly in order of frequency:

• WAL accumulation — a stale replication slot or failing archive_command silently holds hundreds of 16 MB WAL segments
• Temp files — one runaway query doing a massive sort or hash join on disk
• Table bloat — dead tuples not yet vacuumed, or TOAST overflow on wide tables
• Log files — verbose logging accidentally left on in production
• OS-level files — an unrelated application on the same partition quietly filling things up

Fix Approaches

A. Clear WAL Bloat (Most Common Cause)

Stale replication slots are the sneakiest culprit. PostgreSQL holds every WAL segment (16 MB each) until all slots have consumed it — even if the replica disappeared weeks ago. One dead slot can quietly accumulate gigabytes. Check first:

-- Check replication slots and how far behind they are
SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

A slot with gigabytes of lag and active = false is your problem. Drop it:

-- Dropping this lets PostgreSQL recycle the retained WAL segments
SELECT pg_drop_replication_slot('slot_name');

PostgreSQL recycles WAL on the next checkpoint. Force one immediately if you're in a hurry:

CHECKPOINT;

B. Delete Old Logs

# Delete logs older than 3 days
sudo find /var/log/postgresql -name '*.log' -mtime +3 -delete

# Truncate the current log if it's enormous
# (don't delete it — postgres still has the file open)
sudo truncate -s 0 /var/log/postgresql/postgresql-$(date +%Y-%m-%d)*.log

C. Kill the Query Causing Temp Files

A bloated pgsql_tmp directory almost always means one query is doing a massive sort or hash join on disk. Kill it — PostgreSQL cleans up the temp files automatically once the query dies:

-- Find the longest-running queries
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 10;

-- Terminate the offending query by its pid
SELECT pg_terminate_backend(12345);

D. Free Space on the OS Side

# Hunt for large files anywhere on the partition
sudo find / -xdev -size +100M -printf '%s %p\n' 2>/dev/null | sort -rn | head -20

# Clean package manager caches
sudo apt-get clean        # Debian/Ubuntu
sudo yum clean all        # CentOS/RHEL

# Trim systemd journal (this quietly grows to gigabytes on busy servers)
sudo journalctl --vacuum-size=200M

E. Emergency: Extend Disk or Move the WAL Directory

Sometimes you can't free enough space no matter what you delete. Adding capacity is the only way out:

# Option 1: resize the cloud disk (AWS EBS, GCP PD, etc.) then grow the filesystem
# After resizing in the cloud console:
sudo resize2fs /dev/xvda1    # ext4
sudo xfs_growfs /            # xfs

# Option 2: move pg_wal to another partition with free space
sudo systemctl stop postgresql
sudo mv /var/lib/postgresql/14/main/pg_wal /mnt/extra_disk/pg_wal
sudo ln -s /mnt/extra_disk/pg_wal /var/lib/postgresql/14/main/pg_wal
sudo chown -R postgres:postgres /mnt/extra_disk/pg_wal
sudo systemctl start postgresql

F. Long-Term: Table Bloat Cleanup

Dead tuples pile up when autovacuum can't keep pace — high write load, long-running transactions blocking cleanup, or tables too large for the default autovacuum thresholds. Find the worst offenders first:

-- Find tables with the most dead tuples
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Regular VACUUM: marks space for reuse, no table lock, won't return space to the OS
VACUUM tablename;

-- VACUUM FULL: returns space to the OS, but locks the table and rewrites it entirely
-- Use with caution in production
VACUUM FULL tablename;

Verify the Fix

# Confirm space is freed
df -h /var/lib/postgresql

# Check PostgreSQL is healthy
sudo systemctl status postgresql

# Quick write test to confirm no errors
psql -U postgres -c "CREATE TEMP TABLE _test (id int); INSERT INTO _test VALUES (1); DROP TABLE _test;"

# Scan recent logs for lingering errors
sudo tail -50 /var/log/postgresql/postgresql-$(date +%Y-%m-%d)*.log

Prevent It From Happening Again

• Alert at 80% disk usage — by the time you hit 100%, you're already in crisis mode.
• Cap WAL retention if you're not using replication slots: set wal_keep_size = 1GB (PG13+) or wal_keep_segments = 64 (PG12) in postgresql.conf.
• Enable log_temp_files = 64MB in postgresql.conf — this logs any query that writes more than 64 MB of temp data, so you catch disk hogs before they cause an outage.
• Keep work_mem in check. The default is 4 MB per sort operation. Bumping it to 256 MB sounds appealing, but 20 parallel queries each running multiple sorts can burn through gigabytes of temp space in minutes.
• Verify autovacuum is running and not blocked — a single long-running transaction can prevent it from cleaning any table it touches.