pgpool-ii 4.3 增加了一些有价值的特性

作者

digoal

日期

2022-03-04

标签

PostgreSQL , pgpool-ii , failover , 负载均衡

pgpool-ii 4.3 增加了一些有价值的特性, 例如负载均衡时可以控制当所有standby都超过延迟阈值, 是否将SQL发送给最低延迟的standby? 参数控制当数据库收到shutdown或者terminate backend信号时触发failover? 支持PG 14的sql parser等等.

详见release notes:
https://pgpool.net/mediawiki/index.php/Main_Page

• Allow to choose the least replication delay standby node when selecting the load balance node.
• Allow to specify the node id to be promoted in pcp_promote_node.
• Allow to configure to not trigger failover when PostgreSQL is shutdown by admin or killed by pg_terminate_backend.
• Import PostgreSQL 14's SQL parser.
  • Major changes of PostgreSQL 14 parser include:
    • Allow an alias to be used to a JOIN ... USING
    • Allow DISTINCT to be added to GROUP BY
    • New SEARCH and CYCLE clauses for common table expressions

https://www.pgpool.net/docs/43/en/html/release-4-3-0.html

新特性相关参数:

1、prefer_lower_delay_standby (boolean)

This parameter is valid only when delay_threshold is set to greater than 0. When set to on, if the delay of the load balancing node is greater than delay_threshold, 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 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.

2、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.

3、failover_on_backend_shutdown (boolean)

When set to on, Pgpool-II detects a backend shutdown event by checking particular error code 57P01 and 57P02 on a session established between the client and backend. If it detects those error codes, a failover is triggered on that node. When this is set to off, Pgpool-II only report an error and disconnect the session in case of such errors. The default is off.

If no client is connected, Pgpool-II will not detect the event even if the parameter is on.

Please note that 57P01 is not only sent at a shutdown event, but also sent when the backend currently connected is killed by pg_terminate_backend or SIGTERM signal. This is annoying because it results in unwanted failover. To avoid this, turn off this parameter.

Note: It is recommended to turn on the backend health checking (see Section 5.8) when failover_on_backend_shutdown is set to off.

This parameter is available in Pgpool-II 4.3 or later. This parameter can be changed by reloading the Pgpool-II configurations.

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.