I have a postgresql 10 master db with 2 hot standby servers with streaming replication, and the replication is working correctly. The
synchronous_commit is setted to
Also I have a pgpool 3.7.5 configured with the params:
delay_threshold = 1 sr_check_period = 1
And the the following weights:
- master: 1
- node1: 3
- node2: 3
In the log I can see the node1 and node2 are lagging:
Replication of node:1 is behind 75016 bytes from the primary server (node:0)
The pgpool docs says:
Specifies the maximum tolerance level of replication delay in WAL bytes on the standby server against the primary server. If the delay exceeds this configured level, Pgpool-II stops sending the SELECT queries to the standby server and starts routing everything to the primary server even if load_balance_mode is enabled, until the standby catches-up with the primary. Setting this parameter to 0 disables the delay checking. This delay threshold check is performed every sr_check_period. Default is 0.
The problem it's that pgpool sends queries to the hot standbys before they obtained the new data from master through streaming replication.
I enabled the
log_per_node_statement = on temporally to be able to see which node the query executes and I can see that queries are sent to the nodes even if there aren't sync when
delay_threshold should avoid that.
Am I missing something? When the nodes are behind master the queries are not supposed to go the master?
Thanks in advance.
Other config values of pgpool are:
num_init_children = 120 max_pool = 3 connection_cache = off load_balance_mode = on master_slave_sub_mode = 'stream' replication_mode = off sr_check_period = 1