pg主从流复制搭建
流复制是 PostgreSQL 9.0 之后 提供的一种数据复制机制，它允许从服务器实时接收主服务器的 WAL（Write-Ahead Logging）日志，以保持数据的一致性。流复制有助于提高数据库的高可用性，并能够实现读写分离，提高系统性能。
[TOC]
准备工作
- 准备2台机器
| ip地址 | os版本 | pg版本 | pg端口 | 角色 |
|---|---|---|---|---|
| 192.168.31.101 | CentOS 7.9 | 16.6 | 1921 | 主库 master |
| 192.168.31.102 | CentOS 7.9 | 16.6 | 1921 | 从库 standby |
- 所有节点：系统初始化（可重复执行）
#.1.配置dns并安装基础软件
sed -i 's/^nameserver.*/nameserver 223.5.5.5/g' /etc/resolv.conf
cat /etc/resolv.conf | grep "^nameserver" > /dev/null || echo "nameserver 223.5.5.5" > /etc/resolv.conf
yum install -y -q curl wget ntp ntpdate lrzsz telnet zip unzip net-tools
#.2.修改时区并同步时间
timedatectl set-timezone Asia/Shanghai
/usr/sbin/ntpdate -u ntp.aliyun.com
#.3.建议禁用firewalld否则重启后需要iptable -F清除防火墙策略
/usr/sbin/iptables -F
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
#.4.建议关闭selinux否则会限制服务
sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
setenforce 0
主从流复制的配置过程
编译安装 postgresql
- 所有节点：编译安装 postgresql-16.6
#.安装依赖
yum install -y gcc libicu-devel zlib-devel readline-devel perl-ExtUtils-Embed openssl openssl-devel tcl tcl-devel
#.编译安装
cd /opt/
wget -c http://iso.sqlfans.cn/postgresql/postgresql-16.6.tar.gz
tar xvf postgresql-16.6.tar.gz
cd postgresql-16.6
./configure --prefix=/opt/pgsql --with-perl --with-tcl --with-openssl --without-readline
make -j4 && make install
- 所有节点：启动实例，示例端口 1921、安装目录
/data/pgsql_1921、最大连接数 3000
#.创建启动用户
groupadd postgres
useradd -g postgres postgres
echo "Admin_147" | passwd postgres --stdin
#.创建数据目录
mkdir -p /data/pgsql_1921
chown -R postgres:postgres /data/pgsql_1921
chmod -R 775 /data/pgsql_1921
#.初始化及启动
su - postgres -c "/opt/pgsql/bin/initdb -E UNICODE -D /data/pgsql_1921"
su - postgres -c '/opt/pgsql/bin/pg_ctl -D /data/pgsql_1921 -l logfile start'
#.优化默认参数-先取消注释
cp /data/pgsql_1921/postgresql.conf /data/pgsql_1921/postgresql.conf.$(date +%Y%m%d%H%M%S)
sed -i "s/^#port/port/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#listen_addresses/listen_addresses/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#max_connections/max_connections/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#shared_buffers/shared_buffers/" /data/pgsql_1921/postgresql.conf
#.优化默认参数-再修改数值，示例端口改为 1921
sed -i "s/^port.*$/port=1921/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^listen_addresses.*$/listen_addresses='*'/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^max_connections.*$/max_connections=3000/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^shared_buffers.*$/shared_buffers=512MB/g" /data/pgsql_1921/postgresql.conf
cat /data/pgsql_1921/postgresql.conf | egrep "(^port|^listen_addresses|^max_connections|^shared_buffers)"
#.允许所有ip访问
cp /data/pgsql_1921/pg_hba.conf /data/pgsql_1921/pg_hba.conf.$(date +%Y%m%d%H%M%S)
echo "host all all 0.0.0.0/0 md5" >> /data/pgsql_1921/pg_hba.conf
#.重启服务生效
su - postgres -c '/opt/pgsql/bin/pg_ctl -D /data/pgsql_1921 -l logfile restart'
#.重置postgres密码，示例改为 Admin_147
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "ALTER USER postgres WITH PASSWORD 'Admin_147';"
#.登陆测试
/opt/pgsql/bin/psql -h 127.0.0.1 -p 1921 -U postgres -d postgres -c "SELECT * FROM pg_shadow;"
配置主库
- 主库：创建归档和备份目录
mkdir -p /opt/pgsql/pgbackup
chown -R postgres.postgres /opt/pgsql/pgbackup
- 主库：修改 postgresql.conf 启用流复制和归档模式
#.先取消注释
cp /data/pgsql_1921/postgresql.conf /data/pgsql_1921/postgresql.conf.$(date +%Y%m%d%H%M%S)
sed -i "s/^#wal_level/wal_level/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#max_wal_senders/max_wal_senders/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#max_replication_slots/max_replication_slots/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#wal_sender_timeout/wal_sender_timeout/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#min_wal_size/min_wal_size/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#archive_mode/archive_mode/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#archive_command/archive_command/" /data/pgsql_1921/postgresql.conf
#.定义变量
arcommand="cp %p \/opt\/pgsql\/pgbackup\/%f"
#.再修改数值
sed -i "s/^wal_level.*$/wal_level=replica/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^max_wal_senders.*$/max_wal_senders=10/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^max_replication_slots.*$/max_replication_slots=10/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^wal_sender_timeout.*$/wal_sender_timeout=60s/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^min_wal_size.*$/min_wal_size=512MB/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^archive_mode.*$/archive_mode=on/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^archive_command.*$/archive_command='$arcommand'/g" /data/pgsql_1921/postgresql.conf
cat /data/pgsql_1921/postgresql.conf | egrep "(^wal_level|^max_wal_senders|^max_replication_slots|^wal_sender_timeout|^min_wal_size|^archive_mode|^archive_command)"
附：若 max_wal_senders 设置过小，则会造成pg服务无法启动，报错如下：DETAIL: max_wal_senders = 8 is a lower setting than on the primary server, where its value was 10.
- 主库：修改 pg_hba.conf 允许从服务器连接进行复制
cp /data/pgsql_1921/pg_hba.conf /data/pgsql_1921/pg_hba.conf.$(date +%Y%m%d%H%M%S)
echo "host replication all 0.0.0.0/0 md5" >> /data/pgsql_1921/pg_hba.conf
- 主库：创建一个用于复制的用户，示例postgres密码为
Admin_147
/opt/pgsql/bin/psql -h 192.168.31.101 -p 1921 -U postgres -d postgres -c "CREATE USER dba_repl REPLICATION LOGIN ENCRYPTED PASSWORD 'MA6RuouuZZn4x_Hd';"
- 主库：重载配置以生效
/opt/pgsql/bin/psql -h 192.168.31.101 -p 1921 -U postgres -d postgres -c "select pg_reload_conf();"
- 主库：调整pg目录权限（因为上面用root账号cp备份了几个配置文件），否则从库执行 pg_basebackup 会报权限问题
chown -R postgres:postgres /data/pgsql_1921
chmod -R 775 /data/pgsql_1921
配置从库
- 从库：创建归档和备份目录
mkdir -p /opt/pgsql/pgbackup
chown -R postgres.postgres /opt/pgsql/pgbackup
- 从库：先停止服务，再清空目录
su - postgres -c '/opt/pgsql/bin/pg_ctl -D /data/pgsql_1921 -l logfile stop'
rm -rf /data/pgsql_1921/*
- 从库：远程备份master数据，示例postgres密码为 Admin_147
/opt/pgsql/bin/pg_basebackup -D /data/pgsql_1921/ -Fp -Xs -v -P -h 192.168.31.101 -p 1921 -U postgres -R
chown -R postgres:postgres /data/pgsql_1921
- 从库：修改 postgresql.conf 配置standby及primary字符串
#.先取消注释
cp /data/pgsql_1921/postgresql.conf /data/pgsql_1921/postgresql.conf.$(date +%Y%m%d%H%M%S)
sed -i "s/^#hot_standby/hot_standby/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#hot_standby_feedback/hot_standby_feedback/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#primary_conninfo/primary_conninfo/" /data/pgsql_1921/postgresql.conf
sed -i "s/^#recovery_target_timeline/recovery_target_timeline/" /data/pgsql_1921/postgresql.conf
#.定义变量
priconn="host=192.168.31.101 port=1921 user=dba_repl password=MA6RuouuZZn4x_Hd"
#.再修改数值
sed -i "s/^hot_standby.*$/hot_standby=on/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^hot_standby_feedback.*$/hot_standby_feedback=on/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^primary_conninfo.*$/primary_conninfo='$priconn'/g" /data/pgsql_1921/postgresql.conf
sed -i "s/^recovery_target_timeline.*$/recovery_target_timeline=latest/g" /data/pgsql_1921/postgresql.conf
cat /data/pgsql_1921/postgresql.conf | egrep "(^hot_standby|^hot_standby_feedback|^primary_conninfo|^recovery_target_timeline)"
- 从库：启动服务
su - postgres -c '/opt/pgsql/bin/pg_ctl -D /data/pgsql_1921 -l logfile start'
确认主从已同步
- 所有节点：确认进程
#.主库上会多一个 walsender 进程
$ ps -ef | grep postgres | grep wal
postgres 10768 10764 0 16:51 ? 00:00:00 postgres: walwriter
postgres 25409 10764 0 18:19 ? 00:00:00 postgres: walsender postgres 192.168.31.102(40714) streaming 0/60000D8
#.从库上会有一个 walreceiver 进程
$ ps -ef | grep postgres | grep wal
postgres 25825 25821 0 18:19 ? 00:00:00 postgres: walreceiver
- 执行
select pg_is_in_recovery();主库返回f、从库返回t
/opt/pgsql/bin/psql -h 192.168.31.101 -p 1921 -U postgres -d postgres -c "select pg_is_in_recovery();"
/opt/pgsql/bin/psql -h 192.168.31.102 -p 1921 -U postgres -d postgres -c "select pg_is_in_recovery();"
- 登录主库：执行
select * from pg_stat_replication;查看从库是否连接
$ /opt/pgsql/bin/psql -h 192.168.31.101 -p 1921 -U postgres -d postgres -c "select client_addr,state,sync_state from pg_stat_replication;"
Password for user postgres:
client_addr | state | sync_state
----------------+-----------+------------
192.168.31.102 | streaming | async
- 登录从库：通过
select * from pg_stat_wal_receiver;查看同步状态，若status为 streaming 则表示同步正常
$ /opt/pgsql/bin/psql -h 192.168.31.102 -p 1921 -U postgres -d postgres -c "select sender_host,sender_port,status from pg_stat_wal_receiver;"
Password for user postgres:
sender_host | sender_port | status
----------------+-------------+-----------
192.168.31.101 | 1921 | streaming
- 数据确认：主库创建db，从库确认db是否存在
$ /opt/pgsql/bin/psql -h 192.168.31.101 -p 1921 -U postgres -d postgres -c "CREATE DATABASE db2 WITH OWNER=postgres ENCODING='UTF-8';"
Password for user postgres:
CREATE DATABASE
$ /opt/pgsql/bin/psql -h 192.168.31.102 -p 1921 -U postgres -d postgres -c "select oid,datname from pg_database;"
Password for user postgres:
oid | datname
-------+-----------
5 | postgres
16389 | db2
1 | template1
4 | template0