| pgpool-II 4.5.4 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 5. Server Configuration | Next |
Pgpool-II can work with PostgreSQL native Streaming Replication, that is available since PostgreSQL 9.0. To configure Pgpool-II with streaming replication, set backend_clustering_mode to 'streaming-replication'.
Pgpool-II assumes that Streaming Replication is configured with Hot Standby on PostgreSQL, which means that the standby database can handle read-only queries.
Specifies the time interval in seconds to check the streaming replication delay. The default is 10.
This parameter can be changed by reloading the Pgpool-II configurations.
Specifies the PostgreSQL user name to perform streaming replication check. The user must have LOGIN privilege and 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.
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.
If sr_check_password is left blank Pgpool-II will first try to get the password for sr_check_user from pool_passwd file before using the empty password.
Pgpool-II accepts following forms of password in either sr_check_password or pool_passwd file:
Most secure and recommended way to store password. The password string must be prefixed with AES. You can use pg_enc utility to create the correctly formatted AES encrypted password strings. Pgpool-II will require a valid decryption key at the startup to use the encrypted passwords. see Section 6.4.2 for more details on providing the decryption key to Pgpool-II
Not so secure as AES256, but still better than clear text password. The password string must be prefixed with MD5. Note that the backend must set up MD5 authentication as well. You can use pg_md5 utility to create the correctly formatted MD5 hashed password strings.
Not encrypted, clear text password. You should avoid to use this if possible. The password string must be prefixed with TEXT. For example if you want to set mypass as a password, you should specify TEXTmypass in the password field. In the absence of a valid prefix, Pgpool-II will considered the string as a plain text password.
This parameter can be changed by reloading the Pgpool-II configurations.
Specifies the database to perform streaming replication delay checks. The default is "postgres".
This parameter can be changed by reloading the Pgpool-II configurations.
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-II configurations.
Specifies the maximum tolerance level of replication delay on the standby server against the primary server. If this value is specified without units, it is taken as milliseconds. If the specified value is greater than 0, delay_threshold is ignored. 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.
Replication delay is taken from PostgreSQL's system view pg_stat_replication.replay_lag. The view is available PostgreSQL 10 or later. If earlier version of PostgreSQL is used, Pgpool-II automatically falls back to delay_threshold and delay_threshold_by_time is ignored.
This parameter relies on backend_application_name being correctly set and matching application_name in your PostgreSQL standby's primary_conninfo.
If this parameter is enabled, SHOW POOL NODES and pcp_node_info show replication delay in seconds, rather than bytes.
This parameter can be changed by reloading the Pgpool-II configurations.
This parameter is valid only when delay_threshold or delay_threshold_by_time is set to greater than 0. When set to on, if the delay of the load balancing node is greater than delay_threshold or delay_threshold_by_time, Pgpool-II does not send read queries to the primary node but the least delay standby with backend_weight to greater than 0. If delay of all standby nodes are greater than delay_threshold or delay_threshold_by_time the primary selected as the load balancing node first, Pgpool-II sends to the primary. Default is off.
This parameter can be changed by reloading the Pgpool-II configurations.
Specifies when to log the replication delay. Below table contains the list of all valid values for the parameter.
Table 5-11. Log standby delay options
| Value | Description |
|---|---|
| 'none' | Never log the standby delay |
| '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 or delay_threshold_by_time value (the default) |
This parameter can be changed by reloading the Pgpool-II configurations.