postgres 数据库搭建集群 （主备模式）_pgpool-ii搭建集群

postgres 数据库搭建集群（主备模式）

一、实验环境

注意：服务器将selinux和防火墙关掉，如果需要开启，请添加对于的策略

二、软件版本

postgresSQL 12.5

pgpool-II 4.0.20 (此版本要和postgres数据库大版本对应)

三、整体架构

四、安装postgres数据库(主从节点均需要安装)

注意：此处我采用的离线安装！！！

[root@mater soft]# rpm -ivh postgresql12-*.rpm
警告：postgresql12-12.5-1PGDG.rhel7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID 442df0f8: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:postgresql12-libs-12.5-1PGDG.rhel################################# [ 25%]
   2:postgresql12-12.5-1PGDG.rhel7    ################################# [ 50%]
   3:postgresql12-server-12.5-1PGDG.rh################################# [ 75%]
   4:postgresql12-contrib-12.5-1PGDG.r################################# [100%]
[root@mater soft]#
[root@mater soft]# cat /etc/passwd | grep post
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
[root@mater soft]#
1
2
3
4
5
6
7
8
9
10
11
12
13

安装完成后会创建一个postgres用户

配置数据库

1、创建数据目录

[root@mater soft]# mkdir -p /data/pg_datamkdir -p /data/pg_data
[root@mater soft]# chown -R  postgres:root /data/pg_data/
[root@mater soft]# ll /data/
总用量 0
drwxr-xr-x 2 postgres root 6 10月 26 10:27 pg_data
1
2
3
4
5

2、修改配置文件

修改配置文件（root用户下操作，将其中的 PGDATA 修改为新的数据目录）/usr/lib/systemd/system/postgresql-12.service

/usr/pgsql-12/bin/postgresql-12-setup 配置文件中的数据存储目录修改

修改环境变量中的数据存储目录 vim /var/lib/pgsql/.bash_profile

3、初始化数据库

切换到postgres用户

[root@mater soft]# su - postgres 
-bash-4.2$ 
-bash-4.2$ /usr/pgsql-12/bin/initdb 
属于此数据库系统的文件宿主为用户 "postgres".
此用户也必须为服务器进程的宿主.
数据库簇将使用本地化语言 "zh_CN.UTF-8"进行初始化.
默认的数据库编码已经相应的设置为 "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
缺省的文本搜索配置将会被设置到"simple"

禁止为数据页生成校验和.

修复已存在目录 /data/pg_data 的权限 ... 成功
正在创建子目录 ... 成功
选择动态共享内存实现 ......posix
选择默认最大联接数 (max_connections) ... 100
选择默认共享缓冲区大小 (shared_buffers) ... 128MB
selecting default time zone ... Asia/Shanghai
创建配置文件 ... 成功
正在运行自举脚本 ...成功
正在执行自举后初始化 ...成功
同步数据到磁盘...成功

initdb: 警告: 为本地连接启用"trust"身份验证
你可以通过编辑 pg_hba.conf 更改或你下次
执行 initdb 时使用 -A或者--auth-local和--auth-host选项.

成功。您现在可以用下面的命令开启数据库服务器：

    /usr/pgsql-12/bin/pg_ctl -D /data/pg_data -l 日志文件 start

-bash-4.2$
#######
修改以下数据目录的属组
[root@mater soft]# chown -R  postgres:root /data/pg_data
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

4、修改数据库配置文件

vim /data/pg_data/postgresql.conf

vim /data/pg_data/pg_hba.conf 修改为所有IP可访问 md5加密方式

host all all 0.0.0.0/0 md5

5、重启服务，设置为开机自启

[root@mater ~]# systemctl daemon-reload
[root@mater ~]# systemctl enable postgresql-12.service --now
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
[root@mater ~]#
1
2
3
4

查看服务状态

查看监听状态

[root@mater ~]# netstat -ltunp | grep post
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      49752/postmaster    
tcp6       0      0 :::5432                 :::*                    LISTEN      49752/postmaster    
[root@mater ~]#
1
2
3
4

6、修改数据库密码

切换到postgres用户

-bash-4.2$ psql
psql (12.5)
输入 "help" 来获取帮助信息.
postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=#
1
2
3
4
5
6

7、测试登录

