PostgreSQL-14 流复制(主从)配置
PostgreSql主从实现（异步流复制）
一、前期准备
1.1 环境准备：
| 角色 | IP | 端口 | pg版本 | os版本 |
|---|---|---|---|---|
| Master | 10.0.0.8 | 5432 | 14.15 | Ubuntu 22.04.4 LTS |
| Standby | 10.0.0.7 | 5432 | 14.18 | Ubuntu 22.04.4 LTS |
1.2 主库测试数据
# a. 显示数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
# b. 创建表
postgres=# create table t1(id int,name varchar(50));
CREATE TABLE
----
- id int：定义了一个名为id的字段，数据类型为int。
- name varchar(50)：定义了一个名为name的字段，数据类型为varchar，最大长度为50。
----
# c. 显示表结构
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(50) | | |
#d. 插入数据
postgres=# insert into t1 values (01,'kyle'),(02,'rocky');
INSERT 0 2
-----
- 在INSERT INTO语句中，每个字段值之间需要用逗号（,）分隔
- 在SQL中，字符串值需要用单引号（''）括起来
- 插入的值数量必须与字段数量一致，并且顺序要匹配。
-----
#e. 查看表数据
postgres=# select * from t1 ;
id | name
----+-------
1 | kyle
2 | rocky
(2 rows)
二、主库操作
2.1 创建用于数据流的用户
$ psql -h 127.0.0.1 -p 5432 -U postgres -W
Password:
psql (14.10)
Type "help" for help.
postgres=# create role repl login replication encrypted password 'oLfex^5pfe';
CREATE ROLE
postgres=# \q
2.2 配置登录权限
- 修改配置文件：$PGDATA/pg_hba.conf
![image]()
2.3 配置postgresql.conf
wal_level = replica # 开启足够级别的 WAL（Write Ahead Log）日志，用于支持复制。
✅ 可选值：
minimal：最小化 WAL（不支持复制）
replica（默认）：支持流复制
logical：用于逻辑复制（发布订阅）
fsync = on # 保证数据安全
max_wal_senders = 10 # 设置最大并发 WAL 发送进程数，决定最多能有多少个从库连接进行流复制。
❗注意：如果设置，从库这个值需要同主库这个值相同或者比这个值大，否则会失败⭐
hot_standby = on # 参数用于启用热备功能，允许从服务器（standby）在同步过程中接受查询请求。
wal_receiver_status_interval = 10s # 控制 walreceiver 进程向主服务器发送心跳消息的时间间隔
hot_standby_feedback = on # 控制备用服务器是否会向主服务器发送关于自己的复制状态和进度的信息
archive_mode = on（可选,根据数据库大小选择，若太大，不建议配置）
✅ 作用：
启用 WAL 日志归档，用于灾难恢复或从库搭建失败后的修复。
✅ 配合使用：
需要设置 archive_command，如：
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
2.4 重启数据库
su - postgres
pg_ctl restart
或
systemctl restart postgresql
三、从库操作(异步)
3.1 创建从库数据目录
su - postgres
# 根据自己数据目录而定
sudo rm -rf /var/lib/postgresql/14/main
sudo mkdir -p /var/lib/postgresql/14/main/pg_wal && \
sudo chown -R postgres. /var/lib/postgresql/14/main/pg_wal && \
sudo chmod 700 /var/lib/postgresql/14/main/pg_wal
3.2 基础备份
pg_basebackup -h 10.0.0.8 -p 5432 -U repl --pgdata=/var/lib/postgresql/14/main --write-recovery-conf --progress --verbose
3.3 从库相关配置
所有节点确认该参数，如没有则添加
cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 备用服务器相关设置
hot_standby = on # 控制是否允许备用服务器在进行热备份时同时处理读取查询
wal_receiver_status_interval = 10s # 控制 walreceiver 进程向主服务器发送心跳消息的时间间隔
hot_standby_feedback = on # 控制备用服务器是否会向主服务器发送关于自己的复制状态和进度的信息
EOF
3.4 连接主库配置
3.5 创建从库标识文件
ls $PGDATA/standby.signal &> /dev/null || touch $PGDATA/standby.signal
3.6 启动服务
从库操作
pg_ctl start
或
systemctl restart postgresql
3.7 查看日志
从库操作
cd $PGDATA/logs/
tail -f postgresql-`date "+%Y-%m-%d"`.log
3.8 查看表数据
postgres@pgbak:~/14/main/logs$ psql
psql (14.18 (Ubuntu 14.18-1.pgdg22.04+1))
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
(2 rows)
postgres=# select * from t1;
id | name
----+-------
1 | kyle
2 | rocky
(2 rows)
🔶 对比主库数据
四、从库操作（同步）
4.1 基础配置
按照上面异步流复制操作一遍
4.2 连接主库配置
从库操作
cat $PGDATA/postgresql.auto.conf
primary_conninfo = 'host=192.168.32.127 port=5432 user=repl application_name=pg01'
添加
application_name参数
4.3 从库相关配置
所有节点确认该参数，如没有则添加
cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 同步(实时)复制配置
synchronous_commit = on # 控制事务提交的同步方式。该参数决定了在事务提交时是否等待数据同步到磁盘上
synchronous_standby_names = 'FIRST 2(pg01, pg03)' # 该参数指定了在主服务器提交事务时，需要等待哪些热备服务器将数据同步到磁盘上
EOF
参数 synchronous_standby_names 的值说明：
FIRST num (name1, name2)：前面num台为同步服务器，其他为异步服务器ANY num (name1, name2)：从括号中选择num台为同步服务器，其他为异步服务器
其中括号里面的
name1、name2为 3.2步骤 对应的application_name值
4.4 重新加载配置
所有节点
pg_ctl reload
五、验证
- 主库执行，打印从库信息则说明搭建成功
psql -h localhost -p 5432 -U postgres -W postgres -c "select pid,usename,application_name,client_addr,state,sync_state,sync_priority from pg_stat_replication;"
-
sync_state参数说明
-
async: 这台服务器是异步的
-
sync: 这台服务器是同步的
-
potential: 这台服务器现在是异步的，但可能在当前的同步服务器失效时变成同步的
-
quorum: 这台服务器被当做规定数量后备服务器的候选
-
六、故障问题
🔸若同步故障，最终主库查询时没有出现async，可检查配置文件postgresql.conf,检查配置中主从不同之处，例如：
max_wal_senders = 10 # 设置最大并发 WAL 发送进程数，决定最多能有多少个从库连接进行流复制。
❗注意：如果设置，从库这个值需要同主库这个值相同或者比这个值大，否则会失败⭐
🔸关于一些sender数量，size大小配置上，遵从从库比主库相同或者更大
浙公网安备 33010602011771号