This was the most common cause of calls at night: your cluster ran out of space. Nowadays, we can add storage on the fly, storage is cheap and (normally) we do monitor disk space so that this should not happen.

Anyhow, how can we deal with such a problem?

To simulate such a problem, I created a virtual host and I created a big empty file to almost fill the file system. Then I simply inserted a lot of rows until the disk ran out of space.

The general diagram

Here’s how I solve the problem:

Find some details for some steps here:

1. Add more disk and/or remove unnecessary files

Whatever you do, never touch anything inside $PGDATA. If $PGDATA contains links to other directories, don’t touch them too. In particular (and I can’t stress it enough), never touch a directory named pg_xlog or pg_wal and never touch a file which name is made of 24 hexadecimal figures.

There is one exception, in case someone had the bad idea to store Postgres log files inside $PGDATA. In that case, you may remove some old log files if there are a lot of them. I strongly encourage you to set your logs properly in the future.

I strongly recommend moving the files you want to delete instead of simply deleting them.

2. Are the WALs corrupted?

Several log messages should make you think WAL files are corrupted. Here is a list:

• LOG: incorrect resource manager data checksum in record at 0/2000040
• LOG: invalid primary checkpoint record
• PANIC: could not locate a valid checkpoint record

3. Fix the WAL corruption

The best way to fix a WAL corruption is to restore a copy of the WAL that was streamed either to a standby or to your backup tool (if your backup tool can stream WALs).

But you might not stream your WAL files or they might be corrupted too. In that case, I strongly encourage you to read the whole documentation page of the pg_resetwal tool before trying to use it. Moreover, you should copy the content of your WAL directory safely to another location before using it.

4. Check data corruption

Postgres can enable checksums on all data pages. Sadly this mode is not active by default, so the probability that it’s not the case in your cluster is very high.

Anyhow, should you have checksum enabled, use pg_checksums to check for data corruption (see the documentation).

If checksums are disabled on your cluster, try using pg_dumpall (or several pg_dump) to force Postgres to read all your data and check for any corruption. If you have too much data to store the resulting file (or files), redirect the output to /dev/null.

Example under Ubuntu

And that’s how I do it under Ubuntu:

root@elinor:/var/lib# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

My Postgres is not running.

root@elinor:/var/lib# df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            480M     0  480M   0% /dev
tmpfs            99M   11M   89M  11% /run
/dev/sda1       9.7G  9.7G     0 100% /
tmpfs           493M     0  493M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           493M     0  493M   0% /sys/fs/cgroup
tmpfs            99M     0   99M   0% /run/user/1000

root@elinor:/var/lib# ll -h
total 8.1G
drwxr-xr-x 38 root      root      4.0K Mar  8 14:39 ./
drwxr-xr-x 13 root      root      4.0K Sep 30 15:52 ../
drwxr-xr-x  4 root      root      4.0K Sep 30 15:50 AccountsService/
drwxr-xr-x  2 root      root      4.0K Mar 21 13:58 VBoxGuestAdditions/
drwxr-xr-x  5 root      root      4.0K Mar  9 03:40 apt/
drwxr-xr-x  8 root      root      4.0K Mar 21 13:58 cloud/
drwxr-xr-x  2 root      root      4.0K May  5  2018 command-not-found/
drwxr-xr-x  2 root      root      4.0K Mar  8 14:06 dbus/
drwxr-xr-x  2 root      root      4.0K Apr 16  2018 dhcp/
drwxr-xr-x  2 root      root      4.0K Mar  8 14:06 dkms/
drwxr-xr-x  7 root      root      4.0K Mar  8 14:30 dpkg/
drwxr-xr-x  2 root      root      4.0K Apr 20  2020 git/
drwxr-xr-x  3 root      root      4.0K Sep 30 15:51 grub/
drwxr-xr-x  2 root      root      4.0K Sep 30 15:51 initramfs-tools/
drwxr-xr-x  2 landscape landscape 4.0K Sep 30 15:51 landscape/
drwxr-xr-x  2 root      root      4.0K Mar  9 06:25 logrotate/
drwxr-xr-x  2 root      root         0 Mar 21 14:36 lxcfs/
drwxr-xr-x  2 lxd       nogroup   4.0K Mar 21 13:58 lxd/
drwxr-xr-x  2 root      root      4.0K Sep 30 15:51 man-db/
drwxr-xr-x  2 root      root      4.0K Apr 24  2018 misc/
drwxr-xr-x  2 root      root      4.0K Mar  9 06:25 mlocate/
drwxr-xr-x  2 root      root      4.0K Mar  6  2017 os-prober/
drwxr-xr-x  2 root      root      4.0K Sep 30 15:50 pam/
drwxr-xr-x  2 root      root      4.0K Apr  4  2019 plymouth/
drwx------  3 root      root      4.0K Sep 30 15:49 polkit-1/
drwxr-xr-x  3 postgres  postgres  4.0K Mar  8 14:30 postgresql/
drwxr-xr-x  2 root      root      4.0K Sep 30 15:49 python/
drwxr-xr-x 18 root      root      4.0K Mar 21 14:04 snapd/
drwxr-xr-x  3 root      root      4.0K Sep 30 15:49 sudo/
drwxr-xr-x  6 root      root      4.0K Mar  8 14:06 systemd/
-rw-r--r--  1 root      root      8.0G Mar  8 14:39 test
drwxr-xr-x  2 root      root      4.0K Mar  8 14:06 ubuntu-release-upgrader/
drwxr-xr-x  3 root      root      4.0K Mar  8 14:30 ucf/
drwxr-xr-x  2 root      root      4.0K Feb 17  2020 unattended-upgrades/
drwxr-xr-x  2 root      root      4.0K Mar  8 14:06 update-manager/
drwxr-xr-x  4 root      root      4.0K Mar 21 13:59 update-notifier/
drwxr-xr-x  3 root      root      4.0K Sep 30 15:50 ureadahead/
drwxr-xr-x  2 root      root      4.0K Sep 30 15:50 usbutils/
drwxr-xr-x  3 root      root      4.0K Sep 30 15:49 vim/

The / file system is 100% full. There is a 8GB file named test under /var/lib.

root@elinor:/var/lib# tail /var/log/postgresql/postgresql-13-main.log 
2021-03-08 14:45:08.919 UTC [22579] FATAL:  could not write to file "pg_wal/xlogtemp.22579": No space left on device

Just to double-check, the lack of space on the device is the root cause of Postgres being stopped.

root@elinor:/var/lib# rm -f test
root@elinor:/var/lib# du -sh .
243M

I remove the test file.

root@elinor:/var/lib# pg_ctlcluster 13 main start
root@elinor:/var/lib# tail /var/log/postgresql/postgresql-13-main.log 
2021-03-21 14:38:49.859 UTC [2414] LOG:  starting PostgreSQL 13.2 (Ubuntu 13.2-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
2021-03-21 14:38:49.860 UTC [2414] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-03-21 14:38:49.861 UTC [2414] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-03-21 14:38:49.887 UTC [2420] LOG:  database system was shut down at 2021-03-08 14:45:08 UTC
2021-03-21 14:38:49.963 UTC [2414] LOG:  database system is ready to accept connections.

Postgres start nicely once it’s able to write again on the device.

Whenever this kind of things happen, just keep your calm and you’ll see you can fix it.