How to Fix PostgreSQL Error: Disk Full

The "Disk full" error occurs when PostgreSQL cannot write data because the filesystem has run out of available space. This critical error can cause database crashes, transaction failures, and data corruption if not addressed immediately.

Impact

This error makes the database read-only or completely unavailable, prevents writes, causes transaction rollbacks, and can lead to database corruption. It requires immediate intervention to prevent data loss.

Common Causes

1. Insufficient disk space allocation
2. Large table or index growth
3. Excessive transaction logs (WAL files)
4. Log files consuming space
5. Temporary files not cleaned up
6. Database bloat from dead tuples
7. Large backup files on same disk
8. Forgotten pg_dump files

Troubleshooting and Resolution Steps

1. Check disk space immediately:

   # Check overall disk usage
   df -h
   
   # Check PostgreSQL data directory specifically
   du -sh /var/lib/postgresql/
   du -sh /var/lib/postgresql/15/main/
   
   # Find largest directories
   du -sh /var/lib/postgresql/15/main/* | sort -rh | head -10
   

2. Identify space consumers:

   -- Check database sizes
   SELECT
       datname,
       pg_size_pretty(pg_database_size(datname)) AS size
   FROM pg_database
   ORDER BY pg_database_size(datname) DESC;
   
   -- Check table sizes
   SELECT
       schemaname,
       tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
   FROM pg_tables
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
   LIMIT 20;
   

3. Clean up WAL files (carefully):

   # Check WAL directory size
   du -sh /var/lib/postgresql/15/main/pg_wal/
   
   # WAL files should be managed automatically
   # If accumulating, check archiving configuration
   
   # In postgresql.conf, ensure archive_command works
   # Or if not using archiving:
   # wal_level = replica (not 'archive')
   # archive_mode = off
   

4. Clean up log files:

   # Check log file size
   du -sh /var/log/postgresql/
   
   # Compress old logs
   gzip /var/log/postgresql/postgresql-*.log
   
   # Or delete old logs (be careful!)
   find /var/log/postgresql/ -name "*.log" -mtime +30 -delete
   
   # Configure log rotation in /etc/logrotate.d/postgresql
   /var/log/postgresql/*.log {
       weekly
       rotate 4
       compress
       delaycompress
       missingok
       notifempty
   }
   

5. Remove temporary files:

   # PostgreSQL temp files (safe to delete when DB is stopped)
   ls -lh /var/lib/postgresql/15/main/base/pgsql_tmp*
   
   # Remove old temp files (PostgreSQL not running)
   sudo systemctl stop postgresql
   find /var/lib/postgresql/15/main/base/ -name "pgsql_tmp*" -delete
   sudo systemctl start postgresql
   

6. VACUUM to reclaim space:

   -- Regular VACUUM (doesn't return space to OS)
   VACUUM VERBOSE tablename;
   
   -- VACUUM FULL returns space but locks table
   VACUUM FULL VERBOSE tablename;
   
   -- For all tables
   VACUUM FULL;
   
   -- Check for bloat
   SELECT
       schemaname,
       tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) -
                      pg_relation_size(schemaname||'.'||tablename)) AS external_size
   FROM pg_tables
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
   

7. Drop unnecessary indexes:

   -- Find unused indexes
   SELECT
       schemaname,
       tablename,
       indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan
   FROM pg_stat_user_indexes
   WHERE idx_scan = 0
   AND indexrelid NOT IN (
       SELECT indexrelid FROM pg_index WHERE indisprimary OR indisunique
   )
   ORDER BY pg_relation_size(indexrelid) DESC;
   
   -- Drop unused index
   DROP INDEX indexname;
   

8. Delete old data:

   -- Archive and delete old records
   BEGIN;
   -- Optionally export first
   COPY (SELECT * FROM old_logs WHERE created_at < '2023-01-01')
   TO '/tmp/archived_logs.csv' CSV HEADER;
   
   -- Delete in batches to avoid long locks
   DELETE FROM old_logs
   WHERE id IN (
       SELECT id FROM old_logs
       WHERE created_at < '2023-01-01'
       LIMIT 10000
   );
   COMMIT;
   
   -- Then VACUUM to reclaim space
   VACUUM FULL old_logs;
   

9. Extend disk space:

   # For LVM (Logical Volume Manager)
   sudo lvextend -L +50G /dev/vg0/postgresql_lv
   sudo resize2fs /dev/vg0/postgresql_lv
   
   # For cloud providers (AWS, GCP, Azure)
   # Extend volume through provider console
   # Then resize filesystem
   sudo growpart /dev/xvda 1
   sudo resize2fs /dev/xvda1
   
   # Move data to larger volume
   sudo systemctl stop postgresql
   sudo rsync -av /var/lib/postgresql/ /new/larger/volume/postgresql/
   # Update postgresql configuration to point to new location
   sudo systemctl start postgresql
   

10. Set up monitoring and alerts:

    # Create monitoring script
    cat > /usr/local/bin/check_pg_disk.sh << 'EOF'
    #!/bin/bash
    THRESHOLD=80
    USAGE=$(df /var/lib/postgresql | tail -1 | awk '{print $5}' | sed 's/%//')
    
    if [ "$USAGE" -gt "$THRESHOLD" ]; then
        echo "PostgreSQL disk usage is ${USAGE}% - exceeds threshold ${THRESHOLD}%"
        # Send alert (email, Slack, PagerDuty, etc.)
    fi
    EOF
    
    chmod +x /usr/local/bin/check_pg_disk.sh
    
    # Add to crontab
    */5 * * * * /usr/local/bin/check_pg_disk.sh
    

Additional Information

• Always maintain at least 20% free disk space
• Monitor disk usage proactively with alerts
• Configure autovacuum appropriately
• Use tablespaces to distribute data across volumes
• Implement data retention policies
• Regular archiving of old data
• Consider partitioning for large tables
• Keep backups on separate volumes

Frequently Asked Questions

Q: Can I safely delete files from PostgreSQL data directory?
A: Never manually delete files from the data directory while PostgreSQL is running. Use SQL commands (DROP, DELETE, VACUUM) instead. Manual deletion can corrupt the database.

Q: How do I prevent WAL files from filling disk?
A: Ensure archive_command works properly if archiving is enabled, or disable archiving if not needed. WAL files are automatically recycled when no longer needed.

Q: What's the difference between VACUUM and VACUUM FULL?
A: VACUUM marks space as reusable within the file but doesn't return it to the OS. VACUUM FULL rewrites the entire table and returns space to the OS, but locks the table.

Q: Can disk full cause database corruption?
A: Yes, if disk fills during write operations, it can lead to corruption. Always maintain adequate free space.

Q: How much free space should I maintain?
A: Keep at least 20% free for normal operations. More if you have large batch operations or rapid growth.

Q: Can I move PostgreSQL to a larger disk without downtime?
A: Difficult without downtime. Options include: streaming replication to new server, or volume expansion (cloud/LVM) with brief restart.

Q: What if I can't free up space quickly enough?
A: Emergency: Move to read-only mode, stop non-critical services, mount additional temporary storage, or perform emergency cleanup of logs/temp files.