# 1. 安装Repmgr
$ sudo yum install repmgr14

# 2. 主节点配置
$ sudo vi /postgresql/fgdata/postgresql.conf
listen_addresses = ‘*’
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB
hot_standby = on

$ sudo vi /postgresql/fgdata/pg_hba.conf
host replication repmgr 192.168.1.0/24 md5
host repmgr repmgr 192.168.1.0/24 md5

# 3. 创建repmgr用户
$ psql -U postgres -c “CREATE ROLE repmgr WITH SUPERUSER LOGIN PASSWORD ‘password’;”
$ psql -U postgres -c “CREATE DATABASE repmgr OWNER repmgr;”

# 4. 配置repmgr.conf
$ sudo vi /etc/repmgr.conf
node_id=1
node_name=’node1′
conninfo=’host=192.168.1.100 user=repmgr password=password dbname=repmgr port=5432′
data_directory=’/postgresql/fgdata’

# 5. 注册主节点
$ repmgr primary register

# 6. 从节点配置
$ sudo vi /postgresql/fgdata/postgresql.conf
# 与主节点相同的配置

$ sudo vi /postgresql/fgdata/pg_hba.conf
# 与主节点相同的配置

$ sudo vi /etc/repmgr.conf
node_id=2
node_name=’node2′
conninfo=’host=192.168.1.101 user=repmgr password=password dbname=repmgr port=5432′
data_directory=’/postgresql/fgdata’
primary_conninfo=’host=192.168.1.100 user=repmgr password=password dbname=repmgr port=5432′

# 7. 克隆主节点到从节点
$ repmgr standby clone –primary-host=192.168.1.100 –primary-user=repmgr –dbname=repmgr

# 8. 启动从节点
$ sudo systemctl start postgresql

# 9. 注册从节点
$ repmgr standby register

# 10. 验证集群状态
$ repmgr cluster show

# 1. 主节点参数优化
$ sudo vi /postgresql/fgdata/postgresql.conf

# 内存参数
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 2GB
effective_cache_size = 24GB

# 并发参数
max_connections = 200

# WAL参数
wal_level = logical
max_wal_senders = 20
max_replication_slots = 20
wal_keep_size = 2GB

# 2. 从节点参数优化
$ sudo vi /postgresql/fgdata/postgresql.conf

# 内存参数
shared_buffers = 8GB
work_mem = 64MB
maintenance_work_mem = 2GB
effective_cache_size = 24GB

# 并发参数
max_connections = 200

# 热备参数
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s

# 3. Repmgr配置优化
$ sudo vi /etc/repmgr.conf
failover=automatic
promote_command=’repmgr standby promote -f /etc/repmgr.conf’
follow_command=’repmgr standby follow -f /etc/repmgr.conf –upstream-node-id=%n’

# 4. 重启集群节点
$ sudo systemctl restart postgresql

# 5. 验证集群状态
$ repmgr cluster show

# 1. 使用repmgr监控集群状态
$ repmgr cluster show

# 2. 使用pg_stat_replication监控复制状态
$ psql -U postgres -c “SELECT * FROM pg_stat_replication;”

# 3. 使用Prometheus监控集群
$ sudo vi /etc/prometheus/prometheus.yml
– job_name: ‘postgresql’
static_configs:
– targets: [‘192.168.1.100:9187’, ‘192.168.1.101:9187’, ‘192.168.1.102:9187’]

# 4. 配置Grafana面板
# 导入PostgreSQL集群监控面板

# 5. 设置告警规则
$ sudo vi /etc/prometheus/rules/cluster_alerts.yml
groups:
– name: cluster_alerts
rules:
– alert: ClusterNodeDown
expr: pg_up == 0
for: 5m
labels:
severity: critical
annotations:
summary: “Cluster node down on {{ $labels.instance }}”
description: “Cluster node {{ $labels.instance }} has been down for 5 minutes”

– 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