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 22.214.171.124 postgres ERROR: could not extend file "base/71592268/71621568": No space left on device 2022-05-09 23:26:29 EDT pg_restore db123 126.96.36.199 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?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.