pgpool is redirecting the calls to the lagging DB

[dibyanduroy]

postgres on node3 was down for few days as due to one of the maintenance activity.
When we bring the node up, it shows node3 is lagging

patronictl list

postgres@node1:~$ patronictl list
+ Cluster: pg_cluster (7469643466018483368) ----+-----------+----+-----------+---------------------+
| Member         | Host          | Role         | State     | TL | Lag in MB | Tags                |
+----------------+---------------+--------------+-----------+----+-----------+---------------------+
| node1          |  192.168.0.11 | Leader       | running   | 35 |           |                     |
+----------------+---------------+--------------+-----------+----+-----------+---------------------+
| node2          |  192.168.0.12 | Sync Standby | streaming | 35 |         0 |                     |
+----------------+---------------+--------------+-----------+----+-----------+---------------------+
| node3          |  192.168.0.13 | Replica      | running   | 31 |     67060 |                     |
+----------------+---------------+--------------+-----------+----+-----------+---------------------+

only node2 is showing as sync

select * from pg_stat_replication;

postgres@node1:~$ psql -h 192.168.0.10 -p 9999 -c 'select * from pg_stat_replication;'
Password for user postgres:
Timing is on.
   pid   | usesysid | usename | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |   sent_lsn   |  write_lsn   |  flush_lsn   |  replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
---------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+--------------+--------------+--------------+--------------+-----------+-----------+------------+---------------+------------+-------------------------------
 2585905 |  2747291 | repl    | node2            |  192.168.0.12 |                 |       40546 | 2025-03-09 23:10:42.346017-07 |              | streaming | 108/EACE97F8 | 108/EACE97F8 | 108/EACE97F8 | 108/EACE97F8 |           |           |            |             1 | sync       | 2025-03-10 02:04:09.55529-07
(1 rows)

Time: 2.807 ms

query counts for each node

postgres@node1:~$ psql -h 192.168.0.10 -p 9999 -c 'show pool_nodes'
Password for user postgres:
Timing is on.
 node_id |            hostname             | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+---------------------------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | node1.example.com               | 5432 | up     | up        | 0.333333  | primary | primary | 0          | false             | 0                 |                   |                        | 2025-03-10 02:04:40
 1       | node2.example.com               | 5432 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0.000000 second   |                   |                        | 2025-03-10 02:04:40
 2       | node3.example.com               | 5432 | up     | up        | 0.333333  | standby | standby | 0          | true              | 0.000000 second   |                   |                        | 2025-03-10 02:04:40
(3 rows)

Time: 56.201 ms
postgres@node1:~$ psql -h 192.168.0.10 -p 9999 -c 'show pool_backend_stats'
Password for user postgres:
Timing is on.
 node_id |            hostname             | port | status |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | panic_cnt | fatal_cnt | error_cnt
---------+---------------------------------+------+--------+---------+------------+------------+------------+------------+---------+-----------+-----------+-----------+-----------
 0       | node1.example.com               | 5432 | up     | primary | 0          | 0          | 0          | 0          | 0       | 3         | 0         | 0         | 1
 1       | node2.example.com               | 5432 | up     | standby | 0          | 0          | 0          | 0          | 0       | 0         | 0         | 0         | 0
 2       | node3.example.com               | 5432 | up     | standby | 0          | 0          | 0          | 0          | 0       | 1         | 0         | 0         | 0
(3 rows)

Time: 0.494 ms

pcp_node_info shows both secondary node

postgres@node1:~$ pcp_node_info
node1.example.com 5432 2 0.333333 up up primary primary 0 none none 2025-03-10 02:04:40
node2.example.com 5432 2 0.333333 up up standby standby 0.000000 none none 2025-03-10 02:04:40
node3.example.com 5432 2 0.333333 up up standby standby 0.000000 none none 2025-03-10 02:04:40

pgbench query

pgbench -C -n -h 192.168.0.10 -p 9999 -c 10 -S -T 30 pgbench_test
post pgbench test it shows that calls are getting redirected to 3rd node as well.
If the 3rd node is not available in the pg_stat_replication, how pgpool is sending the calls to 3rd node?
if the 3rd node is delayed in replication, the call should not goes to 3rd node

postgres@node1:~$ psql -h 192.168.0.10 -p 9999 -c 'show pool_nodes'
Password for user postgres:
Timing is on.
 node_id |            hostname             | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+---------------------------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | node1.example.com               | 5432 | up     | up        | 0.333333  | primary | primary | 1649       | false             | 0                 |                   |                        | 2025-03-10 02:04:40
 1       | node2.example.com               | 5432 | up     | up        | 0.333333  | standby | standby | 1636       | true              | 0.000000 second   |                   |                        | 2025-03-10 02:04:40
 2       | node3.example.com               | 5432 | up     | up        | 0.333333  | standby | standby | 1584       | false             | 0.000000 second   |                   |                        | 2025-03-10 02:04:40
(3 rows)

