PostgreSQL 9.1 (used in VPOP3 5 and later) supports replication from a 'Master' to a 'Slave' database server. This can be used with VPOP3 for having a near-realtime backup of your settings & messages.

To use the replication, you need to have a file share which is accessible from both PCs. The thing to watch out for is that the PostgreSQL services may be running as a different user from the currently logged in user, so make sure that the user(s) which the PostgreSQL services are running as have full access to the network share. The network share can be on either of the computers involved in the replication, or on a totally different PC.

For the sake of this article, we will have a Master PC, on IP address 192.168.1.10, a Slave PC on IP address 192.168.1.20, and a file share at \\192.168.1.10\pgsql\db_replication (note that you need to access the share using the UNC path, mapped drives will not work). VPOP3 is installed in c:\vpop3 on both PCs

Both the Master and Slave PCs should have VPOP3 installed into the same location (eg c:\vpop3 or d:\vpop3, etc), or settings relating to paths will not work if you have to fail over to the Slave PC. Alternatively, you could use the Command Line Settings Tool to change the paths as part of your fail over procedure.

(We can help set this up database replication for you if you wish, but this will be chargeable - the current rate is £150 + VAT for a simple setup (ie replicating over a LAN with file sharing available between the two PCs). If you have a more complicated setup (eg replicating over the Internet) this can be more complex as it can involve configuring VPNs etc, so the cost may be greater)

• In the VPOP3\pgsql\data folder, find the file postgresql.conf and edit it using Notepad or another text editor. At the bottom of the file add the lines:

wal_level = hot_standby
archive_mode = on
archive_command = 'copy %p \\\\192.168.1.10\\pgsql\\db_replication\\%f'
max_wal_senders = 5
wal_keep_segments = 128
listen_addresses = '*'
hot_standby = on

(Note that the archive_command has doubled up the \ characters)

• Edit the file pg_hba.conf using Notepad or another text editor, and add the lines:

host replication postgres 192.168.1.10/32 trust
host replication postgres 192.168.1.20/32 trust

• Restart the VPOP3 and VPOP3DB services to load this new configuration

• You should see files starting to appear in the share directory as the master starts copying its transaction log files there

• Make sure that TCP port 5433 is allowed through any firewall on the master PC

Once you have made the changes on the Master server, follow these steps on the Slave PC:

• Install the same version of VPOP3 as on the master PC
• Stop the VPOP3 service, and Disable it
• Stop the VPOP3DB service (do not disable it)
• Rename the c:\vpop3\pgsql\data folder

• Go to a command prompt in the VPOP3\pgsql\bin folder and run

pg_basebackup -D c:/vpop3/pgsql/data -P -v -p 5433 -x -h 192.168.1.10 -U postgres This will make a copy of the database folder on the slave PC. This does a full copy of the database folder, so includes all the settings changes made to the Master PC configuration above.

• Using Notepad or another text editor, make a file called Recovery.conf in the VPOP3\pgsql\data folder. The presence of this file tells this installation of PostgreSQL that it is a slave, and how it should contact the master server to replicate the data. Make this file contain the following contents:

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.10 port=5433 user=postgres'
trigger_file = 'c:\\vpop3\\stopreplication.txt'
restore_command = 'copy \\\\192.168.1.10\\pgsql\\db_replication\\%f %p'
archive_cleanup_command = 'c:\\vpop3\\pgsql\\bin\\pg_archivecleanup \\\\192.168.1.10\\pgsql\\db_replication %r'

• Start the VPOP3DB service on the Standby PC

Now, if you need to fail over, you need to:

1. ensure that the VPOP3 and VPOP3DB services are stopped on the Master PC
2. copy any file (contents don't matter) to a file called c:\vpop3\stopreplication.txt on the Slave PC
3. Enable and start the VPOP3 service on the Slave PC
4. Either change the IP address on the Slave PC to match that which was used on the Master PC (ensuring there is no conflict), or change your local DNS server to point email clients at the new IP address, or change all the email account settings in your email clients to point to the Slave PC's IP address.

To start replicating back to the ex-master, you need to do the above steps (most will already have been carried out) - ie do a new 'base backup' copy, and create the recovery.conf file on the ex-master PC. To make the ex-Master back to the Master, you will need to replicate back to the ex-Master, then do the fail-over again, once the replication has caught up

On the Master PC, run 'psql' in the VPOP3 folder (password 'vpop3pass'), then type:

select pg_current_xlog_location();

That will give some numbers (in hexadecimal). This shows the current 'transaction ID'

On the Slave PC, run 'psql' in the VPOP3 folder (password 'vpop3pass'), then type:

select pg_last_xlog_replay_location();

That will give some numbers in hexadecimal as well. This shows how far the Slave PC has got with 'replaying' the transactions that occurred on the Master.

The difference between the two numbers shows how far behind the Slave is from the Master PC. Usually, as long as the Slave PC's counter is increasing, and is not too far behind the Master PC's counter, then everything is OK.