2

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?

1

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.

  • Thanks for the quick response. I was hoping to achieve this by changing a parameter in postgresql.conf . – Stelios Apr 12 '16 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 '16 at 10:13
  • Aye! A log switch or a simple rsync could probably do the job. – Stelios Apr 12 '16 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. – Juan Carlos Oropeza Mar 29 '17 at 18:43
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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