Time: 53.823 ms
postgres@node1:~$ psql -h 192.168.0.10 -p 9999 -c 'show pool_backend_stats'
Password for user postgres:
Timing is on.
 node_id |            hostname             | port | status |  role   | select_cnt | insert_cnt | update_cnt | delete_cnt | ddl_cnt | other_cnt | panic_cnt | fatal_cnt | error_cnt
---------+---------------------------------+------+--------+---------+------------+------------+------------+------------+---------+-----------+-----------+-----------+-----------
 0       | node1.example.com               | 5432 | up     | primary | 1649       | 0          | 0          | 0          | 0       | 4857      | 0         | 0         | 1
 1       | node2.example.com               | 5432 | up     | standby | 1636       | 0          | 0          | 0          | 0       | 1638      | 0         | 0         | 0
 2       | node3.example.com               | 5432 | up     | standby | 1584       | 0          | 0          | 0          | 0       | 1583      | 0         | 0         | 0
(3 rows)

Time: 0.108 ms

application is showing wrong data as the node3 is uptodate. we need to detach the node manually.

postgres@node1:~$ pcp_node_info
node1.example.com 5432 2 0.333333 up up primary primary 0 none none 2025-03-10 02:04:40
node2.example.com 5432 2 0.333333 up up standby standby 0.000000 none none 2025-03-10 02:04:40
node3.example.com 5432 2 0.333333 up up standby standby 0.000000 none none 2025-03-10 02:04:40
postgres@node1:~$ pcp_detach_node -n2
pcp_detach_node -- Command Successful
postgres@node1:~$ pcp_node_info
node1.example.com 5432 2 0.333333 up up primary primary 0 none none 2025-03-10 02:04:40
node2.example.com 5432 2 0.333333 up up standby standby 0.000000 none none 2025-03-10 02:04:40
node3.example.com 5432 3 0.333333 down up standby standby 0.000000 none none 2025-03-10 02:29:40

here are the pgpool config

postgres@node1:/etc/pgpool2$ grep -v "^\s*#" pgpool.conf | grep -v "^\s*$"
backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
listen_backlog_multiplier = 3
backend_hostname0 = 'node1.example.com'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/opt/postgresql/14/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'node1.example.com'
backend_hostname1 = 'node2.example.com'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/postgresql/14/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'node2.example.com'
backend_hostname2 = 'node3.example.com'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/opt/postgresql/14/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'node3.example.com'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 1min
allow_clear_text_frontend_auth = on
ssl = on
ssl_key = '/etc/ssl/private/proddb.example.com.key'
ssl_cert = '/etc/ssl/certs/proddb.example.com.crt'
process_management_mode = dynamic
process_management_strategy = gentle
num_init_children = 1500
min_spare_children = 200
max_spare_children = 700
max_pool = 4
connection_life_time = 10
client_idle_limit = 0
log_connections = on
log_disconnections = on
log_error_verbosity = verbose          # terse, default, or verbose messages
logging_collector = on
log_directory = '/var/log/pgpool'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 200MB
connection_cache = on
load_balance_mode = on
sr_check_period = 10
sr_check_user = 'pgpool'
sr_check_password = 'pgpool'
sr_check_database = 'postgres'
delay_threshold_by_time = 100
follow_primary_command = 'echo "follow_primary_command started"'
health_check_period = 3
health_check_timeout = 5
health_check_user = 'pgpool'
health_check_password = 'pgpool'
health_check_max_retries = 2
health_check_retry_delay = 1
connect_timeout = 5000
failover_command = 'echo "failover_command started"'
failback_command = 'echo "failback_command started"'
failover_on_backend_error = on
failover_on_backend_shutdown = on
detach_false_primary = on
search_primary_node_timeout = 30
auto_failback = on
use_watchdog = on
hostname0 = 'node1.example.com'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'node2.example.com'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'node3.example.com'
wd_port2 = 9000
pgpool_port2 = 9999
delegate_ip = '192.168.0.10'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
wd_escalation_command = '/etc/pgpool2/escalation.sh'
enable_consensus_with_half_votes = on
wd_remove_shutdown_nodes = on
wd_lost_node_removal_timeout = 5s
wd_no_show_node_removal_timeout = 5s
wd_lifecheck_method = 'heartbeat'
wd_interval = 1
heartbeat_hostname0 = 'node1.example.com'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'node2.example.com'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'node3.example.com'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 10

so I see two problems:

1. Even if the node3 is up, its lagging, how to restrict pgpool to not send calls to lagging node?
2. Is this related to show pool_nodes not showing the values for the replication_state, replication_sync_state columns? if yes, how to fix it?

[tatsuo-ishii]

Even if the node3 is up, its lagging, how to restrict pgpool to not send calls to lagging node?

It is likely there's something wrong in your settings.

Is this related to show pool_nodes not showing the values for the replication_state, replication_sync_state columns?

Yes.

if yes, how to fix it?

Check sr_check_user privilege. It needs to be superuser or belongs to "pg_monitor" role.
Also you need to fix backend_application_nameN (N = 0, 1, 2). They must match with the application_name in primary_conninfo . From what pg_stat_replication shows, it is "node2" but you have the application name in pgpool.conf is "node2.example.com".
You need to fix one of them so that they match.