PostgreSQL – How to Change the Write Ahead Log Directory

postgresqlwrite-ahead-logging

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?

Best Answer

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.

Related Solutions

PostgreSQL Dump – How to Change Temporary Directory for pg_dump?

The temporary files are a side effect of the --format=tar option.

A backup in default plain format goes through without temporary tables in the local drive. Omit the option to produce a backup in plain format without temporary local files.

PostgreSQL – Fixing runuser Error on CentOS 7 for Postgres 9.4

The /usr/pgsql-9.4/bin/postgresql94-setup script needs to be executed as root.

It detects the data directory parsing the service file.

This is the full command help:

Usage: postgresql94-setup {initdb|upgrade} [SERVICE_NAME]

Script is aimed to help sysadmin with basic database cluster administration.

The SERVICE_NAME is used for selection of proper unit configuration file; For
more info and howto/when use this script please look at the docu file
$README_RPM_DIST.  The 'postgresql'
string is used when no SERVICE_NAME is explicitly passed.

Available operation mode:
  initdb        Create a new PostgreSQL database cluster.  This is usually the
                first action you perform after PostgreSQL server installation.
  upgrade   Upgrade PostgreSQL database cluster to be usable with new
                server.  Use this if you upgraded your PostgreSQL server to
                newer major version (currently from $PREVMAJORVERSION \
to $PGMAJORVERSION).

Environment:
  PGSETUP_INITDB_OPTIONS     Options carried by this variable are passed to
                             subsequent call of \`initdb\` binary (see man
                             initdb(1)).  This variable is used also during
                             'upgrade' mode because the new cluster is actually
                             re-initialized from the old one.
  PGSETUP_PGUPGRADE_OPTIONS  Options in this variable are passed next to the
                             subsequent call of \`pg_upgrade\`.  For more info
                             about possible options please look at man
                             pg_upgrade(1).
  PGSETUP_DEBUG              Set to '1' if you want to see debugging output.

Best Answer

Related Solutions

PostgreSQL Dump – How to Change Temporary Directory for pg_dump?

PostgreSQL – Fixing runuser Error on CentOS 7 for Postgres 9.4

Related Question