# 1. 修改postgresql.conf文件
$ sudo vi /postgresql/fgdata/postgresql.conf

# 监听地址
listen_addresses = ‘*’

# WAL级别
wal_level = replica

# 最大WAL发送进程数
max_wal_senders = 10

# 最大复制槽数
max_replication_slots = 10

# WAL保持时间
wal_keep_size = 1GB

# 2. 修改pg_hba.conf文件
$ sudo vi /postgresql/fgdata/pg_hba.conf

# 添加复制用户的访问权限
host replication replicator 192.168.1.0/24 md5

# 3. 创建复制用户
$ psql -U postgres -c “CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD ‘password’;”

# 4. 重启主数据库
$ sudo systemctl restart postgresql

# 5. 验证主数据库状态
$ psql -U postgres -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
f
(1 row)

# 1. 停止从数据库
$ sudo systemctl stop postgresql

# 2. 清理从数据库数据目录
$ sudo rm -rf /postgresql/fgdata/*

# 3. 使用pg_basebackup初始化从数据库
$ pg_basebackup -h 192.168.1.100 -U replicator -D /postgresql/fgdata -F p -X stream -P

# 4. 创建recovery.conf文件
$ sudo vi /postgresql/fgdata/recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.1.100 port=5432 user=replicator password=password’
primary_slot_name = ‘replica_slot’

# 5. 修改postgresql.conf文件
$ sudo vi /postgresql/fgdata/postgresql.conf

# 热备模式
hot_standby = on

# 6. 启动从数据库
$ sudo systemctl start postgresql

# 7. 验证从数据库状态
$ psql -U postgres -c “SELECT pg_is_in_recovery();”
pg_is_in_recovery
——————-
t
(1 row)

# 1. 主数据库参数优化
$ sudo vi /postgresql/fgdata/postgresql.conf

# WAL级别
wal_level = logical

# 最大WAL发送进程数
max_wal_senders = 20

# 最大复制槽数
max_replication_slots = 20

# WAL保持时间
wal_keep_size = 2GB

# 同步复制
# synchronous_commit = on
# synchronous_standby_names = ‘replica1’

# 2. 从数据库参数优化
$ sudo vi /postgresql/fgdata/postgresql.conf

# 热备模式
hot_standby = on

# 热备反馈
hot_standby_feedback = on

# 从数据库最大延迟
max_standby_streaming_delay = 30s

# 3. 重启主从数据库
$ sudo systemctl restart postgresql

# 4. 验证复制状态
$ psql -U postgres -h 192.168.1.100 -c “SELECT * FROM pg_stat_replication;”

# 1. 使用pg_stat_replication监控复制状态
$ psql -U postgres -c “SELECT fgapplication_name, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;”

# 2. 使用pg_replication_slots监控复制槽
$ psql -U postgres -c “SELECT slot_name, plugin, slot_type, active FROM pg_replication_slots;”

# 3. 使用Prometheus监控复制状态
$ sudo vi /etc/prometheus/prometheus.yml
– job_name: ‘postgresql’
static_configs:
– targets: [‘fgedu.localhost:9187’]

# 4. 配置Grafana面板
# 导入PostgreSQL复制监控面板

# 5. 设置告警规则
$ sudo vi /etc/prometheus/rules/replication_alerts.yml
groups:
– name: replication_alerts
rules:
– alert: ReplicationLag
expr: pg_replication_lag > 30
for: 5m
labels:
severity: warning
annotations:
summary: “Replication lag on {{ $labels.instance }}”
description: “Replication lag is {{ $value }} seconds”

# 6. 验证监控
$ curl http://fgedu.localhost:9090/targets