PostgreSQL在Ubuntu中的高可用性如何搭建

在Ubuntu中搭建PostgreSQL的高可用性集群，可以使用多种工具和方法。以下是使用Patroni和Keepalived来实现高可用性的步骤：

1. 安装必要的软件包

首先，更新你的Ubuntu系统并安装必要的软件包：

sudo apt-get update
sudo apt-get install -y postgresql-13 postgresql-13-pgpool pgpool-II patroni keepalived

2. 配置PostgreSQL

编辑PostgreSQL的配置文件/etc/postgresql/13/main/postgresql.conf和pg_hba.conf，确保它们支持复制。

`postgresql.conf`

listen_addresses = '*'  # 监听所有地址
wal_level = replica    # 设置为replica以支持流复制
max_wal_senders = 10   # 最大WAL发送者数量
wal_keep_segments = 64 # 保留的WAL段数量
hot_standby = on       # 启用热备模式

`pg_hba.conf`

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5
host    replication     replicator      0.0.0.0/0               md5

3. 初始化复制

在主节点上创建一个复制用户：

CREATE USER replicator WITH REPLICATION PASSWORD 'your_password' LOGIN;

然后，在主节点上创建一个用于复制的数据库：

CREATE DATABASE your_database;

4. 配置Patroni

创建Patroni配置文件/etc/patroni.yml：

scope: postgres
name: master
namespace: /db/
restapi:
  listen: 0.0.0.0:8008
  connect_address: 127.0.0.1:8008
etcd:
  host: 127.0.0.1:2379
  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
      max_wal_senders: 10
      wal_keep_segments: 64
      synchronous_commit: off
      synchronous_standby_names: '*'
  initdb:
  - encoding: UTF8
  - data-checksums
  - timezone: UTC
  - locale: en_US.utf8
  - pg_hba:
    - host replication replicator 0.0.0.0/0 md5
  - auth:
      replication:
        username: replicator
        password: your_password
  - backup:
      enabled: false
  - recovery:
      target_timeline: latest
      restore_command: 'cp /var/lib/postgresql/13/main/wal_archive/%f %p'
      recovery_target_action: pause
      recovery_target_inclusive: true
      recovery_target_time: '2023-04-01 00:00:00'
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'
      recovery_min_apply_delay: 0
      recovery_max_apply_delay: 3600
      recovery_target_priority: 100
      recovery_target_timeline: latest
      recovery_target_xid: 0
      recovery_target_name: 'latest'