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)
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)
host replication postgres 192.168.1.10/32 trust host replication postgres 192.168.1.20/32 trust
Once you have made the changes on the Master server, follow these steps on the Slave PC:
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.
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'
Now, if you need to fail over, you need to:
c:\vpop3\stopreplication.txt
on the Slave PCTo 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.