一 介绍

PostgreSQL数据库高可用方案有很多种，以下四种比较常用。

PAF(PostgreSQL Automatic Failover)

Patroni

repmgr

PGPool-ll

其中Patroni采用DCS存储元数据，能够严格的保障元数据的一致性，可靠性高。而且它的功能也比较强大。

二 环境准备

节点信息

pg1: 192.168.56.105

pg2: 192.168.56.106

etcd: 192.168.56.104

设置所有节点时钟同步

yum install -y ntpdate 

ntpdate time.windows.com && hwclock -w

关闭防火墙

setenforce 0

sed -i.bak "s/SELINUX=enforcing/SELINUX=permissive/g" /etc/selinux/config

systemctl disable firewalld.service

systemctl stop firewalld.service

iptables -F

etcd部署

安装需要的包

yum install -y gcc python-devel epel-release

安装etcd

yum install -y etcd

编辑etcd配置文件/etc/etcd/etcd.conf

[root@localhost ~]# cat /etc/etcd/etcd.conf

ETCD_DATA_DIR="/var/lib/etcd/default.etcd"

ETCD_LISTEN_PEER_URLS="http://192.168.56.104:2380"

ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://192.168.56.104:2379"

ETCD_NAME="etcd0"

ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.56.104:2380"

ETCD_ADVERTISE_CLIENT_URLS="http://192.168.56.104:2379"

ETCD_INITIAL_CLUSTER="etcd0=http://192.168.56.104:2380"

ETCD_INITIAL_CLUSTER_TOKEN="cluster1"

ETCD_INITIAL_CLUSTER_STATE="new"

启动etcd

systemctl start etcd

设置etcd自启动

systemctl enable etcd

检查etcd状态

[root@localhost ~]# systemctl status etcd

三 pg1和pg2实例上环境部署

安装软件

安装PostgreSQL 12

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum install -y postgresql12-server postgresql12-contrib

安装patroni

yum install -y gcc epel-release

yum install -y python3 -pip python-psycopg2 python-deve

python3 -m pip install --upgrade pip

python3 -m pip install --upgrade setuptools

python3 -m pip install patroni[etcd]

创建PostgreSQL数据目录

mkdir -p /pgsql/data

chown postgres:postgres -R /pgsql

chmod -R 700 /pgsql/data

创建partoni service配置文件/etc/systemd/system/patroni.service

pg1和pg2实例配置如下


-bash-4.2$ cat /etc/systemd/system/patroni.service

[Unit]

Description=Runners to orchestrate a high-availability PostgreSQL

After=syslog.target network.target

[Service]

Type=simple

User=postgres

Group=postgres

ExecStart=/usr/local/bin/patroni  /etc/patroni.yml

KillMode=process

TimeoutSec=30

Restart=no

[Install]

WantedBy=multi-user.targ

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

pg1实例如下

-bash-4.2$ cat /etc/patroni.yml

scope: pgsql

namespace: /service/

name: pg1

restapi:

  listen: 0.0.0.0:8000

  connect_address: 192.168.56.105:8000

etcd:

  host: 192.168.56.104:2379

bootstrap:

  dcs:

    ttl: 30

    loop_wait: 10

    retry_timeout: 10

    maximum_lag_on_failover: 1048576

    master_start_timeout: 300

    synchronous_mode: false

    postgresql:

      use_pg_rewind: true

      use_slots: true

      parameters:

        listen_addresses: "0.0.0.0"

        port: 5432

        wal_level: logical

        hot_standby: "on"

        wal_keep_segments: 1000

        max_wal_senders: 10

        max_replication_slots: 10

        wal_log_hints: "on"

  initdb:

  - encoding: UTF8

  - locale: C

  - data-checksums

  pg_hba:

  - host replication repl 0.0.0.0/0 md5

  - host all all 0.0.0.0/0 md5

postgresql:

  listen: 0.0.0.0:5432

  connect_address: 192.168.56.105:5432

  data_dir: /pgsql/data

  bin_dir: /usr/pgsql-12/bin

  authentication:

    replication:

      username: repl

      password: "123456"

    superuser:

      username: postgres

      password: "123456"

  callbacks:

    on_start: /bin/bash /pgsql/loadvip.sh

    on_restart: /bin/bash /pgsql/loadvip.sh

    on_role_change: /bin/bash /pgsql/loadvip.sh

tags:

    nofailover: false

    noloadbalance: false

    clonefrom: false

    nosync: false

pg2实例如下

-bash-4.2$ cat /etc/patroni.yml

scope: pgsql

namespace: /service/

name: pg2

restapi:

  listen: 0.0.0.0:8000

  connect_address: 192.168.56.106:8000

etcd:

  host: 192.168.56.104:2379

bootstrap:

  dcs:

    ttl: 30

    loop_wait: 10

    retry_timeout: 10

    maximum_lag_on_failover: 1048576

    master_start_timeout: 300

    synchronous_mode: false

    postgresql:

      use_pg_rewind: true

      use_slots: true

      parameters:

        listen_addresses: "0.0.0.0"

        port: 5432

        wal_level: logical

        hot_standby: "on"

        wal_keep_segments: 1000

        max_wal_senders: 10

        max_replication_slots: 10

        wal_log_hints: "on"

  initdb:

  - encoding: UTF8

  - locale: C

  - data-checksums

  pg_hba:

  - host replication repl 0.0.0.0/0 md5

  - host all all 0.0.0.0/0 md5

postgresql:

  listen: 0.0.0.0:5432

  connect_address: 192.168.56.106:5432

  data_dir: /pgsql/data

  bin_dir: /usr/pgsql-12/bin

  authentication:

    replication:

      username: repl

      password: "123456"

    superuser:

      username: postgres

      password: "123456"

  callbacks:

    on_start: /bin/bash /pgsql/loadvip.sh

    on_restart: /bin/bash /pgsql/loadvip.sh

    on_role_change: /bin/bash /pgsql/loadvip.sh

tags:

    nofailover: false

    noloadbalance: false

    clonefrom: false

    nosync: false

启动patroni

pg1上启动patroni

systemctl start patroni

初次启动patroni时，patroni会初始创建PostgreSQL实例和用户。

-bash-4.2$ systemctl status patroni

pg2上启动patroni

-bash-4.2$ systemctl status patroni

查看集群状态

-bash-4.2$ patronictl -c /etc/patroni.yml list

添加以下环境变量到~postgres/.bash_profile

export PATRONICTL_CONFIG_FILE=/etc/patroni.yml

export PGDATA=/pgsql/data

export PATH=/usr/pgsql-12/bin:$PATH

设置postgres拥有sudoer权限

echo 'postgres        ALL=(ALL)       NOPASSWD: ALL'> /etc/sudoers.d/postgres

四 Patroni自动化动作

Patroni在特定场景下会执行一些自动化动作，目的是为了保障服务的可用性以及防止脑裂。