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 remote_write

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:

delay_threshold (integer)

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.