PostgresDB 53100: Disk full

When the disk storage is full and cannot accommodate more data.
  1. Check Disk Usage: First, identify the disk usage by running the following command in the terminal:
  2. df -h
  3. Identify Large Objects or Tables: Connect to your PostgreSQL database and run the following queries to find large objects or tables that might be consuming a lot of space:
    • For large tables:
    • SELECT nspname || '.' || relname AS "relation",
      pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE nspname NOT IN ('pg_catalog', 'information_schema')
      AND C.relkind <> 'i'
      AND nspname !~ '^pg_toast'
      ORDER BY pg_total_relation_size(C.oid) DESC
      LIMIT 5;
    • For large objects:
    • SELECT oid, pg_size_pretty(pg_total_relation_size(loid)) as size
      FROM pg_largeobject_metadata
      ORDER BY size DESC
      LIMIT 5;
  4. Check for Unused Indexes: Unused or duplicate indexes can take up space unnecessarily. Check for them with:
  5. SELECT schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
    FROM pg_stat_user_indexes ui
    JOIN pg_index i ON ui.indexrelid = i.indexrelid
    WHERE NOT indisunique AND NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conindid = ui.indexrelid
    )
    ORDER BY pg_relation_size(i.indexrelid) DESC
    LIMIT 5;
  6. Vacuum and Analyze: If the database can't be trimmed, consider running a vacuum (especially if you find tables with a lot of dead tuples). This can free up space without needing to drop data.
    • To vacuum all:VACUUM (VERBOSE, ANALYZE);
  7. Archive and Drop Old Data: If specific tables are identified as large and contain old data that is no longer needed, consider archiving that data (if necessary) and then dropping or deleting it.
    • To delete old records (example):
    • DELETE FROM your_table_name WHERE your_date_column < 'YYYY-MM-DD';
    • To drop an entire table:
    • DROP TABLE your_table_name;
  8. Increase Disk Space: If cleaning up is not enough or not possible, consider increasing your disk space. This might involve contacting your cloud provider or managing your server's storage.

Execute these steps cautiously, especially when dropping data or tables, to avoid losing important information. Ensure you have appropriate backups before making permanent changes.

Master

PostgresDB

in Minutes — Grab the Ultimate Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Real-world configs/examples
Handy troubleshooting shortcuts
Your email is safe with us. No spam, ever.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

PostgresDB

Cheatsheet

(Perfect for DevOps & SREs)

Most-used commands
Your email is safe with us. No spam, ever.

Thankyou for your submission

We have sent the cheatsheet on your email!
Oops! Something went wrong while submitting the form.

MORE ISSUES

Made with ❤️ in Bangalore & San Francisco 🏢

Doctor Droid