【PostgreSQL】流复制主从集群搭建

前言

最近要给客户部署一套postgresql生产环境，规划是采用一主两从的流复制架构搭建集群。记录本次操作过程及遇到的一些问题，留作参考。

环境

集群配置

主机	角色	OS	db
192.168.0.111	主	Ubuntu20	postgresql14
192.168.0.112	备	Ubuntu20	postgresql14
192.168.0.113	备	Ubuntu20	postgresql14

3台主机完成网络配置，可以访问外网，互相可以通信 

辅助工具：screen,keepalive,net-tools

软件安装，过程不再赘述，用下面的命令直接安装就可以了

apt-get update
apt-get install screen
apt-get -y install keepalived
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-14

【可选】初始化postgres用户的密码 12345

passwd postgres
##根据提示输入两次相同的初始密码完成修改

 至此演示环境软硬件环境完成准备。

关于screen工具，这个是用于远程操作时在服务器上打开的后台终端，可以防止本地与服务器连接断开时，服务器上的进程不断，简单介绍一下使用方法如下：

screen -ls #查看已有的服务器后台screen终端进程

screen -S 自定义终端名 #创建一个新screen终端

screen -dr  终端名 #进入一个已有的screen终端，如果这个终端被其他用户使用，强行结束他人的进程并进入

在screen终端内要退出按Ctrl键+a+d,这样退出下次再进去的时候原来在这个screen终端中运行的进程都还在

集群配置过程

操作过程中需要监控系统日志可以另开一个终端，并在这个终端上执行下面的语句

tail -f /var/log/postgresql postgresql-14-main.log #可以观察数据库启停的一些信息

tail -f /var/log/syslog #可以观察所有linux的操作信息

【主库(192.168.0.111)】部署 

 首先对【主库(192.168.0.111)】进行配置

主库的外网访问权限配置【这一步备库也要执行】

su - postgres
psql --version
# set database network
cp /etc/postgresql/14/main/pg_hba.conf /etc/postgresql/14/main/pg_hba.conf.bak
echo "host all all 0.0.0.0/0 scram-sha-256" >>/etc/postgresql/14/main/pg_hba.conf
export PGDATA=/var/lib/postgresql/14/main
mkdir $PGDATA
mkdir /var/lib/postgresql/pdump
mkdir /var/lib/postgresql/pdump/log
echo "export PATH="$PATH":/usr/lib/postgresql/14/bin"  >> ~/.bash_login
echo "export PGDATA="$PGDATA >> ~/.bash_login
source ~/.bash_login
vi /etc/postgresql/14/main/pg_hba.conf
 

# 把local相关的几个认证加密方式修改为trust

systemctl restart postgresql

host all all 0.0.0.0/0 scram-sha-256就是对网段为192.168的客户机开放数据库访问权限，scram-sha-256是认证加密方式，推荐这个 

【问题1】这里重启数据库服务的时候失败了，查看syslog日志看到如下报错

Apr 26 03:35:04 host111 postgresql@14-main[30935]: 2022-04-26 03:35:04.548 UTC [30953] LOG:  invalid CIDR mask in address "192.168.0.0/32 scram-sha-256"
Apr 26 03:35:04 host111 postgresql@14-main[30935]: 2022-04-26 03:35:04.548 UTC [30953] CONTEXT:  line 106 of configuration file "/etc/postgresql/14/main/pg_hba.conf"
Apr 26 03:35:04 host111 postgresql@14-main[30935]: 2022-04-26 03:35:04.548 UTC [30953] FATAL:  could not load pg_hba.conf
Apr 26 03:18:11 host111 postgresql@14-main[30780]: 2022-04-26 03:18:11.344 UTC [30798] FATAL:  could not load pg_hba.conf
Apr 26 03:18:11 host111 postgresql@14-main[30780]: 2022-04-26 03:18:11.345 UTC [30798] LOG:  database system is shut down
Apr 26 03:18:11 host111 postgresql@14-main[30780]: pg_ctl: could not start server
Apr 26 03:18:11 host111 postgresql@14-main[30780]: Examine the log output.
Apr 26 03:18:11 host111 systemd[1]: postgresql@14-main.service: Can't open PID file /run/postgresql/14-main.pid (yet?) after start: Operation not permitted
Apr 26 03:18:11 host111 systemd[1]: postgresql@14-main.service: Failed with result 'protocol'.
Apr 26 03:18:11 host111 systemd[1]: Failed to start PostgreSQL Cluster 14-main.

 检查了一下，发现host all all 192.168.0.0/32 scram-sha-256 不符合，改成

host all all 0.0.0.0/0 scram-sha-256 再重启就好了

主库的性能调优和初始化

psql
alter system set listen_addresses = '*';
alter system set shared_buffers = '1GB';
alter system set temp_buffers = '64MB';
alter system set work_mem = '8MB';
alter system set max_connections=200;
alter system set password_encryption='scram-sha-256';
\q

# 由于修改了加密方式所以密码都要重新设置一下，否则可能登录不了

systemctl restart postgresql
psql
alter user postgres password '220425';
# 【可选】初始化数据库
CREATE ROLE "admin" SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN PASSWORD 'app';
CREATE TABLESPACE bomtbs OWNER admin LOCATION '/var/lib/postgresql/14/data';
create database prjbom owner admin TABLESPACE bomtbs;
GRANT ALL PRIVILEGES ON DATABASE prjbom TO admin;
\q

 listen_addresses = '*'表示允许所有外网用户访问

shared_buffers推荐为物理内存的25%左右

temp_buffers，work_mem分别为临时段和会话所用的内存，不同的业务场景需要的内存不同

重启数据库服务并验证

systemctl restart postgresql
systemctl status postgresql
psql -h 192.168.0.111 -p 5432 -d prjbom -U admin -c 'select 1 n;';

#出现下面的内容，说明我们的初始配置就成功了

n
------------

           1

至此我们的主库如下：

#数据库服务器：192.168.0.111 端口：5432 
#项目数据库名：prjbom
#数据库默认表空间：bomtbs
#dump文件目录：/var/lib/postgresql/pdump
#数据库用户名：admin    登录口令：app
#OS用户名：postgres 登录口令：12345
#super用户名：postgres    登录口令：220425 

接下来，我们开始集群相关的参数设置，首先还是在【主库】上操作 

su - postgres
mkdir /var/lib/postgresql/14/archivelog
chmod -R 0700 /var/lib/postgresql/14/archivelog
echo "host replication replica 192.168.0.0/32 scram-sha-256" >>/etc/postgresql/14/main/pg_hba.conf
echo "host replication replica 192.168.0.0/24 scram-sha-256" >>/etc/postgresql/14/main/pg_hba.conf
systemctl restart postgresql

上面新增了一个归档用的文件目录和流复制用户相关的数据库访问权限，这里也主要要重启一下数据库服务确保规则更新到数据库中，接下来创建流复制相关的用户，用户名 replica，这个名字不是固定的，你也可以取别的名字，但是要和上面访问权限配置用的一致。

【问题】见备库部署【问题描述1】

psql 
create role replica login replication encrypted password 'replica';
alter system set wal_level = 'replica';
alter system set max_wal_senders = 10;
alter system set max_replication_slots=10;
alter system set wal_keep_size = 10240;# MB
alter system set wal_sender_timeout = '60s';
alter system set synchronous_standby_names = '*' ;
alter system set synchronous_commit = local;
alter system set archive_mode =on;
alter system set archive_command = 'cp %p /var/lib/postgresql/14/archivelog/%f';
\q

重启并验证,这里要注意的是重启成功这个主库就称为集群中的主库了

systemctl restart postgresql
systemctl status postgresql
psql -h 192.168.0.111 -p 5432 -d prjbom -U replica -c 'select 1 n;'; 

postgres@host111:~$ psql -h 192.168.0.111 -p 5432 -d prjbom -U replica -c 'select 1 n;'; 
Password for user replica: 
 n 
---
 1
(1 row)

之后在主库上可以执行下面的命令来检查备库的情况

select * from pg_stat_replication; 

至此，集群中的主库我们就配置好了。

【备库192.168.0.112，192.168.0.113】 部署

接下来【备库】的配置，下面所有操作在所有备库上都是一样的。

首先，关闭【备库】

su - postgres
systemctl stop postgresql

postgres@host112:~$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Tue 2022-04-26 05:36:35 UTC; 7min ago
   Main PID: 1078 (code=exited, status=0/SUCCESS)

初始化环境变量和数据库目录【权限配置同主库上】

export PGDATA=/var/lib/postgresql/14/main
echo "export PATH="$PATH":/usr/lib/postgresql/14/bin" >> ~/.bash_login
echo "export PGDATA="$PGDATA >> ~/.bash_login
source ~/.bash_login

目录我们还是选择默认的postgres目录，修改后可能会有问题解决起来比较痛苦。

如果安装数据库的时候是连postgres数据库一起初始化的，则还要先删除默认的数据库目录，执行下列脚本

#为了防止误操作，如果是首次部署，则执行一次，以后无需执行
cp -r /var/lib/postgresql/14/main /var/lib/postgresql/14/main_bak1
cp -r /var/lib/postgresql/14/data /var/lib/postgresql/14/data_bak1

部署脚本，注意这里新建的目录必须给它700权限，否则无法加入集群

rm -rf /var/lib/postgresql/14/main
rm -rf /var/lib/postgresql/14/data
mkdir $PGDATA
mkdir /var/lib/postgresql/14/data
mkdir /var/lib/postgresql/14/archivelog
chmod -R 0700 $PGDATA
chmod -R 0700 /var/lib/postgresql/14/data
chmod -R 0700 /var/lib/postgresql/14/archivelog

复制主库到当前备库

pg_basebackup -h 192.168.0.111 -p 5432 -U replica -Fp -Xs -Pv -R -D $PGDATA 

【问题描述1】执行时报错如下：

pg_basebackup: error: connection to server at "192.168.0.111", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.0.119", user "replica", SSL encryption

 分析：主库的pg_hba.conf已经加入了相关的访问权限，但是用的是具体的IP,检查了一下备库

ip a
--------------------------------------------------------------------
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 08:00:27:60:7f:66 brd ff:ff:ff:ff:ff:ff
    inet 192.168.0.112/32 scope global enp0s3
       valid_lft forever preferred_lft forever
    inet 192.168.0.119/24 brd 192.168.0.255 scope global dynamic enp0s3
       valid_lft 1800sec preferred_lft 1800sec
    inet6 fe80::a00:27ff:fe60:7f66/64 scope link 
       valid_lft forever preferred_lft forever

因为我这个是虚拟机环境，这里看到有两个ip，估计是走了另外一个ip。

解决办法：修改【主库】上的pg_hba.conf

把replica用户的访问权限修改为ip段 如下所示

host replication replica 192.168.0.0/32 scram-sha-256
host replication replica 192.168.0.0/24 scram-sha-256

重启主库数据库，复制成功。

这里要引申出去说一下，如果备库不是这个网段的，则每次都需要重新修改主库配置，这个很麻烦，但是用0.0.0.0/0则不确定安全性如何，有点纠结。 

【问题描述2】复制过程中总是报连接断开错误，主机文件无法正常复制到其中一台备机，丢失WAL。

【解决方案】网上搜了很多描述都没有解决这个问题，尝试下面的方法手工把另一台部署成功的备机上的文件复制到问题备机
scp -r 192.168.0.112:/var/lib/postgresql/14/main /var/lib/postgresql/14
scp -r 192.168.0.112:/var/lib/postgresql/14/data /var/lib/postgresql/14
启动服务成功，检测同步正常。

 重启【备库】数据库服务，优化参数

systemctl start postgresql
psql
alter system set restore_command='cp /var/lib/postgresql/14/archivelog/%f  %p';
systemctl restart postgresql

验证，在【主库】上检查备份节点

 select * from pg_stat_replication;

#正常会看到刚才新加的备份库，状态为streaming
create table t_demo1(id int);

 【备库】为只读库

postgres@host112:~$ psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1+b1))
Type "help" for help.

postgres=# create table t_demo2(id int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction

查询刚才在【主库】上新建的表

postgres=# select * from t_demo1;
 id 
----
(0 rows)

 至此，第一个备库已加入集群，且同步正常。

按照上面的流程，将另一台备机加入集群。

关于一主两从的集群搭建介绍完毕。下一节我们将介绍在此基础上使用keepalive工具实现虚拟IP对外提供数据库访问。

补充：定时自动备份

集群部署完成后，如果是生产环境还需要定时备份，以下为按照每天一次完整备份的策略制定备份计划。

创建自动备份脚本 backup_pg.sh,脚本内容如下：

#!/bin/bash
BACKUP_DIR=/var/lib/postgresql/backup
LOGFILE=$BACKUP_DIR/log/backup.log
cur_time=$(date '+%Y-%m-%d')
sevendays_time=$(date -d -7days '+%Y-%m-%d')
export PGPASSWORD=220425
echo -e `date +"%F %T"` "Starting Backup PostgreSQL ..." >> $LOGFILE
echo -e `date +"%F %T"` "Clear dmp files..." `rm -rf $BACKUP_DIR/pgsql-backup.*.dmp` >>$LOGFILE 2>&1
echo -e `date +"%F %T"` "pg_dumpall...." `pg_dumpall -U postgres -p 5432 > "$BACKUP_DIR/pgsql-backup.$cur_time.dmp"` >>$LOGFILE 2>&1
echo -e `date +"%F %T"` "Starting TAR FILE ..." `tar zcvf "$BACKUP_DIR/pgsql-backup.$cur_time.tar.gz" *.dmp` >>$LOGFILE 2>&1
echo -e `date +"%F %T"` "Finish Backup ...">> $LOGFILE

# 创建必要的目录和授权

su - postgres

mkdir /var/lib/postgresql/backup

mkdir /var/lib/postgresql/backup/log

chmod +x backup_pg.sh

创建执行计划

# crontab -e
# 输入以下内容，表示每天凌晨1点自动运行备份脚本
0 1 * * * /var/lib/postgresql/backup/backup_pg.sh