< Back to blog home Fujitsu PostgreSQL blog

How to solve the problem if pg_wal is full

Rajni Baliyan | November 7, 2017

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; and at the same time 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. So 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 create a state where it can't 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:

Resolving the situation

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

The database won't 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 the archiving is functioning properly. Do this by ensuring the archive command is set correctly and the concerned 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 shown below the archive_command is failing repeatedly). The log will look similar to below:

Steps to resolve an archiving issue:

A. Thoroughly read the PostgreSQL documentation on how to set up archiving. This high-quality documentation has all the information required to properly set up archiving.

B. pg_wal holds WAL files for the archiver to archive and hence is not deleting them, this will result 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 not required will be recycled which will free up space in pg_wal directory.

You can see this is the PostgreSQL log as shown below:

*Note that, at this stage the database will not be available for normal operations.

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

Firstly, set pg_hba.conf (by commenting client connection entries or setting method=reject) so that connections cannot be made to the database whilst under maintenance. Don't forget 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 O/S and file system is designed to allow resizing. If this is not possible then try the 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 a new location and restart the database. This is a fast and effective way of getting the database up and running to resolve the problem.

If needed, resize the original partition later where the pg_wal directory was located and follow the steps to put pg_wal back to original location.

Copy all WAL logs to new WAL location (e.g. /home/transaction/inst5/transactionlog).

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

Since we have all the WAL logs at new location, create a symbolic link from pg_wal to the new location (/home/transaction/inst5/transactionlog).

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

C. A final way of getting space back to start PostgreSQL.

Assuming you have previously followed the proactive approach and have ONLY_DELETE_THIS_DUMMY_FILE_IN_A_POSTGRES_EMERGENCY file already in your pg_wal directory or on the same mount point.

Delete the abovementioned dummy file. This will free up some space in the pg_wal directory so you can bring up your database. In this way, we don't have to look for candidate WAL to be deleted to free up space (as we are doing in the next step).

Do a checkpoint after bringing it 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, you need to follow the next step together with this step.

Note: When everything is back to normal, don't forget to recreate this file in case you want to use it in future.

D. If the above steps are not possible then follow this final option.

Our priority is to find the WAL log where the checkpoint is writing currently. We can't use commands like pg_current_wal_lsn() as the database is not accessible at the moment. Instead, use the following command and look for Latest checkpoint's REDO WAL file (e.g. 000000010000000F00000026)

*where D is the PGDATA directory path.

Now we know the latest WAL so we can safely use it to remove all files older than WAL file name 000000010000000F00000026 using the pg_archivecleanup command in the pg_wal directory. It is always safe to execute a dry run (with option –n) first and then use
–d option to delete them later.

Once deleted, you can start the database and you will also notice that pg_wal gets space back.
Note: Once you have done all of the above, you should take a fresh backup of your cluster.

A proactive approach to avoid this situation:

1. Have a proper backup of the cluster.
   
   
2. Proper and periodic database and database server health check.
   
   
3. Backup pg_wal regularly and free up space. Set email alerts for disk space utilization according to set thresholds (70%, 90% disk full etc.) and react immediately to avoid this situation.
   
   
4. Backup archives regularly. If archive storage size is a concern, you can use gzip to compress the archive logs:
   
   
   archive_command = 'gzip < %p > /archive_dir/%f'

   * Note: You will then need to use gunzip during recovery, like below:

   restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'
5. Make a dummy file of some specific size (eg 300MB) using the following command in a pg_wal directory or anywhere else on the same disk mount point.