5

How do you change the defined WAL directory? During the installation I defined a directory, /var/lib/wal and the $PGDATA/pg_xlog has a symbolic link pointing to that directory.

How do I change the postgres setting to write the WAL files elsewhere - without having to re-install?

2 Answers 2

4

This can be done according to the documentation [here]:(http://www.postgresql.org/docs/9.1/static/wal-internals.html)

It is advantageous if the log is located on a different disk from the main database files. This can be achieved by moving the pg_xlog directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

5
  • Thanks for the quick response. I was hoping to achieve this by changing a parameter in postgresql.conf .
    – Stelios
    Apr 12, 2016 at 9:51
  • 1
    Fraid not :-( - it's quite an operation to change the WAL directory - if you think about it, it's easy to see why. With buffering, you never know if there are any transactions in limbo hanging around before changing your WAL location - changing on a live system could leave your system in an inconsistent state. Maybe a feature request (change WAL without bouncing the server)? I don't see why it couldn't be possible if they wished to - I mean hot backups are possible, so why not a hot WAL location change? I'd say it's more a question of a (competent dev) sitting down than a theoretical no-no.
    – Vérace
    Apr 12, 2016 at 10:13
  • Aye! A log switch or a simple rsync could probably do the job.
    – Stelios
    Apr 12, 2016 at 11:00
  • I came here after reading the documentation, because not sure how do the moving. Maybe is obvious but still don't see it. Mar 29, 2017 at 18:43
  • Wow! I didn't know it's recommended, just was googling if this even legal to do so. Nice. Aug 3, 2020 at 23:56
1

It is very-very simple:

  1. Make sure your postgres is not running!
  2. mount your WAL disk to an easy accessible folder
  3. check the permissions of your WAL directory ($PG_DATA/pg_xlog/)
  4. copy or move all files from $PG_DATA/pg_xlog/ into this directory
  5. unmount the WAL disk and remount it to $PG_DATA/pg_xlog/
  6. Check the permissions and fix if necessary (of the directory and files inside)
  7. Start postgres again ^^

You are done!

1
  • 2
    Seems like the folder is called pg_wal now, not pg_xlog. Please check your data directory and configuration files! Dec 23, 2020 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.