PostgreSQL crash due to lack of device space

Question

We run 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 132.132.132.32 postgres ERROR: could not extend file "base/71592268/71621568": No space left on device
2022年05月09日 23:26:29 EDT pg_restore db123 132.132.132.32 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 /var/lib/pgsql/11/data/base/ directory:

# 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

Questions:

1. How to know which base directory corresponds to which database name without access to the PostgreSQL server?
2. Once we know which directory is assigned to which database, is it safe to delete it?

• VL-80: Per logs you posted the database performed a graceful shutdown - it did not crash as the title of the question says.

Answer 1

You cannot know which directory belongs to which database without a look at pg_database or the file for that table (probably /var/lib/pgsql/11/data/global/1262).

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.