PostgreSQL Disk Full: Tablespace and Storage Management

# PostgreSQL Disk Full: Tablespace and Storage Management

Your PostgreSQL database just stopped accepting writes with this error:

ERROR:  could not extend file "base/16384/16390": No space left on device
HINT:  Check free disk space.
FATAL:  the database system is starting up

A full disk is a critical situation that can corrupt your database. Let's resolve this safely.

Immediate Assessment

Check Disk Space

```bash # System-wide disk usage df -h

# PostgreSQL data directory specifically du -sh /var/lib/postgresql/* du -sh /var/lib/postgresql/16/main/*

# Find largest directories du -h --max-depth=2 /var/lib/postgresql/16/main | sort -hr | head -20 ```

Check Tablespace Sizes

```sql -- Tablespace sizes SELECT spcname AS tablespace, pg_size_pretty(pg_tablespace_size(oid)) AS size FROM pg_tablespace;

-- Database sizes SELECT datname AS database, pg_size_pretty(pg_database_size(oid)) AS size FROM pg_database ORDER BY pg_database_size(oid) DESC;

-- Largest tables SELECT schemaname, relname AS table_name, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS table_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS indexes_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20; ```

Emergency Space Recovery

1. Clean Up PostgreSQL Log Files

```bash # Find PostgreSQL log location sudo -u postgres psql -c "SHOW log_directory;"

# Check log sizes sudo du -sh /var/log/postgresql/*

# Compress old logs sudo gzip /var/log/postgresql/postgresql-16-main.log.* 2>/dev/null

# Remove logs older than 30 days sudo find /var/log/postgresql -name "*.log.*" -mtime +30 -delete sudo find /var/log/postgresql -name "*.gz" -mtime +30 -delete ```

2. Clean Up WAL Files (With Replication Caution)

Warning: Only do this if you understand your replication setup. Removing WAL files needed for replication will break standby servers.

```sql -- Check WAL directory size SELECT pg_size_pretty(sum(size)) AS wal_size FROM pg_ls_waldir();

-- Check current WAL position SELECT pg_current_wal_lsn();

-- Check if replication is active SELECT * FROM pg_stat_replication;

-- Force a checkpoint to recycle WAL files CHECKPOINT;

-- View replication slots holding WAL SELECT slot_name, slot_type, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots; ```

If you have inactive replication slots, they prevent WAL cleanup:

-- Drop inactive replication slots
SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE NOT active;

3. Remove Temporary Files

```sql -- Check for temp files SELECT temp_files, temp_bytes, pg_size_pretty(temp_bytes) AS temp_size FROM pg_stat_database;

-- Temp files location SHOW temp_tablespaces; ```

```bash # Clean up PostgreSQL temp files (should be automatic, but check) sudo find /var/lib/postgresql -name "*.tmp" -delete

# Check for orphaned temp files sudo ls -la /tmp/postgresql*.tmp 2>/dev/null ```

4. Vacuum to Reclaim Space

```sql -- Find tables needing vacuum SELECT schemaname, relname, n_live_tup, n_dead_tup, n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0) * 100 AS dead_ratio FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;

-- Vacuum specific tables VACUUM VERBOSE mytable;

-- Vacuum full to actually reclaim disk space (requires exclusive lock) VACUUM FULL VERBOSE my_large_table;

-- Vacuum entire database VACUUM; ```

Note: VACUUM FULL rewrites the entire table and requires enough space for a copy of the table. If you're critically low on space, this may fail. Instead:

-- Use pg_repack (requires extension and additional disk)
-- Or recreate the table manually
CREATE TABLE new_table AS SELECT * FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;

5. Drop Unused Indexes

```sql -- Find unused indexes SELECT schemaname, relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan AS index_scans, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE NOT indisunique AND idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

-- Drop unused index DROP INDEX IF EXISTS my_schema.unused_index_name;

-- Or drop and recreate later -- CREATE INDEX CONCURRENTLY avoids locking ```

6. Remove Old Data

```sql -- Delete old records (use with caution) DELETE FROM audit_logs WHERE created_at < now() - interval '90 days';

-- Partition older data and drop -- If you have partitioned tables: DROP TABLE events_2023_01;

-- Truncate tables you don't need TRUNCATE TABLE temporary_import_data;

-- Vacuum after deletions to reclaim space VACUUM VERBOSE audit_logs; ```

Add More Storage Space

Add a New Tablespace on Different Disk

