<div dir="ltr"><div><div><div><div><div><div>Hello,<br><br><div>I have setup pgpool (version
3.4) in master slave mode with 2 postgres databases (no load balancing)
using streaming replication:<br><br></div>backend_hostname0 = 'voldb1'<br>backend_port0 = 5432<br>backend_weight0 = 1<br>backend_data_directory0 = '/data01/postgres'<br>backend_flag0 = 'ALLOW_TO_FAILOVER'<br><br>backend_hostname1 = 'voldb2'<br>backend_port1 = 5432<br>backend_weight1 = 1<br>backend_data_directory1 = '/data01/postgres'<br>backend_flag1 = 'ALLOW_TO_FAILOVER'<br><br>connection_cache = on<br>load_balance_mode = off<br><br>master_slave_mode = on<br>master_slave_sub_mode = 'stream'<br><br>sr_check_period = 10<br><br>health_check_period = 40<br>health_check_timeout = 10<br>health_check_max_retries = 3<br>health_check_retry_delay = 1<br>connect_timeout = 10000<br><br>failover_command = '/usr/local/bin/pgpool_failover.sh %P %m %H'<br>failback_command = '/usr/local/bin/pgpool_failback.sh %d %P %m %H'<br>fail_over_on_backend_error = on<br>search_primary_node_timeout = 10<br><br><br></div>I was testing the following scenario:<br><br></div>1. Initial setup:<br></div> node_id | hostname | port | status | lb_weight | role<br>---------+---------------+------+--------+-----------+---------<br> 0 | voldb1.ls.cbn | 5432 | 2 | 0.500000 | primary<br> 1 | voldb2.ls.cbn | 5432 | 2 | 0.500000 | standby<br><br></div><br></div>2. I stopped postgres on voldb1 and voldb2 became the new primary so now I have this:<br> node_id | hostname | port | status | lb_weight | role<br>---------+---------------+------+--------+-----------+---------<br> 0 | voldb1.ls.cbn | 5432 | 3 | 0.500000 | standby<br> 1 | voldb2.ls.cbn | 5432 | 2 | 0.500000 | primary<br><br></div><div>3. I stopped postgres on voldb2 and there were no active masters attached to pgpool:<br></div><div><br>pcp_node_info 10 localhost 9898 cbn_cluster t000r 0<br>voldb1.ls.cbn 5432 3 0.500000<br><br>pcp_node_info 10 localhost 9898 cbn_cluster t000r 1<br>voldb2.ls.cbn 5432 3 0.500000<br><br></div><div>4. I started postgres on both voldb1 and voldb2. No changes as pgpool does not search for the nodes to reattach.<br> <br></div><div>5. I killed pgpool (to simulate a server crash, reboot): pkill -9 pgpool and I started pgpool again. Now this is the state I ended up with:<br> node_id | hostname | port | status | lb_weight | role<br>---------+---------------+------+--------+-----------+---------<br> 0 | voldb1.ls.cbn | 5432 | 2 | 0.500000 | primary<br> 1 | voldb2.ls.cbn | 5432 | 2 | 0.500000 | standby<br><br><br></div><div>So the old master became master again which is a big deal in my case, I can't afford to have any kind of data loss!!!<br><br></div><div>How is pgpool determining what server was master last? Is there a way I could overcome this issue? I thought I could update the pgpool status file in /var/log/pgpool in my scripts once a failover occurs. Would that be the way to go or there are better ways to fix this?<br></div><br><div><div><div><div><div><br><div><div>Thanks a lot,<br></div>Ioana Danes</div></div></div></div></div></div></div>