Fix PostgreSQL "could not extend file" No Space Left on Device

Quick Answer

• Root cause: The PostgreSQL data directory, tablespace location, or the filesystem containing them has run out of disk space or inodes, preventing the database from allocating new file blocks.
• First checks: Run df -h for disk usage and df -i for inode usage immediately. Then identify the largest tables with SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) as size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;.
• Minimal fix: Execute CHECKPOINT; to force WAL recycling, then delete or archive old data from the largest tables. If WAL directory is the culprit, check and drop inactive replication slots with SELECT pg_drop_replication_slot('slot_name');.
• Environment: Applies to all PostgreSQL versions (9.x through 17.x). The error message format varies slightly by version but the root cause and recovery steps are identical.

What Problem It Solves

PostgreSQL throws the could not extend file error when it attempts to write new data to disk but the underlying filesystem has no available space. This is a critical production issue that blocks all write operations—INSERT, UPDATE, DELETE, CREATE, and even VACUUM operations that need to write new tuples. The database remains readable but cannot accept any modifications until space is freed.

This document provides a complete, actionable workflow for diagnosing the exact cause, recovering the database to a writable state, and implementing preventive measures to avoid recurrence.

Root Cause Analysis

The error appears in two primary forms depending on where the space shortage occurs:

The OID numbers in the path correspond to database objects:

• First number (e.g., 16384): database OID
• Second number (e.g., 16385): relation (table/index) OID

You can resolve these OIDs with SQL:

SQL
SELECT datname FROM pg_database WHERE oid = 16384;
SELECT relname FROM pg_class WHERE oid = 16385;

Common Errors and Fixes

ERROR: could not extend file "base/16384/16385": No space left on device

Immediate recovery steps:

1. Check disk and inode usage:

BASH
df -h
df -i

2. Free space by cleaning old logs:

BASH
# Clean PostgreSQL log files older than 7 days
sudo find /var/lib/postgresql/*/main/log -name "*.log" -mtime +7 -delete

# Clean system logs
sudo find /var/log -name "*.log.*" -mtime +7 -delete

3. Force WAL checkpoint and identify large tables:

SQL
CHECKPOINT;

SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) as size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

4. Delete or archive old data from large tables, then run VACUUM to return space to the OS:

SQL
DELETE FROM large_table WHERE created_at < now() - interval '90 days';
VACUUM FULL large_table;

5. If space is still insufficient, create a new tablespace on a different disk and move tables:

SQL
CREATE TABLESPACE new_space LOCATION '/mnt/new_disk';
ALTER TABLE large_table SET TABLESPACE new_space;

ERROR: could not extend file "pg_tblspc/16386/PG_14_202107181/16387/16388": No space left on device

1. Identify the tablespace path:

SQL
SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;

2. Check space on that specific mount point:

BASH
df -h /path/to/tablespace

3. Move tables back to default tablespace or expand the disk:

SQL
ALTER TABLE table_name SET TABLESPACE pg_default;

WAL directory fills the disk while data directory has space

1. Check WAL directory size:

BASH
du -sh /var/lib/postgresql/14/main/pg_wal/

2. Force checkpoint to trigger WAL recycling:

SQL
CHECKPOINT;

3. Check for inactive replication slots that prevent WAL deletion:

SQL
SELECT slot_name, active FROM pg_replication_slots;

4. Drop inactive slots:

SQL
SELECT pg_drop_replication_slot('slot_name');

5. Adjust WAL retention parameters in postgresql.conf:

max_wal_size = 2GB
min_wal_size = 1GB

Then reload: SELECT pg_reload_conf();

VACUUM FULL fails with "No space left on device"

VACUUM FULL requires additional disk space equal to the size of the table being rebuilt. If the disk is nearly full, it will fail.

Alternatives:

• Free at least the table's size in additional space before retrying
• Use pg_repack extension which can rebuild tables online without requiring extra space
• Delete some data first, run a regular VACUUM, then attempt VACUUM FULL

Production Notes and Security Checks

Concurrency and Locking

• VACUUM FULL acquires an ACCESS EXCLUSIVE lock on the table, blocking all reads and writes. Schedule it during maintenance windows.
• Multiple concurrent VACUUM or CHECKPOINT operations can interfere. Monitor with:

SQL
SELECT pid, state, query FROM pg_stat_activity WHERE query LIKE '%VACUUM%' OR query LIKE '%CHECKPOINT%';

File Locking

• Never manually delete files from pg_wal/ or base/ directories. PostgreSQL manages these files internally. Manual deletion will corrupt the database.
• Before dropping a tablespace directory on disk, always run DROP TABLESPACE tablespace_name; in the database first.

Permission Control

• All filesystem commands (df, du, systemctl) typically require sudo or root privileges.
• Run psql commands as the postgres system user. Avoid using the database superuser for routine operations.
• For remote script execution, use .pgpass files or environment variables for password management—never hardcode passwords in scripts.

Data Safety

• Before executing DELETE or DROP TABLE, verify backups exist.
• After changing max_wal_size or min_wal_size, reload the configuration with SELECT pg_reload_conf(); and monitor replication lag if streaming replication is in use.

FAQ

Q: Why does df -h show free space but PostgreSQL still reports "No space left on device"?

A: This is typically caused by inode exhaustion. Run df -i to check inode usage. If inodes are 100% consumed, the filesystem cannot create new files even if data blocks are free. This happens when there are millions of small files—often from unarchived WAL segments, temporary files in /tmp, or accumulated log files.

Fix: Delete small files in bulk. For PostgreSQL, check pg_wal/ for excessive WAL segments and ensure replication slots are not blocking cleanup. Clean /tmp and log directories.

Q: I ran VACUUM FULL but disk space didn't decrease. Why?

A: Several reasons:

1. Long-running transactions: Check SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; and terminate them. Open transactions prevent dead tuple cleanup.
2. Open cursors: Similar to long transactions, cursors block space reclamation.
3. Failed VACUUM FULL: If the operation ran out of space during the rewrite, it rolls back and no space is freed.
4. Index bloat remains: VACUUM FULL only reclaims table space. Run REINDEX TABLE table_name; or REINDEX INDEX index_name; separately.

Best practice: Run VACUUM (VERBOSE, ANALYZE); first to inspect dead tuple counts before deciding on VACUUM FULL.

Q: How can I safely clean WAL logs without stopping the database?

A: Follow this safe sequence:

1. Execute CHECKPOINT; to flush dirty buffers and advance the WAL recycling point.
2. Check replication slots: SELECT * FROM pg_replication_slots;. Drop any inactive slots with SELECT pg_drop_replication_slot('slot_name');.
3. Adjust max_wal_size and min_wal_size in postgresql.conf to reasonable values (e.g., 2GB/1GB) and reload.
4. Never manually delete files from pg_wal/. Let PostgreSQL manage WAL lifecycle.
5. If WAL archiving is enabled, ensure the archive command succeeds and the archive destination has sufficient space. PostgreSQL deletes archived WAL segments automatically.