8、从节点安装postgres数据库，同上

五、主节点配置

5.1 创建用于主从访问的用户，用于远程登录。(切换到postgres用户操作)

create role 用户 login replication encrypted password ‘用户密码’；

-bash-4.2$ psql
psql (12.5)
输入 "help" 来获取帮助信息.

postgres=# create role privateuser login replication encrypted password 'public';
CREATE ROLE
postgres=#
1
2
3
4
5
6
7

5.2 修改pg_hba.conf和postgresql.conf配置

vim /data/pg_data/pg_hba.conf

##添加以下内容

host replication privateuser 192.168.137.121/24 trust
host replication privateuser 192.168.137.122/24 trust
host all all 192.168.137.0/24 md5

vim /data/pg_data/postgresql.conf

wal_level = replica

max_wal_senders = 10

wal_keep_segments = 10240

max_connections = 512

5.3 重启主节点

建议先停止在启动

[root@mater etc]# systemctl stop postgresql-12.service 
[root@mater etc]# systemctl start postgresql-12.service 
[root@mater etc]#
1
2
3

六、从节点配置

6.1 切换postgres用户

su - postgres

6.2 对主节点数据备份至从节点

-bash-4.2$ rm -rf /data/pg_data/*
-bash-4.2$ pg_basebackup -h 192.168.137.121 -U privateuser -D /data/pg_data/ -X stream -P
25311/25311 kB (100%), 1/1 表空间
-bash-4.2$ 
1
2
3
4

6、3 修改postgresql.conf文件

max_connections = 512 #从库的 max_connections要大于主库

max_connections = 1024

###再最后添加以下配置

primary_conninfo = 'host=192.168.137.121 port=5432 user=privateuser password=public'
recovery_target_timeline = 'latest'
promote_trigger_file = '/tmp/promote_trigger_file0'
1
2
3

开启standby模式

hot_standby = on

6.3 进行备份和恢复的配置

注意：在pg10版本，是通过recovery.conf文件来进行，但在12版本进行了修改

需要用到两个文件 recovery.signal standby.signal

-bash-4.2$ touch  /data/pg_data/recovery.signal
-bash-4.2$ touch  /data/pg_data/standby.signal
-bash-4.2$ echo standby_mode= 'on'  >> /data/pg_data/standby.signal
1
2
3

6.4 退出postgres用户，重启pg数据库

[root@node1 ~]# systemctl restart postgresql-12.service 
[root@node1 ~]# 
[root@node1 ~]# 
[root@node1 ~]# systemctl status postgresql-12.service 
● postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: active (running) since 三 2022-10-26 13:29:01 CST; 11s ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 41095 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 41102 (postmaster)
    Tasks: 7
1
2
3
4
5
6
7
8
9
10
11

七、验证主从

7.1主节点执行以下命令

-bash-4.2$ psql 
psql (12.5)
输入 "help" 来获取帮助信息.

postgres=# 
postgres=# 
postgres=# select client_addr,sync_state from pg_stat_replication;
   client_addr   | sync_state 
-----------------+------------
 192.168.137.122 | async
(1 行记录)

postgres=#
1
2
3
4
5
6
7
8
9
10
11
12
13

7.2 读写测试

在主节点写数据，从节点读数据

postgres=# create database test;
CREATE DATABASE
postgres=# 
postgres=# \l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
`test      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |`
(4 行记录)

postgres=#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

从节点上查看主节点创建的数据库

[root@node1 ~]# su - postgres 
上一次登录：三 10月 26 10:56:25 CST 2022pts/2 上
-bash-4.2$ 
-bash-4.2$ 
-bash-4.2$ 
-bash-4.2$ psql 
psql (12.5)
输入 "help" 来获取帮助信息.

postgres=# \l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 `test      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |`
(4 行记录)

postgres=#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

八、postgres用户之间免密登录

8.1 修改postgres用户的密码 为postgres

[root@node1 ~]# passwd postgres 
更改用户 postgres 的密码 。
新的 密码：
无效的密码： 密码包含用户名在某些地方
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
[root@node1 ~]# 
[root@node1 ~]#
1
2
3
4
5
6
7
8

8.2 生成同步密钥

-bash-4.2$ `ssh-keygen`
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa): 
Created directory '/var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:5i65z5nXx37HMk7ufHcVcOBBRevk170fd9jJ9/vBCa8 postgres@mater
The key's randomart image is:
+---[RSA 2048]----+
|            .++o |
|            .....|
|             .oo |
|              +.o|
|        S    . o=|
|       o      =o*|
|       ..  . .o@*|
|      oo o. .+* /|
|      .+*.   E**X|
+----[SHA256]-----+
-bash-4.2$ `ssh-copy-id 192.168.137.121`
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_rsa.pub"
The authenticity of host '192.168.137.122 (192.168.137.122)' can't be established.
ECDSA key fingerprint is SHA256:o0mof75DjkEyMTWAefhjyGVWLCbimiLvlcwayGd7RiM.
ECDSA key fingerprint is MD5:6b:5d:be:8e:36:51:d7:a5:33:d2:73:65:0b:c6:66:ec.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@192.168.137.122's password: `输入密码`

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.137.122'"
and check to make sure that only the key(s) you wanted were added.

-bash-4.2$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

8.3 测试免密登录

-bash-4.2$ `ssh 192.168.137.122`
Last login: Wed Oct 26 13:33:24 2022
-bash-4.2$ 
-bash-4.2$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: `ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    `link/ether 00:50:56:32:15:65 brd ff:ff:ff:ff:ff:ff
    `inet 192.168.137.122/24 brd 192.168.137.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::e85a:359c:c9d3:16fc/64 scope link tentative noprefixroute dadfailed 
       valid_lft forever preferred_lft forever
    inet6 fe80::9e1b:be54:58a2:5d2e/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:55:9d:d8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:55:9d:d8 brd ff:ff:ff:ff:ff:ff
-bash-4.2$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

8.4 在从主机上也是如此操作

-bash-4.2$ ssh 192.168.137.121
Last failed login: Wed Oct 26 13:43:36 CST 2022 from 192.168.137.122 on ssh:notty
There were 3 failed login attempts since the last successful login.
Last login: Wed Oct 26 13:30:49 2022
1
2
3
4

九、安装pgpool

(我通过离线安装的)

9.1 安装（两台均需要安装）

[root@node1 home]# rpm -ivh *.rpm
警告：libmemcached-1.0.16-5.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID f4a80eb5: NOKEY
警告：pgpool-II-pg12-4.0.20-1pgdg.rhel7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID 60ae0e48: NOKEY
准备中...                          ################################# [100%]
        软件包 libmemcached-1.0.16-5.el7.x86_64 已经安装
[root@node1 home]# rpm -ivh pgpool-II-pg12-*
警告：pgpool-II-pg12-4.0.20-1pgdg.rhel7.x86_64.rpm: 头V4 RSA/SHA1 Signature, 密钥 ID 60ae0e48: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:pgpool-II-pg12-4.0.20-1pgdg.rhel7################################# [ 25%]
   2:pgpool-II-pg12-devel-4.0.20-1pgdg################################# [ 50%]
   3:pgpool-II-pg12-extensions-4.0.20-################################# [ 75%]
   4:pgpool-II-pg12-debuginfo-4.0.20-1################################# [100%]
[root@node1 home]#
1
2
3
4
5
6
7
8
9
10
11
12
13
14

9.2 修改配置（两台均需修改）

pool_hba.conf和之前配置的PostgreSQL中的配置时一样的

vim /etc/pgpool-II/pool_hba.conf

9.3 对postgres的密码进行加密

[root@mater pgpool-II]# pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5
[root@mater pgpool-II]# vim /etc/pgpool-II/pcp.conf
postgres:e8a48653851e28c69d0506508fb27fc5
1
2
3
4

[root@mater pgpool-II]# pg_md5 -m -p -u postgres pool_passwd
password: 
[root@mater pgpool-II]#
#####切换到postgres用户
执行上面的操作
如遇到报错
-bash-4.2$ pg_md5 -m -p -u postgres pool_passwd
password: 
ERROR: pid 29514: initializing pool password, failed to open file:"/etc/pgpool-II/pool_passwd"
在root用户给文件一个权限，如下
[root@mater pgpool-II]# chown postgres:root /etc/pgpool-II/pool_passwd
##再次测试
-bash-4.2$ pg_md5 -m -p -u postgres pool_passwd
password: 
-bash-4.2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

十、修改集群配置

1. 修改监听地址，将localhost改为*，即监听所有地址发来的请求。

vim /etc/pgpool-II/pgpool.conf

listen_addresses = '*'

1
2

2. 修改backend相关参数，对应的是PostgreSQ两个节点的相关信息。

backend_hostname0 = '192.168.137.121'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/pg_data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
# or ALWAYS_MASTER
backend_hostname1 = '192.168.137.122'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/pg_data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
1
2
3
4
5
6
7
8
9
10
11

3. pg_hba.conf生效

enable_pool_hba = on
1

4. 使负载均衡生效

load_balance_mode = on
1

5. 主从流复制生效，并配置用于检查的用户，这个用户就用上方创建的用于主从访问的用户

master_slave_mode = on
sr_check_period = 6
sr_check_user = 'privateuser'
sr_check_password = 'public'
sr_check_database = 'postgres'
delay_threshold = 1000000
1
2
3
4
5
6

6. 健康检查相关配置，并配置用于检查的用户，这个用户就用上方创建的用于主从访问的用户。

health_check_period = 10
health_check_timeout = 20
health_check_user = 'privateuser'
health_check_password = 'public'
health_check_database = 'postgres'
1
2
3
4
5

7. 配置主机故障触发执行的脚本。 (未做)

   chown -R postgres.postgres /opt/pgpool

   chmod 777 /opt/pgpool/failover_stream.sh

failover_command = '/var/lib/pgsql/10/failover_stream.sh %d %H'
###脚本内容
#!/bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA"
# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
exit 0;
1
2
3
4
5
6
7
8
9
10

8. 开启开门狗，IP为本机IP

use_watchdog = on
wd_hostname = '192.168.137.121'
1
2

9. 开启虚拟IP，并修改网卡信息。启动后直接使用虚拟IP进行数据库操作,将网卡名字ens33修改为自己对应的网卡名

delegate_IP = '192.168.137.100'
if_up_cmd = 'ip addr add $_IP_$/24 dev ens33 label ens33:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev ens33'
arping_cmd = 'arping -U $_IP_$ -w 1 -I ens33'
1
2
3
4

10. 心跳检查的配置与看门狗配置。IP为从节点的IP

heartbeat_destination0 = '192.168.137.122'
heartbeat_device0 = 'ens33'
other_pgpool_hostname0 = '192.168.137.122'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
1
2
3
4
5

从节点的配置只需要修改 8 和 10 对于的IP

十一、文件权限修改

两个节点都操作

[root@node1 pgpool-II]# chown -R postgres:root /etc/pgpool-II
[root@node1 pgpool-II]# mkdir /var/log/pgpool
[root@node1 pgpool-II]# chown -R postgres:root /var/log/pgpool
[root@node1 pgpool-II]# chown -R postgres:root /var/log/pgpool
[root@node1 pgpool-II]# mkdir /var/run/pgpool
mkdir: 无法创建目录"/var/run/pgpool": 文件已存在
[root@node1 pgpool-II]# chown -R postgres:root /var/run/pgpool
[root@node1 pgpool-II]#
1
2
3
4
5
6
7
8

十二、运行pgpool

[root@mater pgpool-II]# systemctl enable pgpool.service --now
Created symlink from /etc/systemd/system/multi-user.target.wants/pgpool.service to /usr/lib/systemd/system/pgpool.service.
[root@mater pgpool-II]#
1
2
3

12.1 登录虚拟IP节点查看集群节点

[root@mater home]# psql -p 9999 -h 192.168.137.100 -U postgres
用户 postgres 的口令：
psql (12.5)
输入 "help" 来获取帮助信息.

postgres=# show pool_nodes;
 node_id |    hostname     | port | status | lb_weight |  role   | select_cnt | load_balance_node | replic
ation_delay | last_status_change  
---------+-----------------+------+--------+-----------+---------+------------+-------------------+-------
------------+---------------------
 0       | 192.168.137.121 | 5432 | up     | 0.500000  | primary | 0          | false             | 0     
            | 2022-10-26 14:34:04
 1       | 192.168.137.122 | 5432 | up     | 0.500000  | standby | 0          | true              | 0     
            | 2022-10-26 14:34:04
(2 行记录)

postgres=# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17