How to Clean Up pg_wal If the Disk Is Full

When your PostgreSQL database crashes because the pg_wal directory has run out of disk space, the situation is urgent and the recovery path is not always obvious. This guide walks through the exact steps to get your database back online, whether you need to fix a broken archive command, move the WAL directory to a larger partition, or surgically remove old WAL segments. It also covers the proactive habits that keep this scenario from happening again.

Understanding the problem

Transaction logs are a core component of all modern relational database management systems. They are designed to deliver improved performance. PostgreSQL refers to these transaction logs as Write Ahead Logs (WAL).

By writing transaction information out to these logs, PostgreSQL is able to return control to the client before needing to perform the time-consuming task of persisting data changes to disk, while retaining the ability to restore a consistent database in the event of failure.

To do this, it is critical that PostgreSQL is able to read and modify the transaction logs at all times. Should a situation arise where PostgreSQL is not able to access the transaction log, the only option it has is to shut itself down as cleanly and as quickly as possible and wait until a DBA fixes the cause of the problem.

PostgreSQL by default stores its transaction logs in the pg_wal folder of the data directory. (Note: older versions of PostgreSQL used a folder called pg_xlog. This folder was renamed in version 10 to emphasize the importance of the file within.)

All changes made to the database are first written to the transaction log before anything else can occur. When the pg_wal directory fills up and no new files can be created, your database will likely shut down and enter a state where it cannot be brought up again.

You will see the following messages after this problem arises:

Trying to connect a new session will result in this error:

$ bash-4.2$ psql -p 5438 postgres
psql: could not connect to server: No such file or directory
       Is the server running locally and accepting
       connections on Unix domain socket
       "/var/run/postgresql/.s.PGSQL.5448"?

Resolving the situation

This situation can be resolved by following one of two approaches described below. The first approach is reactive: it explains what to do once the situation has already occurred. The second is a more proactive approach that outlines steps to avoid the situation in the first place.

The database will not start due to the disk on which the WAL is stored becoming full.

This problem could be caused by a number of things:

1. Check whether archiving is functioning properly

Do this by ensuring the archive command is set correctly and the target directory has the required permissions.

If the WAL archiving option is in use, old WAL segments cannot be removed or recycled until they are archived. If WAL archiving cannot keep up with the pace that WAL files are generated, or if the archive_command fails repeatedly, old WAL logs will accumulate until the archiving issue is resolved.

The PostgreSQL logs will give you a pretty clear indication of the problem. For example, in the log snippet below the archive_command is failing repeatedly:

sh: /archive/ist5/arc/0000000400000028000000CD: No such file or directory
LOG:  archive command failed with exit code 1 (11263)
DETAIL: The failed archive command was: gzip < pg_wal/0000000400000028000000CD >/archive/ist5/arc/0000000400000028000000CD (14792)
PANIC:  could not write to file "pg_wal/waltemp.4111": No space left on device
LOG:  server process (PID 4111) was terminated by signal 6: Aborted (11206)
LOG:  terminating any other active server processes (11203)
LOG:  archiver process (PID 4103) exited with exit code 1 (11204)
LOG:  connection received: host=[local] (11213)
FATAL:  the database system is in recovery mode (11191)
LOG:  all server processes terminated; reinitializing (11210)
LOG:  database system was interrupted; last known up at 2017-10-04 01:15:41 GMT
LOG: database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 29/8D242168 (10126)
LOG:  redo done at 29/9BFFFF88 (10127)
LOG:  checkpoint starting: end-of-recovery immediate
PANIC:  could not write to file "pg_wal/waltemp.4920": No space left on device
LOG:  startup process (PID 4920) was terminated by signal 6: Aborted (11206)
LOG:  aborting startup due to startup process failure (11201)

Steps to resolve an archiving issue:

1. Thoroughly read the PostgreSQL documentation on how to set up archiving. The documentation has all the information required to properly configure archiving.
2. pg_wal holds WAL files for the archiver to process and will not delete them until they are archived. This results in a disk-full issue.

Once archiving is set up properly, start PostgreSQL (pg_ctl start -D data_directory) and PostgreSQL will automatically start catching up. All the WAL logs that are no longer required will be recycled, freeing up space in the pg_wal directory.

You can see this in the PostgreSQL log:

DETAIL: The failed archive command was: gzip pg_wal/00000004000000290000000A /archive/inst5/arc/00000004000000290000000A

gzip: stdout: No space left on device

LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 58 removed, 3 recycled; write=0.001 s, sync=0.000 s, total=0.170 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB

LOG: checkpoint starting: time

LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 113 removed, 1 recycled; write=0.000 s, sync=0.000 s, total=0.181 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=0 kB

