PG高可用集群搭建—基于Pgpool-II-4.5.2（二）------配置篇

一 PostgreSQL配置

本例Postgresql安装在/usr/pgsql-16目录，Pgpool-II安装在/usr/local/pgpool目录。

1 在PostgreSQL主节点(pg1)上设置流复制

# 根据规划，创建归档日志目录,使用postgres用户操作
su - postgres
mkdir -p /pgdata/archive

# 在主节点初始化数据库，以postgres用户身份执行
/usr/pgsql-16/bin/initdb -D /pgdata

# 修改 /pgdata/postgresql.conf文件
listen_addresses = '*'
archive_mode = on
archive_command = 'test ! -f /pgdata/archive/%f && cp %p /pgdata/archive/%f'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replicahot_
standby = on
wal_log_hints = on


在主节点启动后，我们将使用Pgpool-II在线恢复（Online Recovery）功能设置从节点。

2 创建相关用户（主节点创建即可）

基于安全考虑，创建repl用户用于流复制，pgpool用户用于流复制延迟检查及Pgpool健康检查。

[postgres@pg1]# psql -U postgres -p 5432
  SET password_encryption = 'scram-sha-256';
  CREATE ROLE pgpool WITH LOGIN;
  CREATE ROLE repl WITH REPLICATION LOGIN;
  \password pgpool
  \password repl
  \password postgres




如果想在show pool_nodes的结果里显示"replication_state"和"replication_sync_state"列，pgpool用户需要有数据库的超级用户权限或者添加到pg_monitor组（>=Pgpool-II 4.1）：

GRANT pg_monitor TO pgpool;

修改/pgdata/pg_hba.conf，允许子网内其它节点访问主节点并开启md5认证
 host    all                 all               all                             scram-sha-256host    replication     repl            192.168.75.0/24        scram-sha-256

3 设置主从节点postgres用户ssh免密登录

如果要使用自动故障转移和在线恢复功能，需要设置主从节点间postgres用户免密登录。

4 建立PostgreSQL密码文件

在流复制跟在线recovery时需要repl用户密码，执行pg_rewind时需要postgres超级用户的密码，因此需要在postgres的家目录下创建.pgpass密码文件（重要！）

[pg1]# su - postgres
[pg1]$ vim $HOME/.pgpass
pg1:5432:replication:repl:<repl user password>
pg2:5432:replication:repl:<repl user password>
pg3:5432:replication:repl:<repl user password>
pg1:5432:postgres:postgres:<postgres user password>
pg2:5432:postgres:postgres:<postgres user password>
pg3:5432:postgres:postgres:<postgres user password>
[pg1]$ chmod 600 $HOME/.pgpass

创建完成后用scp复制到其他节点的postgres家目录下。

5 防火墙设置

如果启用了防火墙，需要设置防火墙允许相应端口：
  [all servers]# firewall-cmd --permanent --zone=public --add-service=postgresql
[all servers]# firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp  --add-port=9694/udp
[all servers]# firewall-cmd --reload

6 创建节点文件pgpool_node_id

需要在每个节点机的/usr/local/pgpool/etc目录下创建一个节点文件pgpool_node_id用于标识节点ID，以便看门狗识别每个节点，每个节点ID不能相同。

[pg1]# cat  /usr/local/pgpool/etc/pgpool_node_id 
    0 
[pg2]# cat  /usr/local/pgpool/etc/pgpool_node_id
    1 
[pg3]# cat  /usr/local/pgpool/etc/pgpool_node_id 
    2

二 Pgpool-II配置

源码安装，配置文件在pgpool安装目录下的etc目录下，复制pgpool.conf.sample文件为pgpool.conf，然后修改相应选项。

1 集群模式

Pgpool-II支持多种集群模式，此次使用PostgreSQL的流复制模式，这也是官方推荐的模式。
backend_clustering_mode = 'streaming_replication'

2 侦听地址、端口号

listen_addresses = '*'
port = 9999

3 流复制检查

sr_check_user = 'pgpool'
sr_check_password = ''   # 密码置空，使用pool_passwd文件

4 健康检查

启用运行状况检查，以便Pgpool-II执行故障切换。此外，如果网络不稳定，即使后端运行正常，健康检查也会失败，可能会发生故障切换或退化操作。为了防止此类错误检测运行状况检查，我们将Health_check_max_reries设置为3。
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''    # 密码置空，使用pool_passwd文件
health_check_max_retries = 3

