最佳实践
有奖捉虫：行业应用 & 管理与支持文档专题 HOT
文档中心 > 最佳实践 > 云数据库 PostgreSQL > 通过 pgpool 实现读写分离

通过 pgpool 实现读写分离

最近更新时间：2024-05-17 11:02:21

我的收藏

本页目录：

1、安装 pgpool

下载 pgpool 并进行安装，下载 地址。
$ ./configure
$ make
$ make install

2、配置文件

说明：
使用 pgpool 实现负载均衡访问，所有认证发生在客户端和 pgpool 之间，同时客户端仍然需要继续通过 PostgreSQL 的认证过程。
配置 pool_passwd 密码文件
pool_passwd 密码文件是通过 pgpool 连接数据库时需要使用密码文件。可以使用如下命令生成密码文件：
[root@VM-0-15-tencentos ~]# cd /usr/local/bin
[root@VM-0-15-tencentos bin]# pg_md5 --md5auth --username=dbadmin password
[root@VM-0-15-tencentos bin]# more /usr/local/etc/pool_passwd 
dbadmin:md50b0cdb5c1d1f30fe83e5a72061749681
配置 pgpool.conf 文件
当您安装 pgpool-II 后， pgpool.conf.sample 被自动建立。我们建议拷贝或者重命名它为 pgpool.conf ，然后您可以随意编辑它。
$ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
pgpool-II 默认只接受到 9999 端口的本地连接。如果您希望从其他主机接受连接，请设置
listen_addresses = 'localhost'
port = 9999
重要的 pgpool 配置如下，请参考：
注意：
如下配置为重点参数示例配置，请您根据自身业务特点进行调整，并在上线前严格测试。
#------------------------------------------------------------------------------
# BACKEND CLUSTERING MODE
# Choose one of: 'streaming_replication', 'native_replication',
# 'logical_replication', 'slony', 'raw' or 'snapshot_isolation'
# (change requires restart)
#------------------------------------------------------------------------------
backend_clustering_mode = 'streaming_replication'
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
listen_addresses = '0.0.0.0'
# what host name(s) or IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 9989
# Port number
# (change requires restart)
unix_socket_directories = '/tmp'
# Unix domain socket path(s)
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
#unix_socket_group = ''
# The Owner group of Unix domain socket(s)
# (change requires restart)
reserved_connections = 0
# Number of reserved connections.
# Pgpool-II does not accept connections if over
# num_init_chidlren - reserved_connections.
# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = ''
# what host name(s) or IP address(es) for pcp process to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
pcp_port = 9898
# Port number for pcp
# (change requires restart)
pcp_socket_dir = '/tmp'
# Unix domain socket path for pcp
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
listen_backlog_multiplier = 2
# Set the backlog parameter of listen(2) to
# num_init_children * listen_backlog_multiplier.
# (change requires restart)
serialize_accept = off
# whether to serialize accept() call to avoid thundering herd problem
# (change requires restart)
# - Backend Connection Settings -
backend_hostname0 = '172.16.0.3'
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
#backend_data_directory0 = '/data'
# Data directory for backend 0
backend_flag0 = 'ALWAYS_PRIMARY'
# Controls various backend behavior
# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
# or ALWAYS_PRIMARY
backend_application_name0 = 'server0'
# walsender's application_name, used for "show pool_nodes" command
backend_hostname1 = '172.16.0.12'
backend_port1 = 5432
backend_weight1 = 1
#backend_data_directory1 = '/data1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'
# - Authentication -
enable_pool_hba = on
# Use pool_hba.conf for client authentication
pool_passwd = 'pool_passwd'
# File name of pool_passwd for md5 authentication.
# "" disables pool_passwd.
# (change requires restart)
allow_clear_text_frontend_auth = off
# Allow Pgpool-II to use clear text password authentication
# with clients, when pool_passwd does not
# contain the user password
# - SSL Connections -
ssl =off
# Enable SSL support
# (change requires restart)
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
num_init_children = 32
# Maximum Number of concurrent sessions allowed
# (change requires restart)
max_pool = 4
# Number of connection pool caches per connection
# (change requires restart)
# - Life time -
child_life_time = 5min
# Pool exits after being idle for this many seconds
child_max_connections = 0
# Pool exits after receiving that many connections
# 0 means no exit
connection_life_time = 0
# Connection to backend closes after being idle for this many seconds
# 0 means no close
client_idle_limit = 0
# Client is disconnected after being idle for that many seconds
# (even inside an explicit transactions!)
# 0 means no disconnection
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/var/run/pgpool/pgpool.pid'
# PID file name
# Can be specified as relative to the
# location of pgpool.conf file or
# as an absolute path
# (change requires restart)
logdir = '/tmp'
# Directory of pgPool status file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTION POOLING
#------------------------------------------------------------------------------
connection_cache = on
# Activate connection pools
# (change requires restart)
# Semicolon separated list of queries
# to be issued at the end of a session
# The default is for 8.3 and later
reset_query_list = 'ABORT; DISCARD ALL'
# The following one is for 8.2 and before
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
# Activate load balancing mode
# (change requires restart)
ignore_leading_white_space = on
# Ignore leading white spaces of each query
write_function_list = ''
# Comma separated list of function names
# that write to database
# Regexp are accepted
# If both read_only_function_list and write_function_list
# is empty, function's volatile property is checked.
# If it's volatile, the function is regarded as a
# writing function.
allow_sql_comments = off
# if on, ignore SQL comments when judging if load balance or
# query cache is possible.
# If off, SQL comments effectively prevent the judgment
# (pre 3.4 behavior).
disable_load_balance_on_write = 'transaction'
# Load balance behavior when write query is issued
# in an explicit transaction.
#
# Valid values:
#
# 'transaction' (default):
# if a write query is issued, subsequent
# read queries will not be load balanced
# until the transaction ends.
#
# 'trans_transaction':
# if a write query is issued, subsequent
# read queries in an explicit transaction
# will not be load balanced until the session ends.
#
# 'dml_adaptive':
# Queries on the tables that have already been
# modified within the current explicit transaction will
# not be load balanced until the end of the transaction.
#
# 'always':
# if a write query is issued, read queries will
# not be load balanced until the session ends.
#
# Note that any query not in an explicit transaction
# is not affected by the parameter except 'always'.
statement_level_load_balance = off
# Enables statement level load balancing
#------------------------------------------------------------------------------
# HEALTH CHECK GLOBAL PARAMETERS
#------------------------------------------------------------------------------
health_check_period = 0
# Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = 'nobody'
# Health check user
health_check_password = ''
# Password for health check user
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
health_check_database = ''
# Database name for health check. If '', tries 'postgres' frist,
health_check_max_retries = 60
# Maximum number of times to retry a failed health check before giving up.
health_check_retry_delay = 1
# Amount of time to wait (in seconds) between retries.
connect_timeout = 10000
# Timeout value in milliseconds before giving up to connect to backend.
# Default is 10000 ms (10 second). Flaky network user may want to increase
# the value. 0 means no timeout.
# Note that this value is not only used for health check,
# but also for ordinary conection to backend.

