PostgreSQL crash due to lack of device space



  • 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?


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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2