<div dir="ltr"><div>Thanks Bo, that help me a lot.</div><div><br></div><div>I have a similar setup as described in <a href="https://www.pgpool.net/docs/42/en/html/example-cluster.html">https://www.pgpool.net/docs/42/en/html/example-cluster.html</a> (only with 2 node), but seems that the pgpool master can only talk with the postgres that is on the same machine. So if MASTER is not in the writing node I cannot write. <br></div><div>I think it is because the machines now have more than one network interface and probably something is not binding correctly.</div><div><br></div><div>Here is some info:<br></div><div><br></div><div>pgpool-II successfully started. version 4.1.5 (karasukiboshi)</div><div><br></div><div>...<br><br>-bash-4.2$ psql -h 10.230.4.149 -p 9999 -U pgpool postgres -c "show pool_nodes"<br> node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_c<br>hange<br>---------+--------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------<br>-------<br> 0 | <a href="http://qs-audit-trail-01.dc.me">qs-audit-trail-01.dc.me</a> | 5432 | up | 0.500000 | primary | 0 | false | 0 | | | 2021-01-25 18<br>:12:05<br> 1 | <a href="http://qs-audit-trail-02.dc.me">qs-audit-trail-02.dc.me</a> | 5432 | up | 0.500000 | standby | 0 | true | 0 | streaming | async | 2021-01-25 18<br>:12:05<br>(2 rows)</div><div><br></div><div>and</div><div><br></div><div>-bash-4.2$ pcp_watchdog_info -w -h 10.230.4.149 -p 9898 -U pgpool --verbose<br>Watchdog Cluster Information<br>Total Nodes : 2<br>Remote Nodes : 1<br>Quorum state : QUORUM EXIST<br>Alive Remote Nodes : 1<br>VIP up on local node : YES<br>Master Node Name : <a href="http://qs-audit-trail-01.dc.me:9999">qs-audit-trail-01.dc.me:9999</a> Linux qs-audit-trail-01<br>Master Host Name : <a href="http://qs-audit-trail-01.dc.me">qs-audit-trail-01.dc.me</a><br><br>Watchdog Node Information<br>Node Name : <a href="http://qs-audit-trail-01.dc.me:9999">qs-audit-trail-01.dc.me:9999</a> Linux qs-audit-trail-01<br>Host Name : <a href="http://qs-audit-trail-01.dc.me">qs-audit-trail-01.dc.me</a><br>Delegate IP : 10.230.4.149<br>Pgpool port : 9999<br>Watchdog port : 9000<br>Node priority : 1<br>Status : 4<br>Status Name : MASTER<br><br>Node Name : <a href="http://qs-audit-trail-02.dc.me:9999">qs-audit-trail-02.dc.me:9999</a> Linux qs-audit-trail-02<br>Host Name : <a href="http://qs-audit-trail-02.dc.me">qs-audit-trail-02.dc.me</a><br>Delegate IP : 10.230.4.149<br>Pgpool port : 9999<br>Watchdog port : 9000<br>Node priority : 1<br>Status : 7<br>Status Name : STANDBY</div><div><br></div><div>pgpool.conf:</div><div><br></div><div># - Backend Connection Settings -<br><br>backend_hostname0 = '<a href="http://qs-audit-trail-01.dc.me">qs-audit-trail-01.dc.me</a>'<br>backend_port0 = 5432<br>backend_weight0 = 1<br>backend_data_directory0 = '/export/pgsql/data'<br>backend_flag0 = 'ALLOW_TO_FAILOVER'<br>backend_application_name0 = '<a href="http://qs-audit-trail-01.dc.me">qs-audit-trail-01.dc.me</a>'<br><br>backend_hostname1 = '<a href="http://qs-audit-trail-02.dc.me">qs-audit-trail-02.dc.me</a>'<br>backend_port1 = 5432<br>backend_weight1 = 1<br>backend_data_directory1 = '/export/pgsql/data'<br>backend_flag1 = 'ALLOW_TO_FAILOVER'<br>backend_application_name1 = '<a href="http://qs-audit-trail-02.dc.me">qs-audit-trail-02.dc.me</a>'</div><div><br></div><div><br></div><div>Any hints to figure out the problem? Everything seems to be working out except this.</div><div><br></div><div>
Regards,</div><div>LA<br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jan 21, 2021 at 12:41 AM Bo Peng <<a href="mailto:pengbo@sraoss.co.jp">pengbo@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">Hi,<br>
<br>
On Tue, 19 Jan 2021 15:02:05 +0000<br>
Luís Alves <<a href="mailto:luisalves00@gmail.com" target="_blank">luisalves00@gmail.com</a>> wrote:<br>
<br>
> Hi<br>
> <br>
> Mu cluster have 2 nodes and currently I'm getting:<br>
> <br>
> find_primary_node: standby node is 1<br>
> find_primary_node: standby node is 0<br>
<br>
It seems that Pgpool-II can't detect the primary node.<br>
Pgpool executes "SELECT pg_is_in_recovery()" on each backend node to find the primary node.<br>
<br>
> I'm trying to issue detach or promote commands but I get this:<br>
> <br>
> -bash-4.2$ pcp_detach_node -w -h 10.230.4.148 -p 9898 -U pgpool -g -n 1<br>
> ERROR: connection to host "10.230.4.148" failed with error "Connection<br>
> refused"<br>
> -bash-4.2$ pcp_promote_node -w -h 10.230.4.148 -p 9898 -U pgpool -g -n 0<br>
> ERROR: connection to host "10.230.4.148" failed with error "Connection<br>
> refused"<br>
<br>
The pcp commands failed.<br>
Please make sure the hostname "10.230.4.148" and port "9898" is specified correctly.<br>
<br>
> Any idea how I can mark one as primary?<br>
> <br>
> I'm running with the -D flag: /usr/bin/pgpool -f /etc/pgpool-II/pgpool.conf<br>
> -D -n<br>
<br>
Please note that "pcp_promote_node" just changes the internal status of Pgpool-II <br>
and it does not actually promote PostgreSQL standby server. <br>
<br>
You need to connect to backend directly to promote the standby <br>
as primary in the following steps:<br>
<br>
1. detach node0 using "pcp_detach_node"<br>
2. promote node0 using "pg_ctl promote ..."<br>
3. attach node0 using "pcp_attach_node"<br>
<br>
-- <br>
Bo Peng <<a href="mailto:pengbo@sraoss.co.jp" target="_blank">pengbo@sraoss.co.jp</a>><br>
SRA OSS, Inc. Japan<br>
</blockquote></div>