centos postgresql如何实现高可用

在CentOS上实现PostgreSQL的高可用性，可以采用多种策略和技术。以下是一些常见的方法：

1. 使用Patroni

Patroni是一个用于管理PostgreSQL高可用性的工具，它支持多种后端存储（如etcd、Consul、ZooKeeper）来协调集群状态。

安装Patroni

sudo yum install -y python3-pip
sudo pip3 install patroni

配置Patroni

创建一个Patroni配置文件（例如/etc/patroni.yml）：

scope: postgres
name: centos-postgres
namespace: /db/
restapi:
  listen: 0.0.0.0:8008
  connect_address: 127.0.0.1:8008
etcd:
  host: 127.0.0.1:2379
  scheme: http
  service_name: postgres
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: replica
        max_connections: 100
        hot_standby: on
      replication:
        slot_name: pgsql
        replication_slots: 1
        listen: 0.0.0.0:5432
        connect_address: 127.0.0.1:5432
        authentication:
          replication:
            username: replicator
            password: your_replicator_password
        ssl: false
start: true
stop: true

启动Patroni

sudo patroni /etc/patroni.yml

2. 使用Pgpool-II

Pgpool-II是一个中间件，可以提供负载均衡、故障转移和高可用性。

安装Pgpool-II

sudo yum install -y pgpool2

配置Pgpool-II

编辑/etc/pgpool2/pgpool.conf：

backend_hostname0 = '192.168.1.1'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = '192.168.1.2'
backend_port1 = 5432
backend_weight1 = 1

load_balance_mode = on
failover_command = '/usr/libexec/pgpool_failover.sh %d %h %p %D %m %M %H'
recovery_command = '/usr/libexec/pgpool_recovery.sh %d %h %p %D %m %M %H'

启动Pgpool-II

sudo systemctl start pgpool2
sudo systemctl enable pgpool2

3. 使用Keepalived

Keepalived可以提供虚拟IP地址和故障转移功能。

安装Keepalived

sudo yum install -y keepalived

配置Keepalived

编辑/etc/keepalived/keepalived.conf：

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1234
    }
    virtual_ipaddress {
        192.168.1.100
    }
}

virtual_server 192.168.1.100 5432 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.1.1 5432 {
        weight 1
        TCP_CHECK {
            connect_timeout 10
            connect_port 5432
        }
    }

    real_server 192.168.1.2 5432 {
        weight 1
        TCP_CHECK {
            connect_timeout 10
            connect_port 5432
        }
    }
}

启动Keepalived

sudo systemctl start keepalived
sudo systemctl enable keepalived

总结

以上方法各有优缺点，选择哪种方法取决于你的具体需求和环境。Patroni提供了更全面的解决方案，而Pgpool-II和Keepalived则更适合特定的场景。建议在实际部署前进行充分的测试和评估。