All we need is an easy explanation of the problem, so here it is.
PostgreSQL 11 under a
CentOS 7 server.
Recently, we had several warnings in the log that caused the PostgreSQL server to crash:
2022-05-09 23:26:29 EDT pg_restore db123 126.96.36.199 postgres ERROR: could not extend file "base/71592268/71621568": No space left on device 2022-05-09 23:26:29 EDT pg_restore db123 188.8.131.52 postgres HINT: Check free disk space. (...) 2022-05-09 23:26:33 EDT DETAIL: Could not open file "pg_notify/0000": No space left on device. 2022-05-09 23:26:34 EDT LOG: database system is shut down
There are several databases under the
# du -h /var/lib/pgsql/11/data/base/ 7.7M /var/lib/pgsql/11/data/base/1 7.7M /var/lib/pgsql/11/data/base/13877 8.1M /var/lib/pgsql/11/data/base/13878 0 /var/lib/pgsql/11/data/base/pgsql_tmp 61M /var/lib/pgsql/11/data/base/852671 166M /var/lib/pgsql/11/data/base/852672 699M /var/lib/pgsql/11/data/base/852673 83M /var/lib/pgsql/11/data/base/852674 14M /var/lib/pgsql/11/data/base/852675 476M /var/lib/pgsql/11/data/base/852676 131M /var/lib/pgsql/11/data/base/852677 14G /var/lib/pgsql/11/data/base/1102146 9.9M /var/lib/pgsql/11/data/base/30205118 9.4M /var/lib/pgsql/11/data/base/34117470 274M /var/lib/pgsql/11/data/base/71255881 9.2G /var/lib/pgsql/11/data/base/71501647 9.2G /var/lib/pgsql/11/data/base/71592267 9.8M /var/lib/pgsql/11/data/base/60242430 9.2G /var/lib/pgsql/11/data/base/71423479 8.1G /var/lib/pgsql/11/data/base/71423480 6.5G /var/lib/pgsql/11/data/base/71592268
- How to know which base directory corresponds to which database name without access to the PostgreSQL server?
- Once we know which directory is assigned to which database, is it safe to delete it?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You cannot know which directory belongs to which database without a look at
pg_database or the file for that table (probably
But you cannot start deleting files or directories in the data directory. That will certainly wreck your database cluster. The only solution is to increase the available disk space in the file system, then you will be able to start the server.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