3、配置 PCP 命令

pgpool-II 有一个用于管理功能的接口，用于通过网络获取数据库节点信息、关闭 pgpool-II 等。要使用 PCP 命令，必须进行用户认证。这种认证和 PostgreSQL 的用户认证不同。这需要在 pcp.conf 文件中定义一个用户和密码。在这个文件中，一个用户名和密码成对地出现在每一行中，它们用冒号（:）隔开。密码为用 md5 哈希加密的格式。

4、配置数据库节点

# - Backend Connection Settings -
backend_hostname0 = '172.16.0.30'
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
#backend_data_directory0 = '/data'
# Data directory for backend 0
backend_flag0 = 'ALWAYS_PRIMARY'
# Controls various backend behavior
# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
# or ALWAYS_PRIMARY
backend_application_name0 = 'server0'
# walsender's application_name, used for "show pool_nodes" command
backend_hostname1 = '172.16.0.16'
backend_port1 = 5432
backend_weight1 = 1
#backend_data_directory1 = '/data1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'
当 load_balance_mode 被设置为 true，pgpool-II 将在数据库节点之间分发 SELECT 查询。
load_balance_mode = on
# Activate load balancing mode
# (change requires restart)
ignore_leading_white_space = on
# Ignore leading white spaces of each query
write_function_list = ''
# Comma separated list of function names
# that write to database
# Regexp are accepted
# If both read_only_function_list and write_function_list
# is empty, function's volatile property is checked.
# If it's volatile, the function is regarded as a
# writing function.
allow_sql_comments = off
# if on, ignore SQL comments when judging if load balance or
# query cache is possible.
# If off, SQL comments effectively prevent the judgment
# (pre 3.4 behavior).
disable_load_balance_on_write = 'transaction'
# Load balance behavior when write query is issued
# in an explicit transaction.
#
# Valid values:
#
# 'transaction' (default):
# if a write query is issued, subsequent
# read queries will not be load balanced
# until the transaction ends.
#
# 'trans_transaction':
# if a write query is issued, subsequent
# read queries in an explicit transaction
# will not be load balanced until the session ends.
#
# 'dml_adaptive':
# Queries on the tables that have already been
# modified within the current explicit transaction will
# not be load balanced until the end of the transaction.
#
# 'always':
# if a write query is issued, read queries will
# not be load balanced until the session ends.
#
# Note that any query not in an explicit transaction
# is not affected by the parameter except 'always'.
statement_level_load_balance = off
# Enables statement level load balancing

5、启动 pgpool-II 并验证读写分离

$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
说明：
连接并查询 pg_is_in_recovery()，然后断开重连再查询 pg_is_in_recovery()，如果交替返回 false 和 true，说明是交替将请求发送给了主库和从库，即读写分离成功。
使用客户端 psql 连接 pgpool，展示 status 为正常。
[root@VM-0-15-tencentos ~]# /usr/local/pgsql/bin/psql -h127.0.0.1 -p9989 -Udbadmin -d postgres
Password for user dbadmin: 
psql (15.1)
Type "help" for help.
?
postgres=> show pool_nodes;
 node_id |  hostname   | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_st
atus_change  
---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------
-------------
 0       | 172.16.0.30  | 5432 | up     | unknown   | 0.500000  | primary | unknown | 4          | false             | 0                 |                   |                        | 2024-02
-27 20:04:13
 1       | 172.16.0.16 | 5432 | up     | unknown   | 0.500000  | standby | unknown | 13         | true              | 0                 |                   |                        | 2024-02
-27 20:04:13
(2 rows)
?
postgres=>
在客户端使用读写 SQL，由于提前区分了读写实例和只读实例，发现读写分离成功。
postgres=> insert into pgpool1(id,name)values(3,'b');    
INSERT 0 1
postgres=> select * from pgpool1;
 id | name 
----+------
  1 | a
  2 | b
  3 | a
  4 | b
  3 | a
(5 rows)
?
postgres=>
中国站


http://www.vxiaotou.com