Where is the postgresql wal located? How can I specify a different path?

The WAL files are written to the directory pg_xlog inside of the data directory. Starting with Postgres 10, this directory was renamed to pg_wal

E.g. /var/lib/postgresql/10/main/pg_wal

See the manual for details:

  • http://www.postgresql.org/docs/9.1/static/wal-configuration.html
  • http://www.postgresql.org/docs/current/static/wal-configuration.html

If I'm not mistaken, this directory name can not be changed. But it can be a symbolic link that points to a different disk.

As a matter of fact this is actually recommended to tune WAL performance (See here: http://wiki.postgresql.org/wiki/Installation_and_Administration_Best_practices#WAL_Directory)


To Copy the WAL Directory to another file path/disk drive, follow these steps below:

Descriptive Steps

  1. Turn off Postgres to protect against corruption
  2. Copy WAL directory (by default on Ubuntu - /var/lib/postgresql/<version>/main/pg_wal) to new file path using rsync. It will preserve file/folder permissions and folder structure with the -a flag. You should leave off the training slash.
  3. Verify the contents copied correctly
  4. Rename pg_wal to pg_wal-backup in the Postgres data directory ($PG_DATA)
  5. Create a symbolic link to the new path to pg_wal in the Postgres data directory ($PG_DATA) and update the permissions of the symbolic link to be the postgres user
  6. Start Postgres and verify that you can connect to the database
  7. Optionally, delete the pg_wal-backup directory in the Postgres data directory ($PG_DATA)

Matching Commands

sudo service postgresql stop

sudo rsync -av /var/lib/postgresql/12/main/pg_wal /<new_path>
ls -la /<new_path>

sudo mv /var/lib/postgresql/12/main/pg_wal /var/lib/postgresql/12/main/pg_wal-backup
sudo ln -s /<new_path> /var/lib/postgresql/12/main/pg_wal
sudo chown -h postgres:postgres /var/lib/postgresql/12/main/pg_wal

sudo service postgresql start && sudo service postgresql status
# Verify DB connection using your db credentials/information
psql -h localhost -U postgres -p 5432

rm -rf /var/lib/postgresql/12/main/pg_wal-backup

Tags:

Postgresql

Recent Posts