April 26, 2024
Summary: In this tutorial, we will learn how to move the pg_wal directory that stores WAL segments to a different disk or a faster disk.
Every committed transaction is WAL logged to ensure durability. This ensures that your PostgreSQL instance can perform crash recovery and avoid losing any of its committed transactions. PostgreSQL writes the entire content of each disk page to WAL, along with row-level changes, when full_page_writes is set to ON. This is important for safe crash recovery. However, this could write more data into WALs.
Writing WALs to the same disk as the data directory that contains tables/indexes may add I/O bottlenecks to a busy transactional database. Thus, it is always recommended to move the WAL’s directory to a new disk, if you have observed I/O waits in your server due to huge WAL generation (due to a lot of DMLs).
Getting ready
To move the WALs to a different directory, we must restart our PostgreSQL server. This may cause downtime, so it needs to be planned appropriately. Also, make sure that you plan for enough storage in the new pg_wal directory for storing all the WAL segments pending to be applied by a standby when replication slots are enabled. We must do this because, when replication slots are used, a WAL segment that has not been acknowledged as applied by standby will not be removed from the pg_wal directory of the master. This may accumulate several GBs of WAL segments if this goes unnoticed for a longer duration.
How to do it…
The following steps can be used to move pg_wal to a new location:
1. Create a new directory on a new disk and assign ownership to Postgres:
# mkdir -p /wals
# chown postgres:postgres /wals
2. Stop your PostgreSQL cluster, if it is already running:
$ pg_ctl -D $PGDATA stop -mf
3. If you would like to avoid more downtime due to the huge number of WALs, skip Step 2 and proceed to Step 3b. Otherwise, proceed to Step 3a:
3a. Move all the existing WALs and the archive_status directory inside pg_wal to the new directory on another disk. Make sure that pg_wal is empty and everything is moved to a new directory:
$ mv $PGDATA/pg_wal/* /wals
3b. Use rsync to avoid a huge downtime while copying a huge number of WAL segments to a different disk:
$ rsync -avzh $PGDATA/pg_wal/ /wals
$ pg_ctl -D $PGDATA stop -mf
$ rsync -avzh $PGDATA/pg_wal/ /wals
4. Create a symlink after removing the old WAL directory:
$ rmdir $PGDATA/pg_wal
$ ln -s /wals pg_wal
$ ls -alrth pg_wal
lrwxrwxrwx. 1 postgres postgres 5 Nov 10 00:16 pg_wal -> /wals
5. Start your PostgreSQL cluster now:
$ pg_ctl -D $PGDATA start
With that, you have successfully moved your WALs directory to another location.
How it works…
To move pg_wal, we must add a new disk to the server and create the new directory that the WAL segments will be stored in. We also need to make sure that we give appropriate permissions to the directory, as shown in Step 1. As this requires you to shut down the Postgres server to move the WAL directory, we could use a command similar to the one shown in Step 2 to shut down Postgres.
If you have a huge number of WAL segments, you can avoid more downtime by skipping this step and moving on to Step 3b. If not, you may use Step 3a, which simply moves all the content of the existing pg_wal directory to the new directory.
If you wish to avoid the huge downtime and wish to skip Steps 2 and 3a, you could simply use Step 3b, which uses rsync to copy all the existing WAL segments from pg_wal to the new WAL directory. Once done, we can simply shut down Postgres and use rsync again to copy the newly generated WAL segments. After moving all the WAL segments, remove the old WALs directory, and create a symbolic link to the new directory, as shown in step 4.
As we have seen, pg_wal is not removed from the data directory permanently. Instead, a symbolic link is created to the new directory that we are moving WALs to. Once the symlink has been created, we can start PostgreSQL, as shown in Step 5, which starts writing the newly generated WAL segments to the new WAL directory.
If you have a high availability cluster with 1 master and 1 or more standbys, you can perform these steps in a rolling fashion or all at once. Having different locations in each of the servers of a replication cluster is always possible. Thus, you can stop all the servers and perform these steps or perform them on one server after another.