<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<div name="messageBodySection">
<div dir="auto">Hello<br />
<br />
I have a setup with 1 primary and 2 async standby postgresql nodes behind 1 pgpool all running on a separate hosts, without watchdog.<br />
<br />
I believe I have done all the config required for pgpool to recognise that the standbys are connected to a single primary (for enabling the detach_false_primary feature) but it still doesn’t recognize it.<br />
<br />
Before getting into more details, I'll say that I have tried to pcp_detach_node and pcp_attach_node and also pcp_recovery_node in the hope that I was missing some step that would lead to pgpool not connecting to the standby properly but none of those helped.<br />
<br />
<strong>THE ERROR:</strong><br />
I see this log in pgpool:<br />
<br />
2021-07-01 17:07:44: pid 559: LOG: verify_backend_node_status: primary 2 does not connect to standby 0<br />
2021-07-01 17:07:44: pid 559: LOG: verify_backend_node_status: primary 2 does not connect to standby 1<br />
2021-07-01 17:07:44: pid 559: LOG: verify_backend_node_status: primary 2 owns only 0 standbys out of 2<br />
2021-07-01 17:08:34: pid 559: LOG: verify_backend_node_status: primary 2 does not connect to standby 0<br />
2021-07-01 17:08:34: pid 559: LOG: verify_backend_node_status: primary 2 does not connect to standby 1<br />
2021-07-01 17:08:34: pid 559: LOG: verify_backend_node_status: primary 2 owns only 0 standbys out of 2<br />
2021-07-01 17:09:25: pid 559: LOG: verify_backend_node_status: primary 2 does not connect to standby 0<br />
2021-07-01 17:09:25: pid 559: LOG: verify_backend_node_status: primary 2 does not connect to standby 1<br />
2021-07-01 17:09:25: pid 559: LOG: verify_backend_node_status: primary 2 owns only 0 standbys out of 2<br />
<br />
<strong>Relevant config from pgpool.conf:</strong><br />
# - Backend Connection Settings -<br />
<br />
backend_hostname0 = '<a href="http://dbod-ag-pg01.cern.ch" target="_blank">dbod-ag-pg01.ch</a>'<br />
backend_data_directory0 = '/ORA/dbs03/AG_PG01/data'<br />
backend_port0 = 6601<br />
backend_weight0 = 1<br />
backend_flag0 = 'ALLOW_TO_FAILOVER'<br />
backend_application_name0 = 'ag_pg01'<br />
<br />
backend_hostname1 = '<a href="http://dbod-ag-pg02.cern.ch" target="_blank">dbod-ag-pg02.ch</a>'<br />
backend_data_directory1 = '/ORA/dbs03/AG_PG02/data'<br />
backend_port1 = 6604<br />
backend_weight1 = 1<br />
backend_flag1 = 'ALLOW_TO_FAILOVER'<br />
backend_application_name1 = 'ag_pg02'<br />
<br />
backend_hostname2 = '<a href="http://dbod-ag-pg03.cern.ch" target="_blank">dbod-ag-pg03.ch</a>'<br />
backend_data_directory2 = '/ORA/dbs03/AG_PG03/data'<br />
backend_port2 = 6600<br />
backend_weight2 = 1<br />
backend_flag2 = 'ALLOW_TO_FAILOVER'<br />
backend_application_name2 = 'ag_pg03'<br />
<br />
<strong>Relevant config from postgresql.conf / myrecovery.conf (application name exists and is same in primary_conninfo as backend_application_name):</strong><br />
primary_conninfo = 'host=<a href="http://dbod-hac-c02.cern.ch" target="_blank">dbod-hac-c02.ch</a> port=6600 user=pgrepl application_name=ag_pg01 passfile=''/home/postgres/.pgpass'''<br />
<br />
<strong>"GRANT pg_monitor TO pgpool;” has been done:</strong><br />
postgres=# \dg<br />
List of roles<br />
Role name | Attributes | Member of<br />
-----------------+------------------------------------------------------------+--------------<br />
admin | Create role, Create DB | {}<br />
dod_dbmon | | {}<br />
dod_pmm | Superuser | {}<br />
pgpool | | {pg_monitor}<br />
pgpool_recovery | Superuser | {}<br />
pgrepl | Replication | {}<br />
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}<br />
<br />
<strong>Users for sr_check and health_check:</strong><br />
'sr_check_user': 'pgpool',<br />
'sr_check_database': 'postgres',<br />
'health_check_user': 'pgpool',<br />
'health_check_database': 'postgres',<br />
<br />
<strong>Versions:</strong><br />
PgPool - 4.2.2<br />
PostgreSQL - 12.6<br />
<br />
<strong>“show pool_nodes and show pool_health_check_stats” shows everything is fine:</strong><br />
postgres=# show pool_nodes;<br />
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state<br />
| replication_sync_state | last_status_change<br />
---------+----------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------<br />
+------------------------+---------------------<br />
0 | <a href="http://dbod-ag-pg01.cern.ch" target="_blank">dbod-ag-pg01.ch</a> | 6601 | up | 0.333333 | standby | 3 | true | 0 | streaming<br />
| async | 2021-07-01 17:37:42<br />
1 | <a href="http://dbod-ag-pg02.cern.ch" target="_blank">dbod-ag-pg02.ch</a> | 6604 | up | 0.333333 | standby | 0 | false | 0 | streaming<br />
| async | 2021-07-01 17:37:42<br />
2 | <a href="http://dbod-ag-pg03.cern.ch" target="_blank">dbod-ag-pg03.ch</a> | 6600 | up | 0.333333 | primary | 2 | false | 0 |<br />
| | 2021-07-01 16:51:00<br />
(3 rows)<br />
<br />
postgres=# SHOW POOL_HEALTH_CHECK_STATS;<br />
node_id | hostname | port | status | role | last_status_change | total_count | success_count | fail_count | skip_count | retry<br />
_count | average_retry_count | max_retry_count | max_duration | min_duration | average_duration | last_health_check | last_successful_health_<br />
check | last_skip_health_check | last_failed_health_check<br />
---------+----------------------+------+--------+---------+---------------------+-------------+---------------+------------+------------+------<br />
-------+---------------------+-----------------+--------------+--------------+------------------+---------------------+------------------------<br />
------+------------------------+--------------------------<br />
0 | <a href="http://dbod-ag-pg01.cern.ch" target="_blank">dbod-ag-pg01.ch</a> | 6601 | up | standby | 2021-07-01 17:37:42 | 2029 | 1998 | 0 | 31 | 0<br />
| 0.000000 | 0 | 1033 | 21 | 26.975475 | 2021-07-01 17:38:47 | 2021-07-01 17:38:47<br />
| 2021-07-01 16:52:00 |<br />
1 | <a href="http://dbod-ag-pg02.cern.ch" target="_blank">dbod-ag-pg02.ch</a> | 6604 | up | standby | 2021-07-01 17:37:42 | 2027 | 1850 | 1 | 176 | 5<br />
| 0.002701 | 5 | 20010 | 23 | 40.249595 | 2021-07-01 17:38:50 | 2021-07-01 17:38:50<br />
| 2021-07-01 16:55:53 | 2021-07-01 14:05:05<br />
2 | <a href="http://dbod-ag-pg03.cern.ch" target="_blank">dbod-ag-pg03.ch</a> | 6600 | up | primary | 2021-07-01 16:51:00 | 2024 | 2010 | 0 | 14 | 0<br />
| 0.000000 | 0 | 834 | 23 | 54.582090 | 2021-07-01 17:38:52 | 2021-07-01 17:38:52<br />
| 2021-07-01 16:38:07 |<br />
(3 rows)<br />
<br />
<strong>If I understand correctly, this is the query that pgpool runs on standbys to understand if they are talking to the current primary and the result of this query seems alright to me:</strong><br />
postgres=# select * from pg_stat_wal_receiver;<br />
pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | last_msg_send_time | last_msg_receipt_<br />
time | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port |<br />
conninfo<br />
<br />
------+-----------+-------------------+-------------------+--------------+--------------+-------------------------------+----------------------<br />
---------+----------------+-------------------------------+-----------+----------------------+-------------+-----------------------------------<br />
-----------------------------------------------------------------------------------------------------------------------------------------------<br />
--------------------------------------------------------------------------------<br />
1651 | streaming | 1/0 | 5 | 1/10009ED8 | 5 | 2021-07-01 19:41:12.659496+02 | 2021-07-01 19:41:12.6<br />
59872+02 | 1/10009ED8 | 2021-07-01 19:41:12.659496+02 | | <a href="http://dbod-hac-c02.cern.ch" target="_blank">dbod-hac-c02.ch</a> | 6600 | user=pgrepl passfile=/home/postgre<br />
s/.pgpass dbname=replication host=<a href="http://dbod-hac-c02.cern.ch" target="_blank">dbod-hac-c02.ch</a> port=6600 application_name=ag_pg01 fallback_application_name=walreceiver sslmode=prefer<br />
sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any<br />
(1 row)</div>
</div>
<div name="messageSignatureSection"><br />
<div class="matchFont">
<div dir="auto">Cheers!
<div dir="auto"><br /></div>
<div dir="auto">Anirudh</div>
</div>
</div>
</div>
</body>
</html>