┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL 复制架构                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│    Primary ─────► Standby 1 (同步)                          │
│      │           ─────► Standby 2 (异步)                     │
│      │           ─────► Standby 3 (异步)                     │
│      │                                                        │
│    ┌──┴──┐                                                   │
│    │     │                                                   │
│  sync  async                                                │
│                                                              │
└─────────────────────────────────────────────────────────────┘

# postgresql.conf

# 启用 WAL
wal_level = replica

# 最大复制连接数
max_wal_senders = 10

# WAL 保留
wal_keep_size = 1GB
max_replication_slots = 10

# 复制相关参数
synchronous_commit = on
synchronous_standby_names = 'standby1,standby2'

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# 允许复制连接
host    replication     replicator      192.168.1.0/24          scram-sha-256
host    all             postgres        192.168.1.0/24          scram-sha-256

-- 创建复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator_password';

-- 授予必要权限
GRANT CONNECT ON DATABASE mydb TO replicator;
GRANT USAGE ON SCHEMA public TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

# 方法 1：使用 pg_basebackup
pg_basebackup -h primary_ip -U replicator -D /var/lib/postgresql/16/main -P -Xs -R

# 参数说明：
# -h: 主库地址
# -U: 复制用户名
# -D: 从库数据目录
# -P: 显示进度
# -Xs: 包含 WAL 文件
# -R: 创建 standby.signal 文件

# 方法 2：手动配置
# 1. 从主库复制数据
rsync -av --exclude=pg_wal primary_server:/var/lib/postgresql/data/ /var/lib/postgresql/data/

# 2. 创建 standby.signal
touch /var/lib/postgresql/data/standby.signal

# 3. 配置连接信息
cat >> /var/lib/postgresql/data/postgresql.auto.conf << 'EOF'
primary_conninfo = 'host=primary_ip port=5432 user=replicator password=replicator_password application_name=standby1'
EOF

# 主库 postgresql.conf
synchronous_commit = on
synchronous_standby_names = 'standby1,standby2'

# 同步复制优先级
synchronous_standby_names = 'standby1 (priority=2), standby2 (priority=1)'

-- 在主库查看复制状态
SELECT * FROM pg_stat_replication;

-- 详细复制信息
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state,
    sync_priority
FROM pg_stat_replication;

-- 查看复制槽
SELECT * FROM pg_replication_slots;

-- 在从库查看复制状态
SELECT * FROM pg_stat_wal_receiver;

-- 主库监控复制延迟
SELECT 
    client_hostname,
    state,
    pg_size_pretty(
        pg_wal_lsn_diff(sent_lsn, replay_lsn)
    ) AS replication_delay
FROM pg_stat_replication;

-- 从库查看延迟
SELECT 
    now() - pg_last_xact_replay_timestamp() AS replay_delay;

# 在主库查看复制状态
psql -U postgres -c "SELECT * FROM pg_stat_replication;"

# 停止主库
sudo systemctl stop postgresql

# 在新主库执行提升
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/data

# 验证新主库
psql -U postgres -c "SELECT pg_is_in_recovery();"
# 应返回 f (false)

# 更新其他从库指向新主库
# 在其他从库执行:
psql -U postgres -c "SELECT pg_reload_conf();"

# patroni.yml 配置示例
patroni:
  name: node1
  scope: postgres-cluster
  namespace: /service
  restapi:
    listen: 0.0.0.0:8008
    connect_address: node1:8008
  etcd:
    host: etcd:2379
  postgresql:
    listen: 0.0.0.0:5432
    connect_address: node1:5432
    data_dir: /data/postgresql
    parameters:
      wal_level: replica
      max_wal_senders: 10
      max_replication_slots: 10
      hot_standby: on
  slots:
    persistent_slots:
      node2:
        type: physical
      node3:
        type: physical
  bootstrap:
    dcs:
      ttl: 30
      loop_wait: 10
      retry_timeout: 10
      synchronous_node_count: 1
      members:
        - node1
        - node2
        - node3

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

-- 1. 创建发布
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- 2. 创建订阅
CREATE SUBSCRIPTION my_subscription 
CONNECTION 'host=primary_ip port=5432 dbname=mydb user=replicator'
PUBLICATION my_publication;

-- 3. 查看订阅状态
SELECT * FROM pg_stat_subscription;

-- 4. 查看复制表
SELECT * FROM pg_publication_tables;

-- 只复制特定行
CREATE PUBLICATION active_users_pub FOR TABLE users
WHERE (status = 'active');

-- 复制所有表的变更
CREATE PUBLICATION full_db_pub FOR ALL TABLES;

-- 添加表到发布
ALTER PUBLICATION my_publication ADD TABLE products;

-- 删除表
ALTER PUBLICATION my_publication DROP TABLE products;

-- 禁用表同步
ALTER SUBSCRIPTION my_subscription ALTER TABLE users SET (copy_data = false);

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# haproxy.cfg
listen postgres
    bind 0.0.0.0:5432
    mode tcp
    balance roundrobin
    
    # 主库（读写）
    server primary primary_ip:5432 check port 5432 inter 2000 rise 2 fall 3
    
    # 从库（只读）
    server replica1 replica1_ip:5432 check port 5432 inter 2000 rise 2 fall 3
    server replica2 replica2_ip:5432 check port 5432 inter 2000 rise 2 fall 3

listen pgbouncer
    bind 0.0.0.0:6432
    mode tcp
    balance leastconn
    server pgbouncer1 127.0.0.1:6432