pgsql 主从流复制安装及测试

目录

• pgsql 主从流复制安装及测试
  • 安装前准备
  • 安装pgsql
  • pgsql主131服务器操作
  • pgsql从132服务器操作
  • 验证
  • pgsql复制模式简介

pgsql 主从流复制安装及测试

pgsql 提供了多种复制模式，可以参考文章最后简介部分

安装前准备

我这准备了两台centos7.8的机器 ，安装pgsql12版本
192.168.40.131 主
192.168.40.132 从

# 安装前做一些系统优化，不做的话可能导致初始化失败或者主从同步失败
# 修改添加limit的配置，修改后重启，执行 ulimit -a 查看，如果没能生效检查下 .bashrc 或 /etc/profile ，这里过小会导致数据库初始化失败。
# 临时设置的 ulimit -s 102400 这种在初始化时也会失败，需要持久化到配置文件。
vim /etc/security/limits.conf  
* soft stack 102400
* hard stack 102400

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

# 检查本地防火墙是否有开启,可以根据实际情况进行关闭
systemctl status firewalld
systemctl status ipatbles   # centos7中正常不存在

安装pgsql

注：两台机器都需要安装

#创建镜像源文件
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

pgsql主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，为从服务器添加权限
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';

# 也可以无需重制密码
# 设置postgres密码
\password postgres

pgsql从132服务器操作

# 132的从服务器，在安装完pgsql后无需初始化，直接通过命令可以将主的配置拷贝过来
# 注意的是，下面命令不可直接粘贴执行，需要进行替换
# 192.168.40.131是我们主的ip地址，replica是我们上面新建的role，/var/lib/pgsql/12/data/ 是上面初始化时 PGDATA设置的地址
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 指定创建的备库的数据库目录

# 修改从的配置文件
vim /var/lib/pgsql/12/data/postgresql.conf 
# 标明去主同步的ip，端口，账号，密码
primary_conninfo = '192.168.40.131 port=5432 user=replica password=123456'
recovery_target_timeline = 'latest'
hot_standby = on

# 修改pg_hba.conf，为主131主添加访问132从的权限
vim /var/lib/pgsql/12/data/pg_hba.conf

host replication replica 192.168.40.131/32 md5

# 修改从的文件夹权限和属主属组，否则无法同步
chown -R postgres:postgres /var/lib/pgsql/12/data
chmod -R 0700 /var/lib/pgsql/12/data

# 启动从的pgsql
systemctl start postgresql-12.service && systemctl enable postgresql-12.service

验证

主库查看

# 主库查看,可以看到主正向从 192.168.40.132 发送
[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)

从库查看

[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 等复制管理工具构建的高可用集群。
  提供自动故障切换和主备切换的能力。