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= port=5444 user=enterprisedb password=123456 ' trigger_file = '/tmp/failover' restore_command = 'scp [email protected]:/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. Commented Feb 3, 2015 at 9:41
  • Sorry, my mistake, i am using pgpool-II 3.4 to control failover. I will edit my post.
    – Luan Huynh
    Commented Feb 3, 2015 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
    Commented Feb 3, 2015 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
    Commented Feb 5, 2015 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
    Commented Feb 24, 2015 at 2:27


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.