Centos7基于PG13.6+PgPool2-4.2.8搭建高可用

PostgreSQL+Pgpool2高可用安装步骤

1、前置准备

1、服务器

需要准备三台Centos7的服务、和一个虚拟IP。

三台服务器都已经配置好静态IP。

没有配的可以参考:https://blog.csdn.net/qq_37481017/article/details/118732932

主机名	IP	PostgreSQL版本	Pgpool2版本
server1	192.168.159.181	13.6	4.2.8
server2	192.168.159.182	13.6	4.2.8
server3	192.168.159.183	13.6	4.2.8
VIP	192.168.159.184	/	/

2、环境准备

以下所有操作需要在所有机器上执行、操作用户是root

1、修改主机名

修改机器主机名。

hostnamectl set-hostname server1

2、修改hosts

修改hosts文件、增加以下配置

vim /etc/hosts
# 增加以下配置
192.168.159.181 server1
192.168.159.182 server2
192.168.159.183 server3

3、下载依赖

yum install libtermcap-devel ncurses-devel libevent-devel readline-devel pcre-devel gcc openssl openssl-devel per perl wget -y

4、创建postgres用户

useradd -m -U postgres
# 修改postgres用户密码
passwd postgres

5、关闭防火墙

# 查看防火墙状态
firewall-cmd --state
# 临时停止防火墙
systemctl stop firewalld.service
# 禁止防火墙开机启动
systemctl disable firewalld.service

6、创建安装包目录

mkdir -p /app/software 

7、重启机器

reboot

2、安装PostgreSQL

PostgreSQL官网:https://www.postgresql.org/

从官网下载指定版本安装包，上传到指定位置。这里以/app/software目录为例。

以下操作需要在所有机器上执行、操作用户是root

1、要求

PostgreSQL13.6版本构建需要make版本是3.80或者更高版本。先检查机器make版本是否符合要求

make --version

2、安装

1、解压安装包

tar -zxvf postgresql-13.6.tar.gz

2、安装

cd postgresql-13.6

# prefix指定安装到哪里
./configure --prefix=/app/postgresql-13.6

make world && make install-world

3、配置环境变量

vim /etc/profile

# 增加以下配置
LD_LIBRARY_PATH=/app/postgresql-13.6/lib
export LD_LIBRARY_PATH
PATH=/app/postgresql-13.6/bin:$PATH
export PATH
MANPATH=/app/postgresql-13.6/share/man:$MANPATH
export MANPATH

# 刷新环境变量
source /etc/profile
# 验证环境变量配置是否成功
pg_config

4、修改app所属用户

chown -R postgres:postgres /app

3、初始化数据库

以下操作只需要在server1机器执行、以postgres用户

1、切换用户

su postgres

2、初始化

[postgres@server1 bin]$ /app/postgresql-13.6/bin/initdb -D /app/postgresql-13.6/data -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.
# 输入超级用户密码
Enter new superuser password:
# 确认密码
Enter it again: 

creating directory /app/postgresql-13.6/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /app/postgresql-13.6/bin/pg_ctl -D /app/postgresql-13.6/data -l logfile start

3、修改postgresql.conf文件

vim /app/postgresql-13.6/data/postgresql.conf

# 修改以下配置
listen_addresses = '*'
port = 5432 
wal_level = replica
archive_mode = on
archive_command = 'cp "%p" "/app/postgresql-13.6/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
wal_log_hints = on

4、修改pg_hba.conf文件

vim /app/postgresql-13.6/data/pg_hba.conf

# 修改以下配置
host    all             all             0.0.0.0/0            trust
host    replication     all             0.0.0.0/0            trust

5、创建归档目录和日志目录

mkdir -p /app/postgresql-13.6/archivedir && mkdir -p /app/postgresql-13.6/logs

4、启动

1、启动数据库

# -D 指定数据存放目录  -l 指定日志目录文件
[postgres@server1 bin]$ /app/postgresql-13.6/bin/pg_ctl start -D /app/postgresql-13.6/data/ -l /app/postgresql-13.6/logs/postgres.log
waiting for server to start.... done
server started

2、创建数据库用户

[postgres@server1 bin]$ /app/postgresql-13.6/bin/psql -U postgres -p 5432
psql (13.6)
Type "help" for help.

postgres=# SET password_encryption = 'md5';
SET
postgres=# CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
CREATE ROLE
postgres=# \password pgpool
Enter new password for user "pgpool": 
Enter it again: 
postgres=# \password repl
Enter new password for user "repl": 
Enter it again: 
postgres=# \password postgres
Enter new password for user "postgres": 
Enter it again: 
postgres=# GRANT pg_monitor TO pgpool;
GRANT ROLE
postgres=# \q