5 PostgreSQL服务端设置

 backend_hostname0 = 'pg1'                    # 主机名或IP地址
backend_port0 = 5432
backend_weight0 = 1                                   # 权重，1表示允许进行select负载均衡
backend_data_directory0 = '/pgdata'            # 对应的数据库数据目录
backend_flag0 = 'ALLOW_TO_FAILOVER'   # 接受故障转移

backend_hostname1 = 'pg2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'pg3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/pgdata'
backend_flag2 = 'ALLOW_TO_FAILOVER'
如果想在show pool_nodes的结果里显示"replication_state" and "replication_sync_state"列，需要设置backend_application_name参数。这里我们设置成后端主机名。(>=Pgpool-II 4.1)
  ...
backend_application_name0 = 'pg1'
...
backend_application_name1 = 'pg2'
...
backend_application_name2 = 'pg3'

6 故障转移配置

在failover_command参数中指定故障转移后要执行的failover.sh脚本。如果我们使用3台PostgreSQL服务器，则需要指定flow_primary_command用于在主节点故障转移后进行后续处理。如果有两台PostgreSQL服务器，则不需要设置flow_primary_command。
  failover_command = '/usr/local/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
 follow_primary_command = '/usr/local/pgpool/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

pgpool安装目录下的etc目录下有相关脚本的的示例文件，需要根据自身情况做相应的修改。修改完成后scp到其他节点相同目录下。
cd  /usr/local/pgpool/etc
cp -p failover.sh.sample failover.sh
cp -p follow_primary.sh.sample follow_primary.sh
chown postgres:postgres failover.sh follow_primary.sh
# 这两个脚本文件主要修改PG_HOME这一项，改为自己的PostgreSQL安装目录

follow_primary_command所设置的脚本follow_primary.sh脚本还需要执行pcp命令，还需要做pcp密码验证，因些还要修改follow_primary.sh的PCP_USER参数，这里我们使用pgpool用户，所有节点均需操作。
# cat  /usr/local/pgpool/etc/follow_primary.sh
...
PCP_USER=pgpool
..

使用pg_md5程序加密pgpol密码并保存到/usr/local/pgpool/etc/pcp.conf文件中，所有节点均需操作。
/usr/local/pgpool/bin/pg_md5 pgpool密码
echo 'pgpool:pgpool_md5密码' >> /usr/local/pgpool/etc/pcp.conf

为了让follow_primary.sh脚本执行pcp命令时无需输入密码，需要在postgres用户的家目录下生成.pcppass文件,所有节点均需操作。
 [all servers]# su - postgres
[all servers]$ echo 'localhost:9898:pgpool:<pgpool用户密码>'  >  ~/.pcppass
[all servers]$ chmod 600  ~/.pcppass

7 在线recovery配置

在线recovery需要超级用户权限，因此需要以postgres用户的身份运行，需要执行recovery_1st_stage_command参数指定的脚本，配置如下：
  recovery_user = 'postgres'
  recovery_password = ''
  recovery_1st_stage_command = 'recovery_1st_stage'

这两个脚本在pgpool安装目录下etc目录下也有相应的用例，根据自己的场景修改相关参数，主要修改PG_HOME参数。并将修改好的脚本文件复制到PG_HOME目录，赋予可执行权限。
  [pg1]# cp -p /usr/local/pgpool/etc/recovery_1st_stage.sample /pgdata/recovery_1st_stage
[pg1]# cp -p /usr/local/pgpool/etc/pgpool_remote_start.sample  /pgdata/pgpool_remote_start
[pg1]# chown postgres:postgres  /pgdata/{recovery_1st_stage,pgpool_remote_start}
[pg1]# chmod +x /pgdata/{recovery_1st_stage,pgpool_remote_start}

