# 2. 创建复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘replicator123’;

# 3. 备库配置
– 基础备份
pg_basebackup -h 192.168.1.100 -U replicator -D /yashandb/fgdata/fgedudb -F p -X stream

– 创建recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.1.100 port=54321 user=replicator password=replicator123’

# 4. 启动备库
$ yasboot start

# 5. 验证复制状态
fgedudb=# SELECT * FROM pg_stat_replication;

# 2. 创建发布
CREATE PUBLICATION fgedu_pub FOR TABLE fgedu.employees, fgedu.departments;

# 3. 备库配置
– 创建订阅
CREATE SUBSCRIPTION fgedu_sub CONNECTION ‘host=192.168.1.100 port=54321 dbname=fgedudb user=replicator password=replicator123’ PUBLICATION fgedu_pub;

# 4. 验证复制状态
fgedudb=# SELECT * FROM pg_stat_subscription;

– 查看复制延迟
SELECT
slot_name,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;

– 查看备库状态
SELECT
pg_last_wal_receive_lsn() AS receive_lsn,
pg_last_wal_replay_lsn() AS replay_lsn,
pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_lag_bytes;

# 监控逻辑复制
– 查看订阅状态
SELECT * FROM pg_stat_subscription;

# 监控系统状态
– 查看WAL发送进程
ps aux | grep wal sender

– 查看WAL接收进程
ps aux | grep wal receiver

# 监控告警
– 配置Prometheus + Grafana
– 配置复制延迟告警

2. 查看日志
$ tail -f /yashandb/fgdata/fgedudb/log/yashandb.log

3. 常见故障原因
– 网络连接问题
– 权限问题
– WAL文件缺失
– 存储空间不足

4. 故障处理
– 网络问题：检查网络连接
– 权限问题：检查复制用户权限
– WAL缺失：重建备库
– 空间不足：清理空间

# 重建备库
1. 停止备库
$ yasboot stop

2. 清理数据目录
$ rm -rf /yashandb/fgdata/fgedudb/*

3. 执行基础备份
pg_basebackup -h 192.168.1.100 -U replicator -D /yashandb/fgdata/fgedudb -F p -X stream

4. 修改recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.1.100 port=54321 user=replicator password=replicator123’

5. 启动备库
$ yasboot start

6. 验证复制状态
SELECT * FROM pg_stat_replication;