3、安装Pgpool2

官网地址:https://pgpool.net/mediawiki/index.php/Main_Page

从官网下载指定版本安装包，上传到指定位置。这里以/app/software目录为例。

1、前置准备

以下操作需要在所有机器上执行、操作用户postgres

1、设置postgres用户互相免密

# 在postgres用户目录下创建
mkdir ~/.ssh && chmod 700 ~/.ssh  && cd ~/.ssh
# 生成公钥和私钥
ssh-keygen -t rsa -f id_rsa_pgpool
# 远程拷贝给对方机器
ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
ssh-copy-id -i id_rsa_pgpool.pub postgres@server3

2、创建.pgpass文件

vim /app/postgresql-13.6/.pgpass

# 主机名:端口:库名:用户名:密码
# 增加以下配置
server1:5432:replication:repl:repl
server2:5432:replication:repl:repl
server3:5432:replication:repl:repl
server1:5432:postgres:postgres:postgres
server2:5432:postgres:postgres:postgres
server3:5432:postgres:postgres:postgres

chmod 600 /app/postgresql-13.6/.pgpass

2、安装

1、解压安装包

tar -zxvf pgpool-II-4.2.8.tar.gz

2、安装

cd /app/software/pgpool-II-4.2.8
# --prefix指定安装目录  --with-pgsql指定postgresql的目录
./configure --prefix=/app/pgpool2-4.2.8 --with-pgsql=/app/postgresql-13.6/

make && make install

3、安装pgpool-recovery

安装pgpool-recovery、在使用在线恢复时需要

cd /app/software/pgpool-II-4.2.8/src/sql/pgpool-recovery

make && make install

4、配置pgpool_node_id

配置pgpool_node_id文件、server1是0、server2是1、server3是2

vim /app/pgpool2-4.2.8/etc/pgpool_node_id

0

3、准备环境

以下操作全部在server1机器上执行、postgres用户

1、修改failover和follow_primary文件

# 复制failover故障转移和follow_primary故障转移后备机和新的主同步
cp /app/pgpool2-4.2.8/etc/failover.sh.sample /app/pgpool2-4.2.8/etc/failover.sh && \
cp /app/pgpool2-4.2.8/etc/follow_primary.sh.sample /app/pgpool2-4.2.8/etc/follow_primary.sh && \
cd /app/pgpool2-4.2.8/etc/ && \
chmod 700 failover.sh follow_primary.sh

vim /app/pgpool2-4.2.8/etc/failover.sh
# 修改以下配置
PGHOME=/app/postgresql-13.6

vim /app/pgpool2-4.2.8/etc/follow_primary.sh
# 修改以下配置
PGHOME=/app/postgresql-13.6
ARCHIVEDIR=/app/postgresql-13.6/archivedir
REPLUSER=repl
PCP_USER=pgpool
PGPOOL_PATH=/app/pgpool2-4.2.8/bin
primary_conninfo = 'host=${NEW_PRIMARY_NODE_HOST} port=${NEW_PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${NODE_HOST} passfile=''/app/postgresql-13.6/.pgpass'''

2、修改pcp.conf

cp /app/pgpool2-4.2.8/etc/pcp.conf.sample /app/pgpool2-4.2.8/etc/pcp.conf
# 生成pgpool用户和密码
echo 'pgpool:'`/app/pgpool2-4.2.8/bin/pg_md5 pgpool` >> /app/pgpool2-4.2.8/etc/pcp.conf

4、创建pcppass文件

echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass && chmod 600 ~/.pcppass

5、修改recovery_1st_stage和pgpool_remote_start

# 复制在线恢复脚本
cp /app/pgpool2-4.2.8/etc/recovery_1st_stage.sample /app/postgresql-13.6/data/recovery_1st_stage && cp /app/pgpool2-4.2.8/etc/pgpool_remote_start.sample /app/postgresql-13.6/data/pgpool_remote_start && chmod 700 /app/postgresql-13.6/data/recovery_1st_stage /app/postgresql-13.6/data/pgpool_remote_start

