5.8. Health Check

Pgpool-II periodically connects to the configured PostgreSQL backends to detect any error on the servers or networks. This error check procedure is called "health check". If an error is detected, Pgpool-II performs failover or degeneration depending on the configurations.

Caution

Health check requires one extra connection to each backend node, so max_connections in the postgresql.conf needs to be adjusted accordingly.

The health check process collects various statistics data such as number of health check count in total. To inspect the statistics data, use SHOW POOL_HEALTH_CHECK_STATS command. Please note that the data is stored in the shared memory area and it will be initialized upon Pgpool-II starting up.

Following parameter names can also have numeric suffix at the end of each name. The suffix corresponds to backend id, which is defined in backend information, such as backend_hostname. example, health_check_timeout0 is applied to backend 0's health_check_timeout value.

If there's no parameter with suffix, the value for the backend is taken from the parameter name which does not have a suffix. In this sense, parameter names without suffix work like "global variables".

health_check_timeout (integer)

Specifies the timeout in seconds to give up connecting to the backend PostgreSQL if the TCP connect does not succeed within this time.

This parameter serves to prevent the health check from waiting for a long time when the network cable is unplugged. Default value is 20. Setting it to 0, disables the timeout (waits until TCP/IP timeout).

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

health_check_period (integer)

Specifies the interval between the health checks in seconds. Default is 0, which means health check is disabled.

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

health_check_user (string)

Specifies the PostgreSQL user name to perform health check. The same user must exist in all the PostgreSQL backends. Otherwise, health check causes an error.

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

health_check_password (string)

Specifies the password for the PostgreSQL user name configured in health_check_user to perform health check. The user and password must be same in all the PostgreSQL backends. Otherwise, health check results in an error.

If health_check_password is left blank Pgpool-II will first try to get the password for health_check_user from pool_passwd file before using the empty password.

Pgpool-II accepts following forms of password in either health_check_password or pool_passwd file:

AES256-CBC encrypted password

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

MD5 hashed password

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.

Plain text password

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.

health_check_database (string)

Specifies the PostgreSQL database name to perform health check. The default is ''(empty), which tries "postgres" database first, then "template1" database until it succeeds

health_check_database was introduced in Pgpool-II V3.5.

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

health_check_max_retries (integer)

Specifies the maximum number of retries to do before giving up and initiating failover when health check fails.

Tip: This setting can be useful in spotty networks, when it is expected that health checks will fail occasionally even when the primary node is fine.

Tip: It is advised that failover_on_backend_error must be disabled, if you want to enable health_check_max_retries.

Default is 0, which means do not retry.

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

health_check_retry_delay (integer)

Specifies the amount of time in seconds to sleep between failed health check retries (not used unless health_check_max_retries is > 0). If 0, then retries are immediate without delay.

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

connect_timeout (integer)

Specifies the amount of time in milliseconds before giving up connecting to backend using connect() system call. Default is 10000 ms (10 second). The flaky network user may want to increase the value. 0 means no timeout.

Note: connect_timeout value is not only used for a health check, but also for creating ordinary connection pools.

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