1/ Description:

I have 2 Centos machine: master, slave (streaming replication). Both of them are built on PostgreSQL 9.3 (image below) and configured failover event (failover PG 9.3 document).

  • Last WAL in master (pg_xlog): 0000000100000002000000D8

  • Last WAL in archive log folder in master: 0000000100000002000000D7

  • Last WAL in slave (pg_xlog): 0000000100000002000000D5 (3 WALs latency between slave and master) .

  • recovery.conf file in slave

    standby_mode = 'on' primary_conninfo = 'host=10.12.10.108 port=5444 user=enterprisedb password=123456 ' trigger_file = '/tmp/failover' restore_command = 'scp enterprisedb@10.12.10.108:/opt/archivelog/%f "%p"'

Now (3 WALs latency slave & master), the master fails (shutdown by manual) then the slave begin failover procedures..

2/ Questions:

  • Does slave miss 3 WALs(0000000100000002000000D6, 0000000100000002000000D7, 0000000100000002000000D8) or 1 WAL (0000000100000002000000D8) after failover ? Why ?
  • If I copy all WAL in pg_xlog from master to pg_xlog in slave . Does slave miss WAL after failover? Why ?
  • If I copy all WAL in pg_xlog from master to pg_xlog in slave (last WAL in slave: 0000000100000002000000D8 ) . How slave do a recovery when failover ?

enter image description here

EDIT: I am using pgpool-II 3.4 to control failover between master-slave (failover_command in pgpool.conf).

  • Is this a real setup? What happens when the host of the master goes completely down? In this case, it appears, you cannot get the missing WALs in any way, which means you cannot really fail over to the slave. – dezso Feb 3 '15 at 9:41
  • Sorry, my mistake, i am using pgpool-II 3.4 to control failover. I will edit my post. – Luan Huynh Feb 3 '15 at 9:43
  • This post is my test. As my thinking, we have all history of WAL in both pg_xlog & archive log folder in master (except crash disk). So when failover, slave will restore from master's archive log folder (i know that), but if we copy several WAL files from master.pg_xlog to slave.pg_xlog (latency case) , i do not know WAL files will restore or not when failover? – Luan Huynh Feb 3 '15 at 17:10
  • 1
    I tested with question 1 and slave miss 1 WAL (0000000100000002000000D8) because slave recover WAL in master's archive log folder and can not receive WAL streaming (master stopped). – Luan Huynh Feb 5 '15 at 2:17
  • With question 2 (if WAL is not crashed in master && latency = 3 WAL files), after copying WAL from master to slave, slave is started successfully because master.pg_xlog + master.archive_log save all WAL files and slave.pg_xlog is a part of them (i think) so when failover, slave will restore from master.archive_log (000...D5 -> 000.D7) & slave.pg_xlog (000...D8) which is copied from master.pg_xlog. (fix me if i am wrong-thanks) – Luan Huynh Feb 24 '15 at 2:27

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.