<div dir="ltr"><div>Tatsuo, </div><div dir="ltr">Thank you so much for the response to this. This config isn't working as we would expect though. (Answering your question: Yes, Patroni does a pg_rewind on any orphaned replicas that are still up.)<div><br></div><div>We get the behavior shown below. Node1 was former primary, we stopped that node, node 0 became the new primary, and Patroni executed pg_rewind on node1 and node2. In the show pool_nodes output, <b>the pg_role is correct, but the role is not</b>:</div><div><pre class="gmail-c-mrkdwn__pre" style="box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;font-variant-ligatures:none;line-height:1.50001;white-space:pre-wrap;word-break:normal;border-radius:4px;overflow-y:hidden;color:rgb(29,28,29);font-family:Monaco,Menlo,Consolas,"Courier New",monospace">postgres@mrkpgdb3:~$ psql -h mrkpgdb3 -p 5432 -c 'show pool_nodes;'
Pager usage is off.
node_id | hostname | port | status | pg_status | lb_weight | <b>role</b> | <b>pg_role</b> | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | mrkpgdb1 | 5433 | up | up | 0.333333 | standby | <b>primary</b> | 0 | true | 0 | | | 2023-04-11 09:02:18
1 | mrkpgdb2 | 5433 | up | up | 0.333333 | <b>primary</b> | standby | 0 | false | 0 | | | 2023-04-11 09:02:18
2 | mrkpgdb3 | 5433 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2023-04-11 09:02:18
(3 rows)</pre></div></div><div><br></div><div>Here is our PGPool configuration:</div><div><pre class="gmail-c-mrkdwn__pre" style="box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;font-variant-ligatures:none;line-height:1.50001;white-space:pre-wrap;word-break:normal;border-radius:4px;overflow-y:hidden;color:rgb(29,28,29);font-family:Monaco,Menlo,Consolas,"Courier New",monospace">root@mrkpgdb3:~# cat /etc/pgpool2/pgpool.conf
### On-Prem PGPool config parameters
# Static config parameters
listen_addresses = '*'
port = 5432
serialize_accept = on
num_init_children = 1048
max_pool = 1
listen_backlog_multiplier = 1
log_destination = 'syslog,stderr'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
pid_file_name = '/var/run/postgresql/pgpool.pid'
logdir = '/tmp'
connection_life_time = 300
connection_cache = OFF
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
child_life_time = 300
client_idle_limit = 0
memory_cache_enabled = off
sr_check_user = 'xxx'
sr_check_password = 'xxx'
sr_check_database = 'postgres'
sr_check_period = 1
delay_threshold = 1
log_standby_delay = 'if_over_threshold'
failover_on_backend_error = FALSE
allow_multiple_failover_requests_from_node = OFF
health_check_user = 'xxx'
health_check_password = 'xxx'
health_check_period = 1
health_check_timeout = 1
health_check_max_retries = 3
health_check_retry_delay = 1
connect_timeout = 800
master_slave_mode = on
master_slave_sub_mode = 'stream'
failover_command = ''
auto_failback = ON
detach_false_primary = ON
#failback_command = '/etc/pgpool2/failback.sh %d %h %p'
#follow_master_command = '/etc/pgpool2/follow_master.sh %d %h %p postgres'
recovery_user = 'xxx'
recovery_password = 'xxx'
#recovery_1st_stage_command = 'recovery_1st_stage.sh'
recovery_timeout = 5
ssl = OFF
ssl_cert = '/etc/pgpool2/server.crt'
ssl_key = '/etc/pgpool2/server.key'
ssl_prefer_server_ciphers = ON
ssl_ciphers = 'HIGH:!MEDIUM:!LOW:!SSLv2:!SSLv3:!TLSv1:!TLSv1.1:+TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA256:DHE-RSA-AES256-SHA::DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!EDH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA:!3DES'
use_watchdog = OFF
failover_when_quorum_exists = on
failover_require_consensus = on
#wd_port = 9000
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 15
heartbeat_destination_port0 = 9694
heartbeat_destination_port1 = 9694
other_wd_port0 = 9000
other_wd_port1 = 9000
other_pgpool_port0 = 5432
other_pgpool_port1 = 5432
#wd_escalation_command = '/etc/pgpool2/escalate.sh -e'
#wd_de_escalation_command = '/etc/pgpool2/escalate.sh -d'
if_cmd_path = '/etc/pgpool2'
if_up_cmd = 'ip addr add $_IP_$/24 dev ens3 label ens3:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev ens3'
arping_path = '/etc/pgpool2'
arping_cmd = 'arping -U $_IP_$ -w 1'
# Host and Cluster specific parameters
backend_hostname0 = 'mrkpgdb1'
backend_data_directory0 = '/var/lib/postgresql/14/main'
backend_port0 = 5433
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'mrkpgdb1'
backend_hostname1 = 'mrkpgdb2'
backend_data_directory1 = '/var/lib/postgresql/14/main'
backend_port1 = 5433
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'mrkpgdb2'
backend_hostname2 = 'mrkpgdb3'
backend_data_directory2 = '/var/lib/postgresql/14/main'
backend_port2 = 5433
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'mrkpgdb3'
load_balance_mode = ON
delegate_IP = '172.19.7.240'
wd_hostname = 'mrkpgdb3'
wd_priority = 3
heartbeat_destination0 = 'mrkpgdb1'
other_pgpool_hostname0 = 'mrkpgdb1'
heartbeat_destination1 = 'mrkpgdb2'
other_pgpool_hostname1 = 'mrkpgdb2'
trusted_servers = '172.19.7.1'</pre><pre class="gmail-c-mrkdwn__pre" style="box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;font-variant-ligatures:none;line-height:1.50001;white-space:pre-wrap;word-break:normal;border-radius:4px;overflow-y:hidden;color:rgb(29,28,29);font-family:Monaco,Menlo,Consolas,"Courier New",monospace">Here's the PGPool version:</pre><pre class="gmail-c-mrkdwn__pre" style="box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;font-size:12px;font-variant-ligatures:none;line-height:1.50001;white-space:pre-wrap;word-break:normal;border-radius:4px;overflow-y:hidden;color:rgb(29,28,29);font-family:Monaco,Menlo,Consolas,"Courier New",monospace"><pre class="gmail-c-mrkdwn__pre" style="box-sizing:inherit;margin-top:4px;margin-bottom:4px;padding:8px;line-height:1.50001;white-space:pre-wrap;word-break:normal;border-radius:4px;overflow-y:hidden;font-family:Monaco,Menlo,Consolas,"Courier New",monospace">postgres@mrkpgdb3:~$ pgpool -v
pgpool-II version 4.3.5 (tamahomeboshi)</pre><br></pre></div><div>Are there other parameters we need to unset in PGPool.conf?</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Apr 4, 2023 at 11:12 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>Hi,<br>
<br>
> We want to examine using PGPool as an intelligent proxy only, while letting<br>
> Patronic orchestrate failover/switcher and rewind/recovery.<br>
> <br>
> Obviously, our failover_command and other similar settings should just be<br>
> blank. What other settings should I make to allow PGPool to recognize that<br>
> the primary might have changed when PGPool is not executing that change?<br>
> <br>
> We've tried setting backend_flag0/1/2 = 'DISALLOW_TO_FAILOVER', but PGPool<br>
> did not recognize the change in primary.<br>
<br>
You should not set DISALLOW_TO_FAILOVER. Even if you want setup<br>
Pgpool-II as a proxy, you should not turn off failover because<br>
Pgpool-II recognizes the primary change at failover. You just need to<br>
disable failover_command and follow_primary_command. Also I recommend<br>
to disable failover_on_backend_error to avoid accidental failover.<br>
Thus followings are necessary set up:<br>
<br>
failover_command = ''<br>
follow_primary_command = ''<br>
failover_on_backend_error = off<br>
<br>
With this settings the actual operation looks like this (suppose we<br>
have 3 PostgreSQL servers).<br>
<br>
# The Initial state: node 0 is primary.<br>
<br>
$ pcp_node_info -w -p 11001<br>
localhost 11002 1 0.333333 waiting up primary primary 0 none none 2023-04-05 11:54:15<br>
localhost 11003 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15<br>
localhost 11004 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15<br>
<br>
# Stop the primary<br>
<br>
$ pg_ctl -D data0 stop<br>
waiting for server to shut down.... done<br>
server stopped<br>
<br>
# node 0 is surely down.<br>
<br>
$ pcp_node_info -w -p 11001<br>
localhost 11002 1 0.333333 waiting down primary unknown 0 none none 2023-04-05 11:54:15<br>
localhost 11003 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15<br>
localhost 11004 1 0.333333 waiting up standby standby 0 streaming async 2023-04-05 11:54:15<br>
<br>
# make node 1 to new primary.<br>
<br>
$ pg_ctl -D data1 promote<br>
waiting for server to promote.... done<br>
server promoted<br>
<br>
# As you can see, pgpool reconizes new primary.<br>
<br>
$ pcp_node_info -w -p 11001<br>
localhost 11002 3 0.333333 down down standby unknown 0 none none 2023-04-05 11:55:16<br>
localhost 11003 1 0.333333 waiting up primary primary 0 none none 2023-04-05 11:55:16<br>
localhost 11004 1 0.333333 waiting up standby standby 104 none none 2023-04-05 11:54:15<br>
<br>
Note that node 2 is not following new primary. Maybe Patroni takes care of this?<br>
<br>
Best reagards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS LLC<br>
English: <a href="http://www.sraoss.co.jp/index_en" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
Japanese:<a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
</div>
</blockquote></div><br clear="all"><div><br></div><span class="gmail_signature_prefix">-- </span><br><div dir="ltr" class="gmail_signature"><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><b><span style="font-size:11pt;font-family:Arial;vertical-align:baseline;white-space:pre-wrap"><font color="#434343">Dave Sisk</font></span></b></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><b><i><font color="#434343">Data Architect</font></i></b></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><b><i><font color="#434343"><br></font></i></b></p><p dir="ltr" style="line-height:1.38;margin-top:0pt;margin-bottom:0pt"><font color="#434343">T: </font></p></div></div>
<br>
<img src="https://storage.googleapis.com/emailfooter-all2022/BH_Logo_150x60_V1.png"><br><div><a href="https://www.linkedin.com/company/bullhorn" target="_blank"><img src="https://storage.googleapis.com/emailfooter-all2022/LinkedIn_30x30_V1.png" alt="https://www.linkedin.com/company/bullhorn"></a><a href="https://twitter.com/Bullhorn" target="_blank"><img src="https://storage.googleapis.com/emailfooter-all2022/Twitter_30x30_V1.png" alt="https://linktr.ee/bullhornglobal"></a><a href="https://www.youtube.com/@BULLHORNTV" target="_blank"><img src="https://storage.googleapis.com/emailfooter-all2022/Youtube_30x30_V1.png" alt="https://www.youtube.com/channel/UC-Rgm5YxtoxsvlCHy6KW3Ug"></a><br></div>