2. Archiving is not in place or is working but pg_wal is still full

If you do not have archiving in place (which is not recommended for any transactional database) or archiving is working fine but you can see that pg_wal is full, try one of the following solutions.

First, set pg_hba.conf (by commenting out client connection entries or setting method=reject) so that connections cannot be made to the database while under maintenance. Remember to change it back when everything is back to normal.

A. Add more space to the partition.

The quickest way to give more space to PostgreSQL is by adding more space to the partition where the pg_wal directory is located. However, this is only a solution when your OS and file system are designed to allow resizing. If this is not possible, try option B below.

B. Move the pg_wal directory.

Move the pg_wal directory to another partition with more space while the database is shut down. Then create a symbolic link pointing to the new location and restart the database. This is a fast and effective way of getting the database up and running.

If needed, resize the original partition later and follow the steps to put pg_wal back to its original location.

Copy all WAL logs to the new WAL location (e.g. /home/pg_wal/):

cp -rf /var/lib/postgres/13/data/pg_wal/* /home/pg_wal/

If possible, and you have space at a different mount point, take a backup of pg_wal:

mv pg_wal /home/postgres/pg_wal_bkp

Since you now have all the WAL logs at the new location, create a symbolic link from the data directory to the new location:

ln -s /home/pg_wal/ /var/lib/postgres/13/data/pg_wal

Start PostgreSQL and check whether new WAL logs are being generated at the new location. If everything is working fine, you can safely delete the backup copy:

rm -rf /home/postgres/pg_wal_bkp

C. Use a pre-placed dummy file to free space.

Assuming you have previously followed the proactive approach and have an ONLY_DELETE_THIS_DUMMY_FILE_IN_A_POSTGRES_EMERGENCY file already in your pg_wal directory or on the same mount point, delete that file. This will free up some space in the pg_wal directory so you can bring up your database. In this way, you do not have to hunt for candidate WAL files to delete.

Do a checkpoint after bringing the database up. Ideally, the checkpoint should start to recycle unused WAL logs. Sometimes it happens that even after issuing a checkpoint, space will not be freed up. In that case, follow step D together with this step.

rm ONLY_DELETE_THIS_DUMMY_FILE_IN_A_POSTGRES_EMERGENCY
psql -d postgres
CHECKPOINT;

D. Remove old WAL segments with pg_archivecleanup.

If the above steps are not possible, this is the final option.

Your priority is to find the WAL log where the checkpoint is currently writing. You cannot use commands like pg_current_wal_lsn() because the database is not accessible. Instead, use the following command and look for the “Latest checkpoint’s REDO WAL file” (e.g. 000000010000000F00000026):

pg_controldata -D /database/inst5
Database system identifier:                   6465070279055498266
Database cluster state:                       in production
pg_control last modified:                     Wed 13 Sep 2017 12:07:38 EST
Latest checkpoint location:                   0/F826ACC8
Prior checkpoint location:                    0/F8266088
Latest checkpoint's REDO location:            0/F826ACC8
Latest checkpoint's REDO WAL file:            000000010000000F00000026

Where -D is the PGDATA directory path.

Now that you know the latest WAL file, you can safely remove all files older than 000000010000000F00000026 using the pg_archivecleanup command in the pg_wal directory. It is always safe to execute a dry run first (with the -n option) and then use the -d option to delete them:

pg_archivecleanup -n /home/transaction/inst5/transactionlog 000000010000000F00000026

pg_archivecleanup -d /home/transaction/inst5/transactionlog 000000010000000F00000026

Once deleted, you can start the database and you will notice that pg_wal has space back.

A proactive approach to avoid this situation

1. Maintain a proper backup of the cluster.
2. Perform proper and periodic database and database server health checks.
3. Back up pg_wal regularly and free up space. Set email alerts for disk space utilization at defined thresholds (70%, 90% disk full, etc.) and react immediately to avoid this situation.
4. Back up archives regularly. If archive storage size is a concern, use gzip to compress the archive logs:

archive_command = 'gzip < %p > /archive_dir/%f'

restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'

5. Make a dummy file of a specific size (e.g. 300 MB) in the pg_wal directory or anywhere else on the same disk mount point:

dd if=/dev/zero of=/database/inst5/pg_wal/ONLY_DELETE_THIS_DUMMY_FILE_IN_A_POSTGRES_EMERGENCY bs=1MB count=300

Sometimes this file is very handy when you need space back to get PostgreSQL running after a disk-full issue. There is no need to hunt for candidate WAL logs to delete. While having a dummy file is not mandatory, it is good to be able to free up a little workspace when you need it.

References

• How to Solve the Problem if pg_wal is Full

• Technology
• How-To

• Sql
• SysAdmin
• Troubleshooting