5.11. Streaming Replication Check

Pgpool-II can work with PostgreSQL native Streaming Replication, that is available since PostgreSQL 9.0. To configure Pgpool-II with streaming replication, enable master_slave_mode and set master_slave_sub_mode to 'stream'.

Pgpool-II assumes that Streaming Replication is configured with Hot Standby on PostgreSQL, which means that the standby database can handle read-only queries.

sr_check_period (integer)

Specifies the time interval in seconds to check the streaming replication delay. Default is 0, which means the check is disabled.

This parameter can be changed by reloading the Pgpool-II configurations.

sr_check_user (string)

Specifies the PostgreSQL user name to perform streaming replication check. The user must exist on all the PostgreSQL backends.

If SSL is enabled, the streaming replication check process may use SSL connection.

Note: sr_check_user> and sr_check_password are used even when sr_check_period is set to 0 (disabled) for the identification of the primary server.

This parameter can be changed by reloading the Pgpool-II configurations.

sr_check_password (string)

Specifies the password of the sr_check_user PostgreSQL user to perform the streaming replication checks. Use '' (empty string) if the user does not requires a password.

This parameter can be changed by reloading the Pgpool-II configurations.

sr_check_database (string)

Specifies the database to perform streaming replication delay checks. The default is "postgres".

This parameter can be changed by reloading the Pgpool-II configurations.

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.

This parameter can be changed by reloading the Pgpool-IIconfigurations.

log_standby_delay (string)

Specifies when to log the replication delay. Below table contains the list of all valid values for the parameter.

Table 5-9. Log standby delay options

ValueDescription
'none'Never log the standby delay (the default)
'always'Log the standby delay if it's greater than 0, every time the replication delay is checked
'if_over_threshold'Only log the standby delay, when it exceeds delay_threshold value

This parameter can be changed by reloading the Pgpool-II configurations.