postgres 数据库搭建集群（主备模式）
一、实验环境
| 虚拟机名 | IP | 主从划分 |
|---|---|---|
| master（centos7.8） | 192.168.137.121 | 主节点 |
| node1（centos7.8） | 192.168.137.122 | 从节点 |
| vip | 192.168.137.100 | 虚拟IP |
注意：服务器将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]#
安装完成后会创建一个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
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
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 ~]#
查看服务状态
查看监听状态
[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 ~]#
6、修改数据库密码
切换到postgres用户
-bash-4.2$ psql
psql (12.5)
输入 "help" 来获取帮助信息.
postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=#
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=#
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]#
六、从节点配置
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$
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'
开启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
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
七、验证主从
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=#
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=#
从节点上查看主节点创建的数据库
[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=#
八、postgres用户之间免密登录
8.1 修改postgres用户的密码 为postgres
[root@node1 ~]# passwd postgres
更改用户 postgres 的密码 。
新的 密码：
无效的密码： 密码包含用户名在某些地方
重新输入新的 密码：
passwd：所有的身份验证令牌已经成功更新。
[root@node1 ~]#
[root@node1 ~]#
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$
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$
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
九、安装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]#
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
[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
十、修改集群配置
- 修改监听地址，将localhost改为*，即监听所有地址发来的请求。
vim /etc/pgpool-II/pgpool.conf
listen_addresses = '*'
- 修改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'
- pg_hba.conf生效
enable_pool_hba = on
- 使负载均衡生效
load_balance_mode = on
- 主从流复制生效，并配置用于检查的用户，这个用户就用上方创建的用于主从访问的用户
master_slave_mode = on
sr_check_period = 6
sr_check_user = 'privateuser'
sr_check_password = 'public'
sr_check_database = 'postgres'
delay_threshold = 1000000
- 健康检查相关配置，并配置用于检查的用户，这个用户就用上方创建的用于主从访问的用户。
health_check_period = 10
health_check_timeout = 20
health_check_user = 'privateuser'
health_check_password = 'public'
health_check_database = 'postgres'
配置主机故障触发执行的脚本。 (未做)
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;
- 开启开门狗，IP为本机IP
use_watchdog = on
wd_hostname = '192.168.137.121'
- 开启虚拟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'
- 心跳检查的配置与看门狗配置。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
从节点的配置只需要修改 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]#
十二、运行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]#
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=#
扫一扫
775
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