vim /app/postgresql-13.6/data/recovery_1st_stage
# 修改以下配置
PGHOME=/app/postgresql-13.6
ARCHIVEDIR=/app/postgresql-13.6/archivedir
primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''/app/postgresql-13.6/.pgpass'''

vim /app/postgresql-13.6/data/pgpool_remote_start
# 修改以下配置
PGHOME=/app/postgresql-13.6


# 执行
psql template1 -c "CREATE EXTENSION pgpool_recovery"

6、修改pgpool.conf

cp /app/pgpool2-4.2.8/etc/pgpool.conf.sample-stream /app/pgpool2-4.2.8/etc/pgpool.conf

vim /app/pgpool2-4.2.8/etc/pgpool.conf

# 修改以下配置
listen_addresses = '*'
port = 9999
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

backend_hostname0 = 'server1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/app/postgresql-13.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'

backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/postgresql-13.6/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'


backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/app/postgresql-13.6/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server3'

failover_command = '/app/pgpool2-4.2.8/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/app/pgpool2-4.2.8/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'
pid_file_name = '/app/pgpool2-4.2.8/pgpool.pid'
enable_pool_hba = on
use_watchdog = on
delegate_IP = '192.168.159.184'
# ens33是你的网卡名
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens33 label ens33:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens33'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33'
if_cmd_path = '/sbin'
arping_path = '/usr/sbin'

hostname0 = 'server1'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'server2'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'server3'
wd_port2 = 9000
pgpool_port2 = 9999

wd_lifecheck_method = 'heartbeat'
wd_interval = 10

heartbeat_hostname0 = 'server1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'server2'
heartbeat_port1 = 9694
heartbeat_device1 = ''

heartbeat_hostname2 = 'server3'
heartbeat_port2 = 9694
heartbeat_device2 = ''

wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30

wd_escalation_command = '/app/pgpool2-4.2.8/etc/escalation.sh'

log_destination = 'stderr'
logging_collector = on
log_directory = '/app/pgpool2-4.2.8/logs'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB

6、修改pool_hba.conf

cp /app/pgpool2-4.2.8/etc/pool_hba.conf.sample /app/pgpool2-4.2.8/etc/pool_hba.conf

vim /app/pgpool2-4.2.8/etc/pool_hba.conf
# 修改以下配置
host    replication all         0.0.0.0/0             md5
host    all         all         0.0.0.0/0             md5

7、生成pool_passwd文件

# 生成pgpool和postgres密码文件
/app/pgpool2-4.2.8/bin/pg_md5 -f /app/pgpool2-4.2.8/etc/pgpool.conf -m -u pgpool pgpool
/app/pgpool2-4.2.8/bin/pg_md5 -f /app/pgpool2-4.2.8/etc/pgpool.conf -m -u postgres postgres

8、修改escalation文件

# 复制看门狗的脚本
cp /app/pgpool2-4.2.8/etc/escalation.sh.sample /app/pgpool2-4.2.8/etc/escalation.sh && chmod 700 escalation.sh

vim /app/pgpool2-4.2.8/etc/escalation.sh
# 修改以下配置
VIP=192.168.159.184
DEVICE=ens33

9、创建logs目录

需要所有机器都执行

mkdir -p /app/pgpool2-4.2.8/logs

10、远程复制给其他机器

cd /app/pgpool2-4.2.8/etc
scp escalation.sh failover.sh follow_primary.sh pgpool.conf pcp.conf pool_hba.conf pool_passwd postgres@server2:/app/pgpool2-4.2.8/etc/

cd ~
scp  .pcppass postgres@server2:/home/postgres/

11、修改sudoers文件

切换成root、需要每台机器都修改

su root

vim /etc/sudoers
postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD:/usr/sbin/arping

12、配置自定义服务

需要每台机器都修改、操作用户是root

vim /usr/lib/systemd/system/pgpool.service

[Unit]
Description=Pgpool-II
After=syslog.target network.target

[Service]
Type=forking
User=postgres
Group=postgres
PIDFile=/app/pgpool2-4.2.8/pgpool.pid
ExecStart=/app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpool2-4.2.8/etc/pcp.conf
ExecStop=/app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpool2-4.2.8/etc/pcp.conf -m fast stop
ExecReload=/app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpool2-4.2.8/etc/pcp.conf reload

[Install]
WantedBy=multi-user.target

4、启动

# 启动服务
systemctl start pgpool
# 设置开机自启
systemctl enable pgpool
[root@server1 system]# systemctl status pgpool
● pgpool.service - Pgpool-II
   Loaded: loaded (/usr/lib/systemd/system/pgpool.service; disabled; vendor preset: disabled)
   Active: active (running) since 一 2022-05-23 19:17:08 CST; 4s ago
 Main PID: 30782 (pgpool)
   Memory: 131.2M
   CGroup: /system.slice/pgpool.service
           ├─30782 /app/pgpool2-4.2.8/bin/pgpool -f /app/pgpool2-4.2.8/etc/pgpool.conf -F /app/pgpoo...
           ├─30783 pgpool: PgpoolLogger
           └─30785 pgpool: watchdog

5月 23 19:17:08 server1 systemd[1]: Started Pgpool-II.
5月 23 19:17:08 server1 systemd[1]: Starting Pgpool-II...

5、加入postgresql备机

su postgres

cd /app/pgpool2-4.2.8/bin

# 使用在线恢复脚本加入postgres备机   后面的1和2就是你的pgpool_node_id
./pcp_recovery_node -h 192.168.159.184 -p 9898 -U pgpool -n 1
Password: 
pcp_recovery_node -- Command Successful
[postgres@server1 bin]$ ./pcp_recovery_node -h 192.168.159.184 -p 9898 -U pgpool -n 2
Password: 
pcp_recovery_node -- Command Successful

4、验证高可用

1、查看postgresql节点状态

psql -h 192.168.159.184 -p 9999 -U pgpool postgres -c "show pool_nodes"

# 可以看到现在server1是postgres主服务、2和3同步1的数据
node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | 0.333333  | primary | 0          | true              | 0                 |                   |                        | 2022-05-23 19:21:47
 1       | server2  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2022-05-23 19:21:47
 2       | server3  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2022-05-23 19:21:47
(3 rows)

2、查看watchdog状态

/app/pgpool2-4.2.8/bin/pcp_watchdog_info -h 192.168.159.184 -p 9898 -U pgpool

# 可以看到现在server1是主。2和3是备
server1:9999 Linux server1 server1 9999 9000 4 LEADER
server2:9999 Linux server2 server2 9999 9000 7 STANDBY
server3:9999 Linux server3 server3 9999 9000 7 STANDBY

停掉server1服务、然后再查看watchdog状态。

systemctl stop pgpool

# 现在是server2是主，3是备。1是宕机
server2:9999 Linux server2 server2 9999 9000 4 LEADER
server1:9999 Linux server1 server1 9999 9000 10 SHUTDOWN
server3:9999 Linux server3 server3 9999 9000 7 STANDBY

再启动server1服务、查看状态。

# server2是主，1和3是备
server2:9999 Linux server2 server2 9999 9000 4 LEADER
server1:9999 Linux server1 server1 9999 9000 7 STANDBY
server3:9999 Linux server3 server3 9999 9000 7 STANDBY

3、postgresql故障转移

psql -h 192.168.159.184 -p 9999 -U pgpool postgres -c "show pool_nodes"

# 现在server1是主
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | 0.333333  | primary | 0          | true              | 0                 |                   |                        | 2022-05-23 19:26:31
 1       | server2  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2022-05-23 19:26:31
 2       | server3  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2022-05-23 19:26:31

停掉server1的postgresql服务。

/app/postgresql-13.6/bin/pg_ctl stop -D /app/postgresql-13.6/data/

# 可以看到现在server2是主，server3是备。1是宕机
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | down   | 0.333333  | standby | 0          | false             | 0                 |                   |                        | 2022-05-23 19:27:42
 1       | server2  | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2022-05-23 19:27:42
 2       | server3  | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 | streaming         | async                  | 2022-05-23 19:27:47
 
 
 # 查看单节点信息。server3是t、备状态
[postgres@server1 bin]$ psql -h server3 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()"
 pg_is_in_recovery 
-------------------
 t
(1 row)
# server2是f、主节点
[postgres@server1 bin]$ psql -h server2 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()"
 pg_is_in_recovery 
-------------------
 f
(1 row)
 
 # 查看server2的同步信息
 [postgres@server1 bin]$ psql -h server2 -p 5432 -U pgpool postgres -c "select * from pg_stat_replication" -x
-[ RECORD 1 ]----+------------------------------
pid              | 31103
usesysid         | 16385
usename          | repl
application_name | server3
client_addr      | 192.168.159.183
client_hostname  | 
client_port      | 47512
backend_start    | 2022-05-23 19:27:43.700107+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/60001B8
write_lsn        | 0/60001B8
flush_lsn        | 0/60001B8
replay_lsn       | 0/60001B8
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2022-05-23 19:29:34.022772+08

4、在线恢复

# 使用在线恢复脚本恢复server1
[postgres@server1 bin]$ ./pcp_recovery_node -h 192.168.159.184 -p 9898 -U pgpool -n 0
Password: 
pcp_recovery_node -- Command Successful

# 可以看到server2是主，1和3是备
[postgres@server1 bin]$ psql -h 192.168.159.184 -p 9999 -U pgpool postgres -c "show pool_nodes"
Password for user pgpool: 
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | server1  | 5432 | up     | 0.333333  | standby | 0          | false             | 0                 | streaming         | async                  | 2022-05-23 19:31:02
 1       | server2  | 5432 | up     | 0.333333  | primary | 0          | false             | 0                 |                   |                        | 2022-05-23 19:27:42
 2       | server3  | 5432 | up     | 0.333333  | standby | 0          | true              | 0                 | streaming         | async                  | 2022-05-23 19:27:47
(3 rows)