I am trying an new configuration and was wondering if it is valid. We have three PostgreSQL servers (DB1, DB2 and DB3). DB1 is currently the master with DB2 and DB3 setup as hot_standby using streaming WAL. DB2 is the backup in case DB1 fails. A virtual IP managed by Pacemaker moves between DB1 and DB2 depending on which is running and is Master. DB3 is used for read only queries for reporting. Its recovery.conf file points to the virtual IP so that in theory, it will connect to whichever server (DB1 or DB2) is active/master.
I got the initial WAL replication going between DB1 and DB2 and between the virtual IP (currently on DB1) and DB3. So far all is well. However, If I simulate a failover to DB2 (promote DB2 and move the virtual IP to it), WAL does not resume between the virtual IP and DB3. I tried restarting PostgreSQL on DB2, but that did not help. I also tried restarting PostgreSQL on DB2 to see if that would kick start it, but it did not. The only way I could get WAL between the Virutal IP and DB3 is to do a manual sync using rsync.
I have seen claims of similar configurations working with three servers in replication and having the third server re-establishing streaming WAL with a server after failure of the previous master. However, not much said on the configuration.
Is what I am trying to do possible?
Thanks,
Keith