在线recovery需要用到pgpool_recovery扩展提供的pgpool_recovery, pgpool_remote_start, pgpool_switch_xlog函数，因此需要在主节点的template1数据库模板上创建pgpool_recovery扩展
[pg1]# su - postgres 
[pg1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"


8 客户端认证配置

出于安全原因，客户端访问pgpool里也需要密码认证，因些需要配置pgpool的密码认证，类似于PostgreSQL的pg_hba.conf，pgpool的配置文件保存在pgpool安装目录的etc目录下，文件名为pool_hba.conf，我们使用scram-sha-256算法。
 enable_pool_hba = on
 pool_passwd = 'pool_passwd'

pool_hba.conf
host    all    pgpool     samenet     scram-sha-256
host    all    postgres   samenet     scram-sha-256

用于身份验证的默认密码文件名为 pool_passwd。 要使用 scram-sha-256 身份验证，请将解密密钥设置为 解密密码是必需的。我们在 postgres 用户的主目录中创建 .pgpoolkey 文件
[all servers]# su - postgres 
[all servers]$ echo 'some string' > ~/.pgpoolkey 
[all servers]$ chmod 600 ~/.pgpoolkey

生成key文件后，执行pg_enc -m -k /path/to/.pgpoolkey -u username -p生成用户的密码。如果 pool_passwd 尚不存在，它将在与 pgpool.conf 相同的目录中创建。
[all servers]# su - postgres 
[all servers]$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p 
    db password: [pgpool user's password] 
[all servers]$ pg_enc -m -k ~/.pgpoolkey -u postgres -p 
    db password: [postgres user's password]

9 看门狗配置

# 看门狗相关
use_watchdog = on
# 节点1
hostname0 = 'pg1'
wd_port0 = 9000
pgpool_port0 = 9999
# 节点2
hostname1 = 'pg2'
wd_port1 = 9000
pgpool_port1 = 9999
# 节点3
hostname2 = 'pg3'
wd_port2 = 9000
pgpool_port2 = 9999
delegate_ip = '192.168.74.100' #虚拟IP
if_cmd_path = '/usr/sbin'
if_up_cmd = '/usr/bin/sudo /usr/sbin/ip addr add $_IP_$/32 dev ens33 label ens33:0'
if_down_cmd = '/usr/bin/sudo /usr/sbin/ip addr del $_IP_$/32 dev ens33'
arping_path = '/usr/sbin'
arping_cmd = '/usr/bin/sudo /usr/bin/arping -U $_IP_$ -w 1 -I ens33'
ping_path = '/usr/bin'
wd_escalation_command = '/usr/local/pgpool/etc/escalation.sh'
heartbeat_hostname0 = 'pg1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'pg2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'pg3'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
看门狗在做浮动IP切换、执行arping检查其他节点是否存活时需要以root权限执行，需要为postgres用户设置相应的权限，使用vim /etc/sudoers编辑权限，加上如下内容
     postgres ALL=NOPASSWD: /usr/sbin/ip 
    postgres ALL=NOPASSWD: /usr/bin/arping

wd_escalation_command指定浮动IP切换脚本，/usr/local/pgpool/etc目录下有相应用例escalation.sh.sample，根据自身情况修改：
    [all servers]# cd /usr/local/pgpool/etc
    [all servers]# cp -p escalation.sh.sample escalation.sh
    [all servers]# chown postgres:postgres escalation.sh
    [all servers]# chmod +x escalation.sh
    [all servers]# vim escalation.sh
        ...
        PGPOOLS=(pg1 pg2 pg3)
        VIP=192.168.75.100
        # 需要绑定的网卡名可通过ifconfig -a查看
        DEVICE=ens33

10 日志配置

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
在所有服务器上创建日志目录。
[all servers]# mkdir /var/log/pgpool_log/
[all servers]# chown postgres:postgres /var/log/pgpool_log/

11 配置分发

pg1(主节点) 上的 pgpool.conf 配置完成。 将 pgpool.conf 复制到其他 Pgpool-II 节点（pg2 和 pg3）。
[pg1]# scp -p  /usr/local/pgpool/pgpool.conf pg2:/usr/local/pgpool/pgpool.conf
[pg1]# scp -p  /usr/local/pgpool/pgpool.conf  pg3:/usr/local/pgpool/pgpool.conf

附：一个完整的配置文件例子

backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 9999
unix_socket_directories = '/var/run'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run'
# master
backend_hostname0 = 'pg1' #主机名或IP地址
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'pg1'
# standby-1
backend_hostname1 = 'pg2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'pg2'
# standby-2
backend_hostname2 = 'pg3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/pgdata'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'pg3'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
process_management_mode = dynamic
num_init_children = 32
min_spare_children = 5
max_spare_children = 10
max_pool = 4
# 日志相关
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
connection_cache = on #连接池开启
load_balance_mode = on #负载均衡开启
disable_load_balance_on_write = 'transaction'  #应用有事务时一定要设置
# 健康检查相关
sr_check_user = 'pgpool'
sr_check_password = ''
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
# failover、recovery相关
failover_command = '/usr/loacl/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/usr/loacl/pgpool/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'
# 看门狗相关
use_watchdog = on
# 节点1
hostname0 = 'pg1'
wd_port0 = 9000
pgpool_port0 = 9999
# 节点2
hostname1 = 'pg2'
wd_port1 = 9000
pgpool_port1 = 9999
# 节点3
hostname2 = 'pg3'
wd_port2 = 9000
pgpool_port2 = 9999
delegate_ip = '192.168.75.100'   #浮动IP
if_cmd_path = '/usr/sbin'
if_up_cmd = '/usr/bin/sudo /usr/sbin/ip addr add $_IP_$/32 dev ens33 label eth0:0'
if_down_cmd = '/usr/bin/sudo /usr/sbin/ip addr del $_IP_$/32 dev ens33'
arping_path = '/usr/sbin'
arping_cmd = '/usr/bin/sudo /usr/bin/arping -U $_IP_$ -w 1 -I ens33'
ping_path = '/usr/bin'
wd_escalation_command = '/usr/loacl/pgpool/etc/escalation.sh'
heartbeat_hostname0 = 'pg1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'pg2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'pg3'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30

12 结语

最后需要确认三个节点均存在对应的配置文件（个别文件的内容是不一致的，但是文件名相同）




评论

发表评论

此博客中的热门博文

PG高可用集群搭建—基于Pgpool-II-4.5.2（一）------规划及安装

图片
一、前言 Pgpool-II是一款工作在PostgreSQL服务器和PostgreSQL数据库客户端之间的中间件 。 提供了以下功能： 连接池 Pgpool-II保存到PostgreSQL服务器的连接，并在具有相同属性(即用户名、数据库、协议版本)的新连接进入时重新使用它们。 它减少了连接开销，并提高了系统的整体吞吐量。 复制 Pgpool-II可以管理多个PostgreSQL服务器。 使用复制功能可在2个或更多物理磁盘上创建实时备份，以便在磁盘出现故障时无需停服即可继续服务。 负载均衡 如果数据库有多副本，则在任何服务器上执行SELECT查询都将返回相同的结果。 Pgpool-II利用复制功能，通过在多个服务器之间分配SELECT查询来减少每台PostgreSQL服务器上的负载，从而提高系统的整体吞吐量。查询 性能随PostgreSQL服务器数量的增加而成比例提高。 负载均衡在有许多用户同时执行多个查询的情况下工作得最好。 限制过多的连接 与PostgreSQL的并发连接的最大数量是有限制的，连接过多后会被拒绝。 但是，设置最大连接数会增加资源消耗并影响系统性能。 Pgpool-II对最大连接数也有限制，但额外的连接将被排队，而不是立即返回错误。 看门狗 WatchDog可以协调多个Pgpool-II，创建一个健壮的集群系统，避免单点故障或脑裂。 WatchDog可以对其他pgpool-II节点执行健康检查，以检测PgPoll-II的故障。 如果主用Pgpool-II关闭，备用Pgpool-II可以升级为主用，并接管虚拟IP。 查询缓存 查询缓存允许保存SELECT语句及其结果。 如果出现相同的查询，则Pgpool-II将从缓存中返回值。 由于不涉及SQL解析或访问PostgreSQL，因此在内存缓存中使用非常快。 另一方面，在某些情况下，它可能比正常路径慢，因为它增加了存储缓存数据的一些开销。 Pgpool-II使用PostgreSQL的后端和前端协议，并在后端和前端之间转发消息。 因此，数据库应用程序(前端)认为Pgpool-II是实际的PostgreSQL服务器，而服务器(后端)将Pgpool-II视为其客户端之一。 因为Pgpool-II对服务器和客户端都是透明的，所以现有的数据库应用程序 几乎可以在不更改其源代码的情况下与Pgpool-II一起使用。 下...
阅读全文