PostgreSQL crash due to lack of device space

All we need is an easy explanation of the problem, so here it is.

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?

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.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply