Moving PostgreSQL WAL file to a new drive on an Ubuntu server

Sometimes you may need to move PostgreSQL's WAL (write ahead log) file to a new drive. For example to gain better speed if you put it on an SSD or NVME drive, or to gain additional space.

 

First stop PostgreSQL service:

sudo service postgresql stop

Now make a new folder on the new drive, and move (or copy if have room) the pg_wal directory across:

mkdir -p /mnt/newdrive/pg_wal

su postgres

cp /var/lib/postgresql/11/main/pg_wal/* /mnt/newdrive/pg_wal/

Make a back up for now of the WAL directory:

mv /var/lib/postgresql/11/main/pg_wal   /var/lib/postgresql/11/main/pg_wal_bck

Create a link and start the DB again:

ln -s /mnt/newdrive/pg_wal /var/lib/postgresql/11/main/pg_wal

sudo service postgresql start

psql -U postgres -W

 >> should now ask for your password and you can log in

Any issues check the LOG file:

tail postgresql/postgresql-11-main.log -n 100

When you are happy it's all working then you can remove the WAL back up directory:

rmdir -r /var/lib/postgresql/11/main/pg_wal_bck

Notes:

any permission issues try chown to postgres and chmod 777 on the new dir

sudo chown postgres /mnt/newdrive/pg_wal

sudo chmod 777 /mnt/newdrive/pg_wal