Home / Database / PostgreSQL Disk Full: Tablespace and Storage Management

Database

PostgreSQL Disk Full: Tablespace and Storage Management

Recover from PostgreSQL disk full errors by managing tablespaces, cleaning up data, and reclaiming storage space.

Published: Nov 15, 20259 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

# PostgreSQL Disk Full: Tablespace and Storage Management

Your PostgreSQL database just stopped accepting writes with this error:

bash

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.

Introduction

This article covers troubleshooting steps and solutions for PostgreSQL Disk Full: Tablespace and Storage Management. The error typically occurs in production environments and can cause service disruptions if not addressed promptly.

Symptoms

Common error messages include:

bash

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

```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 ```

```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; ```

Common Causes

Configuration misconfiguration
Missing or incorrect credentials
Network connectivity issues
Version compatibility problems
Resource exhaustion or limits
Permission or access denied

Step-by-Step Fix

1.Check logs for specific error messages
2.Verify configuration settings
3.Test network connectivity
4.Review recent changes
5.Apply corrective action
6.Verify the fix

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;

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:

sql

-- 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:

sql

-- 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

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

Action	Command
Disk usage	`df -h`
Table sizes	`SELECT pg_size_pretty(pg_total_relation_size('table'));`
Tablespace sizes	`SELECT pg_size_pretty(pg_tablespace_size('tsname'));`
Force checkpoint	`CHECKPOINT;`
Vacuum table	`VACUUM VERBOSE table_name;`
Full vacuum	`VACUUM FULL table_name;`
Drop index	`DROP INDEX index_name;`
Create tablespace	`CREATE TABLESPACE name LOCATION '/path';`
Move table	`ALTER TABLE t SET TABLESPACE ts;`
WAL size	`SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();`

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

PostgreSQL Disk Full: Tablespace and Storage Management PostgreSQL Disk Full: Tablespace and Storage Management Database PostgreSQL Disk Full: Tablespace and Storage Management troubleshooting PostgreSQL Disk Full: Tablespace and Storage Management fix Recover from PostgreSQL disk full errors by managing tablespaces, cleaning up data, and reclaiming storage space Database Recover from PostgreSQL disk full errors by managing tablespaces, cleaning up data, and reclaiming storage space

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Nov 15, 2025

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

PostgreSQL Disk Full: Tablespace and Storage Management

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Immediate Assessment

Check Disk Space

Check Tablespace Sizes

Emergency Space Recovery

1. Clean Up PostgreSQL Log Files

2. Clean Up WAL Files (With Replication Caution)

3. Remove Temporary Files

4. Vacuum to Reclaim Space

5. Drop Unused Indexes

6. Remove Old Data

Add More Storage Space

Add a New Tablespace on Different Disk

Move Entire Data Directory

Monitor and Prevent Future Issues

Set Up Disk Space Monitoring

Configure Autovacuum Properly

Per-Table Autovacuum Tuning

Check for Bloat

Preventing Recovery Issues

Verification

Quick Reference Commands

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

PostgreSQL Disk Full: Tablespace and Storage Management

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Immediate Assessment

Check Disk Space

Check Tablespace Sizes

Emergency Space Recovery

1. Clean Up PostgreSQL Log Files

2. Clean Up WAL Files (With Replication Caution)

3. Remove Temporary Files

4. Vacuum to Reclaim Space

5. Drop Unused Indexes

6. Remove Old Data

Add More Storage Space

Add a New Tablespace on Different Disk

Move Entire Data Directory

Monitor and Prevent Future Issues

Set Up Disk Space Monitoring

Configure Autovacuum Properly

Per-Table Autovacuum Tuning

Check for Bloat

Preventing Recovery Issues

Verification

Quick Reference Commands

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading