PostgreSQl 12主从流复制

一、环境
系统：Centos7.8

镜像源：阿里云PostgreSQL12

二、安装前准备
准备两台centos7.8的机器，以 192.168.40.131 主 192.168.40.132 从为例，下面简称主131 和从132

安装前需要做一些系统优化，不做可能导致初始化失败或者主从同步失败

修改添加limit的配置，临时设置的 ulimit -s 102400 这种在初始化时也会失败，需要重启持久化到配置文件。

vim /etc/security/limits.conf * soft stack 102400

• hard stack 102400

重启服务器后，执行 ulimit -a 查看，如果没能生效检查下 .bashrc 或 /etc/profile ，这里过小会导致数据库初始化失败。

关闭selinux

sed -i "s/^SELINUX=.*/SELINUX=disable/g" /etc/selinux/config
setenforce 0

检查本地防火墙是否有开启,可以根据实际情况进行关闭

systemctl status firewalld
systemctl status ipatbles # centos7中正常不存在

三、安装PostgreSQL12
下面内容摘自 一：PostgreSQL12安装

创建镜像源文件

vim /etc/yum.repos.d/pgsql12.repo

[pgdg12]
name=PostgreSQL 12 for RHEL/CentOS $releasever - $basearch
baseurl=https://mirrors.aliyun.com/postgresql/repos/yum/12/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/postgresql/repos/yum/RPM-GPG-KEY-PGDG
repo_gpgcheck = 1

安装pgsql

yum install postgresql12-server-12.9 -y

检查

[root@dev-k8s-01-6-68 ~]# rpm -qa|grep postgres
postgresql12-server-12.9-1PGDG.rhel7.x86_64
postgresql12-devel-12.9-1PGDG.rhel7.x86_64
postgresql12-12.9-1PGDG.rhel7.x86_64
postgresql12-libs-12.9-1PGDG.rhel7.x86_64

如果缺少 postgresql12-devel，可以执行以下命令

yum install centos-release-scl-rh -y
yum install llvm-toolset-7-clang -y
yum install postgresql12-devel.x86_64 -y

四、主131服务器配置

编辑pgsql初始化前的配置文件

vi /usr/lib/systemd/system/postgresql-12.service

编辑服务配置 修改数据库目录位置,根据实际情况来

Environment=PGDATA=/var/lib/pgsql/12/data

初始化数据库

/usr/pgsql-12/bin/postgresql-12-setup initdb

初始化完成之后，会在/var/lib/pgsql/12/data 生成pgsql主的配置文件 postgresql.conf，修改主的配置文件

vim postgresql.conf

允许来自任何主机的连接

listen_addresses = '*'

最大连接数，从库的max_connections必须要大于主库的

max_connections = 100

添加如下配置项

设置主pgsql为生成wal的主机

wal_level = replica
max_wal_senders = 32 # 同步最大的进程数量
wal_sender_timeout = 60s # 流复制主机发送数据的超时时间

默认值为“80MB”，该值通常太小，很容易导致备库失效，也需要设置得大一些

min_wal_size = 800MB

修改pg_hba.conf，为从132添加权限

vim /var/lib/pgsql/12/data/pg_hba.conf
host replication replica 192.168.40.132/32 md5 #也可以将md5改为 scram-sha-256，据说这个算法更安全
host all all 0.0.0.0/0 md5 # 这个视情况添加

启动pgsql 并加入开机自启

systemctl start postgresql-12.service && systemctl enable postgresql-12.service

创建一个账户密码，方便从服务器接收数据

su - postgres # 切换postgres 用户
psql -U postgres # 登陆pgsql
create role replica with replication login password '123456';
alter user replica with password '123456';

五、从132服务器配置

从132在安装完pgsql后无需初始化，直接通过命令可以将主131的配置拷贝过来

注意的是，下面命令不可直接粘贴执行，需要进行替换

pg_basebackup -h 192.168.40.131 -p 5432 -U replica -Fp -Xs -Pv -R -D /var/lib/pgsql/12/data/

其他参数：

-h 启动的主库数据库地址
-p 主库数据库端口
-U 流复制用户
-W 使用密码验证，要用repl的密码 pg_basebackup 的 -W 使用密码验证；小写 -w 是免密登陆，主库的 pg_hba.conf 中流复制 METHOD 选择 trust
-Fp 备份输出正常的数据库目录
-Xs 使用流复制的方式进行复制
-Pv 输出复制过程的详细信息
-R 为备库创建recovery.conf文件。但是pgsql 10以后的新版本的pgsql不需要这个文件了。-R则是用于创建用于replication的配置文件，其实就是生成$PGDATA/standby.signal文件。如果执行pg_basebackup的时候忘记加-R这个参数了，可以手动在备库的$PGDATA下touch standby.signal
同时在postgresql.auto.conf中生成对应的primary_conninfo参数信息
-D 指定创建的备库的数据库目录

修改pg_hba.conf，为主131主添加访问132从的权限

vim /var/lib/pgsql/12/data/pg_hba.conf

host replication replica 192.168.40.131/32 md5

修改从132的配置文件

vim /var/lib/pgsql/12/data/postgresql.conf

标明去主131同步的ip，端口，账号，密码

primary_conninfo = '192.168.40.131 port=5432 user=replica password=123456'
recovery_target_timeline = 'latest'
hot_standby = on

修改从132的文件夹权限和属主属组，否则无法同步

chown -R postgres:postgres /var/lib/pgsql/12/data
chmod -R 0700 /var/lib/pgsql/12/data

启动从132的pgsql并加入开机自启

systemctl start postgresql-12.service && systemctl enable postgresql-12.service

如果从同步过程中出现问题，想推倒重来，可以按照以下步骤执行

停止132从的pgsql服务
删除同步过来的文件 rm -rf /var/lib/pgsql/12/data/*
重新执行上述 pg_basebackup 命令
启动132从的pgsql服务
六、验证

131主上验证

[root@centos7-0-1 data]# ps -ef | grep send
postgres 13006 12759 0 21:54 ? 00:00:00 postgres: walsender replica 192.168.40.132(58116) streaming 0/16000148

登陆pgsql查看

su - postgres
psql -U postgres

执行sql1

select client_addr,sync_state from pg_stat_replication;

执行sql2

select pid, usename, application_name, client_addr, backend_start, client_port, state, sync_state from pg_stat_replication;

pid | usename | application_name | client_addr | backend_start | client_port | state | sync_state
-------+---------+------------------+----------------+-------------------------------+-------------+-----------+------------
13006 | replica | walreceiver | 192.168.40.132 | 2024-05-23 21:54:38.231088+08 | 58116 | streaming | async
(1 row)

132从上验证

[root@centos7-0-2 system]# ps aux | grep receiver
postgres 13321 0.1 0.2 404248 4620 ? Ss 13:54 0:05 postgres: walreceiver streaming 0/16000148

七、pgsql其他复制模式简介

pgsql提供了5种复制模式，我们上面安装的是第一种

流复制（Streaming Replication）:
这是最常用的复制模式之一。
主库将事务日志以流的形式传送到备库,备库实时应用这些变更。
支持同步和异步复制模式。

逻辑复制（Logical Replication）:
基于变更数据捕获(CDC)的复制方式。
备库可以选择性地订阅主库的部分数据表。
支持跨版本复制,主备可以运行不同版本的 PostgreSQL。

物理复制（Physical Replication）:
也称为基于块级别的复制。
主库将磁盘数据块以原始形式传输到备库。
备库可以直接从主库的数据文件中恢复数据。

多主复制（Multi-Master Replication）:
允许多个节点同时作为主库,相互复制数据。
需要处理数据冲突的问题。
通常用于读写分离和负载均衡。

复制集群（Replication Cluster）:
基于 Patroni 等复制管理工具构建的高可用集群。
提供自动故障切换和主备切换的能力。