What is
PostgresDB 53100: Disk full
?
- Check Disk Usage: First, identify the disk usage by running the following command in the terminal:
df -h- 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;
- Check for Unused Indexes: Unused or duplicate indexes can take up space unnecessarily. Check for them with:
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;- 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);
- To vacuum all:
- 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;
- 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.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Master
PostgresDB
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
Most-used commands
Real-world configs/examples
Handy troubleshooting shortcuts
Oops! Something went wrong while submitting the form.
PostgresDB
Cheatsheet
(Perfect for DevOps & SREs)
Most-used commands
Oops! Something went wrong while submitting the form.
MORE ISSUES
Backed by
Deep Sea Tech Inc. — Made with ❤️ in Bangalore & San Francisco 🏢