```sql -- Create directory for new tablespace -- (Run on OS first) -- sudo mkdir -p /mnt/larger_disk/pg_data -- sudo chown postgres:postgres /mnt/larger_disk/pg_data

-- Create tablespace CREATE TABLESPACE large_data LOCATION '/mnt/larger_disk/pg_data';

-- Move tables to new tablespace ALTER TABLE large_table SET TABLESPACE large_data;

-- Move indexes ALTER INDEX large_table_idx SET TABLESPACE large_data;

-- Set as default for a database ALTER DATABASE mydb SET TABLESPACE large_data;

-- Set as default for new objects ALTER USER appuser SET default_tablespace = large_data; ```

Move Entire Data Directory

```bash # Stop PostgreSQL sudo systemctl stop postgresql

# Copy data to new location sudo rsync -av /var/lib/postgresql/16/main/ /mnt/new_disk/pg_data/

# Update PostgreSQL configuration sudo vi /etc/postgresql/16/main/postgresql.conf # data_directory = '/mnt/new_disk/pg_data'

# Update AppArmor (Ubuntu) or SELinux (RHEL) sudo vi /etc/apparmor.d/tunables/alias # Add: alias /var/lib/postgresql/ -> /mnt/new_disk/pg_data/,

# Restart PostgreSQL sudo systemctl start postgresql ```

Monitor and Prevent Future Issues

Set Up Disk Space Monitoring

```sql -- Create monitoring view CREATE OR REPLACE VIEW disk_usage AS SELECT current_setting('data_directory') AS data_dir, pg_size_pretty(pg_database_size(oid)) AS db_size, (SELECT count(*) FROM pg_stat_activity) AS active_connections FROM pg_database WHERE datname = current_database();

-- Tables needing attention CREATE OR REPLACE VIEW tables_needing_vacuum AS SELECT schemaname, relname, n_dead_tup, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) AS size, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > n_live_tup * 0.2 OR n_dead_tup > 100000 ORDER BY n_dead_tup DESC; ```

Configure Autovacuum Properly

```conf # postgresql.conf

# Enable autovacuum autovacuum = on

# More aggressive settings for busy tables autovacuum_vacuum_scale_factor = 0.1 # Vacuum when 10% of tuples are dead autovacuum_vacuum_threshold = 1000 # Minimum dead tuples before vacuum autovacuum_analyze_scale_factor = 0.05 autovacuum_analyze_threshold = 500

# For write-heavy databases autovacuum_vacuum_scale_factor = 0.05 # More aggressive autovacuum_vacuum_cost_limit = 1000 # Higher cost limit autovacuum_vacuum_cost_delay = 2ms # Lower delay

# Track vacuum progress track_counts = on ```

Per-Table Autovacuum Tuning

```sql -- More aggressive autovacuum for specific tables ALTER TABLE my_busy_table SET ( autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 500 );

-- Less aggressive for large, infrequently updated tables ALTER TABLE archive_table SET ( autovacuum_vacuum_scale_factor = 0.5, autovacuum_vacuum_threshold = 100000 ); ```

Check for Bloat

```sql -- Estimate table bloat SELECT schemaname, relname, n_live_tup, n_dead_tup, pg_size_pretty(pg_relation_size(relid)) AS table_size, ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS bloat_percent FROM pg_stat_user_tables WHERE n_dead_tup > 0 ORDER BY n_dead_tup DESC;

-- Or use pgstattuple extension CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('my_table'); ```

Preventing Recovery Issues

When disk is critically full, PostgreSQL may not start:

```bash # If PostgreSQL won't start due to disk space # Start in single-user mode postgres --single -D /var/lib/postgresql/16/main postgres

# In single-user mode, you can run SQL postgres=# DELETE FROM large_table WHERE id < 1000000; postgres=# VACUUM;

# Exit with Ctrl+D ```

```bash # Free emergency space by removing core dumps sudo rm -f /var/lib/postgresql/core.*

# Remove postmaster.pid only if PostgreSQL is not running sudo rm -f /var/lib/postgresql/16/main/postmaster.pid ```

Verification Steps

After cleanup:

```bash # Verify disk space df -h

# Check PostgreSQL is healthy psql -U postgres -c "SELECT version();"

# Verify data integrity psql -U postgres -c "SELECT datname, pg_database_size(oid) FROM pg_database;"

# Check for corruption pg_verifybackup /var/lib/postgresql/16/main 2>/dev/null || echo "pg_verifybackup not available" ```

Quick Reference Commands