Setting Up Archiving of transaction logs (WAL) on the PostgreSQL on Linux server

To set up WAL (Write Ahead Log) we first need to understand the following:

Need: To archive the transaction logs before they are overwritten by Postgre

Mechanism: System physically divides the long sequence of WAL records into small WAL segment files. And now before the file segments are overwritten they are archived (by any OS utility)


We set WAL level to enable WAL archiving to archive_mode or hot_standby. We edit the postgresql.conf:

Edit postgresql.conf

For the standby server, you only need to change one setting in this file. Follow these steps:

Edit the file. In the terminal for the standby server, enter the following command:

$ nano ../../etc/postgresql/9.3/main/postgresql.conf

In the REPLICATION section, in the Standby Servers section, turn on Hot Standby and uncomment the line:

hot_standby = on

Save and close the file.

Create the recovery configuration file

When you implement a server in Hot Standby mode, you must supply a configuration file that contains the settings that will be used in the event of data recovery. This file is named recovery.conf. To add this file to the standby server, follow these steps:

Copy the sample recovery file to the proper location. In the terminal for the standby server, enter the following command:

$ cp -avr ../../usr/share/postgresql/9.3/recovery.conf.sample /../../var/lib/postgresql/9.3/main/recovery.conf

Edit the recovery file:

$ nano /../../var/lib/postgresql/9.3/main/recovery.conf

In the STANDBY SERVER PARAMETERS section, change the standby mode:

standby_mode = on

Set the connection string to the primary server. Replace with the external IP address of the primary server. Replace with the password for the user named repuser.

primary_conninfo = 'host= port=5432 user=repuser password='

(Optional) Set the trigger file location:

trigger_file = '/tmp/postgresql.trigger.5432'

The trigger_file path that you specify is the location where you can add a file when you want the system to fail over to the standby server. The presence of the file "triggers" the failover. Alternatively, you can use the pg_ctl promote command to trigger failover.

Save and close the file.

Start the standby server

You now have everything in place and are ready to bring up the standby server. In the terminal for the standby server, enter the following command:

$ service postgresql start



In archive_command, %p is replaced by the path name of the file to archive, while %f is replaced by only the file name. (The path name is relative to the current working directory, i.e., the cluster's data directory.) Use %% if you need to embed an actual % character in the command. The simplest useful command is something like:

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix

archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows