基于Patroni的PostgreSQL高可用环境部署

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

yum install -y gcc python-devel epel-release

yum install -y etcd

ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://192.168.234.204:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://192.168.234.204:2379"
ETCD_NAME="etcd0"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://192.168.234.204:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://192.168.234.204:2379"
ETCD_INITIAL_CLUSTER="etcd0=http://192.168.234.204:2380"
ETCD_INITIAL_CLUSTER_TOKEN="cluster1"
ETCD_INITIAL_CLUSTER_STATE="new"

systemctl start etcd

systemctl enable etcd

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

yum install -y gcc epel-release
yum install -y python-pip python-psycopg2 python-devel

pip install --upgrade pip
pip install --upgrade setuptools
pip install patroni[etcd]

mkdir -p /pgsql/data
chown postgres:postgres -R /pgsql
chmod -R 700 /pgsql/data

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
 
[Service]
Type=simple
User=postgres
Group=postgres
#StandardOutput=syslog
ExecStart=/usr/bin/patroni /etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=no
 
[Install]
WantedBy=multi-user.target

scope: pgsql
namespace: /service/
name: pg1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.234.201:8008

etcd:
  host: 192.168.234.204: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: 100
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"

  initdb:
  - encoding: UTF8
  - locale: C
  - lc-ctype: zh_CN.UTF-8
  - 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.234.201:5432
  data_dir: /pgsql/data
  bin_dir: /usr/pgsql-12/bin

  authentication:
    replication:
      username: repl
      password: "123456"
    superuser:
      username: postgres
      password: "123456"

  basebackup:
    max-rate: 100M
    checkpoint: fast

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

systemctl start patroni

[root@node1 ~]# systemctl status patroni
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
   Loaded: loaded (/etc/systemd/system/patroni.service; disabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-09-05 14:41:03 CST; 38min ago
 Main PID: 1673 (patroni)
   CGroup: /system.slice/patroni.service
           ├─1673 /usr/bin/python2 /usr/bin/patroni /etc/patroni.yml
           ├─1717 /usr/pgsql-12/bin/postgres -D /pgsql/data --config-file=/pgsql/data/postgresql.conf --listen_addresses=0.0.0.0 --max_worker_processe...
           ├─1719 postgres: pgsql: logger
           ├─1724 postgres: pgsql: checkpointer
           ├─1725 postgres: pgsql: background writer
           ├─1726 postgres: pgsql: walwriter
           ├─1727 postgres: pgsql: autovacuum launcher
           ├─1728 postgres: pgsql: stats collector
           ├─1729 postgres: pgsql: logical replication launcher
           └─1732 postgres: pgsql: postgres postgres 127.0.0.1(37154) idle

[root@node2 ~]# systemctl status patroni
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
   Loaded: loaded (/etc/systemd/system/patroni.service; disabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-09-05 16:09:06 CST; 3min 41s ago
 Main PID: 1882 (patroni)
   CGroup: /system.slice/patroni.service
           ├─1882 /usr/bin/python2 /usr/bin/patroni /etc/patroni.yml
           ├─1898 /usr/pgsql-12/bin/postgres -D /pgsql/data --config-file=/pgsql/data/postgresql.conf --listen_addresses=0.0.0.0 --max_worker_processe...
           ├─1900 postgres: pgsql: logger
           ├─1901 postgres: pgsql: startup   recovering 000000010000000000000003
           ├─1902 postgres: pgsql: checkpointer
           ├─1903 postgres: pgsql: background writer
           ├─1904 postgres: pgsql: stats collector
           ├─1912 postgres: pgsql: postgres postgres 127.0.0.1(35924) idle
           └─1916 postgres: pgsql: walreceiver   streaming 0/3000060

[root@node2 ~]# patronictl -c /etc/patroni.yml list
+ Cluster: pgsql (6868912301204081018) -------+----+-----------+
| Member |       Host      |  Role  |  State  | TL | Lag in MB |
+--------+-----------------+--------+---------+----+-----------+
|  pg1   | 192.168.234.201 | Leader | running |  1 |           |
|  pg2   | 192.168.234.202 |        | running |  1 |       0.0 |
+--------+-----------------+--------+---------+----+-----------+

echo 'export PATRONICTL_CONFIG_FILE=/etc/patroni.yml' >/etc/profile.d/patroni.sh

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

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

[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target
 
[Service]
Type=simple
User=postgres
Group=postgres
#StandardOutput=syslog
ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog
ExecStartPre=-/usr/bin/sudo /bin/chown postgres /dev/watchdog
ExecStart=/usr/bin/patroni /etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
TimeoutSec=30
Restart=no
 
[Install]
WantedBy=multi-user.target

systemctl enable patroni

watchdog:
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

synchronous_mode:true

patronictl edit-config -s 'synchronous_mode=true'

[root@node4 ~]# etcdctl get /service/cn/sync
{"leader":"pg1","sync_standby":"pg2"}

[root@node4 ~]# etcdctl get /service/cn/sync
{"leader":"pg1","sync_standby":null}

synchronous_mode:true
synchronous_mode_strict:true

tags:
    nofailover: true
    noloadbalance: true
    clonefrom: false
    nosync: true

retry_timeout:864000000
synchronous_mode:true

[postgres@node2 ~]$ patronictl --help
Usage: patronictl [OPTIONS] COMMAND [ARGS]...

Options:
  -c, --config-file TEXT  Configuration file
  -d, --dcs TEXT          Use this DCS
  -k, --insecure          Allow connections to SSL sites without certs
  --help                  Show this message and exit.

Commands:
  configure    Create configuration file
  dsn          Generate a dsn for the provided member, defaults to a dsn of...
  edit-config  Edit cluster configuration
  failover     Failover to a replica
  flush        Discard scheduled events (restarts only currently)
  history      Show the history of failovers/switchovers
  list         List the Patroni members for a given Patroni
  pause        Disable auto failover
  query        Query a Patroni PostgreSQL member
  reinit       Reinitialize cluster member
  reload       Reload cluster member configuration
  remove       Remove cluster from DCS
  restart      Restart cluster member
  resume       Resume auto failover
  scaffold     Create a structure for the cluster in DCS
  show-config  Show cluster configuration
  switchover   Switchover to a replica
  version      Output version of patronictl command or a running Patroni...

patronictl edit-config -p 'max_connections=300'

[postgres@node2 ~]$ patronictl list
+ Cluster: pgsql (6868912301204081018) -------+----+-----------+-----------------+
| Member |       Host      |  Role  |  State  | TL | Lag in MB | Pending restart |
+--------+-----------------+--------+---------+----+-----------+-----------------+
|  pg1   | 192.168.234.201 | Leader | running | 25 |           |        *        |
|  pg2   | 192.168.234.202 |        | running | 25 |       0.0 |        *        |
+--------+-----------------+--------+---------+----+-----------+-----------------+

 patronictl restart pgsql

curl -s http://127.0.0.1:8008/patroni | jq

[root@node2 ~]# curl -s http://127.0.0.1:8008/patroni | jq
{
  "database_system_identifier": "6870146304839171063",
  "postmaster_start_time": "2020-09-13 09:56:06.359 CST",
  "timeline": 23,
  "cluster_unlocked": true,
  "watchdog_failed": true,
  "patroni": {
    "scope": "cn",
    "version": "1.6.5"
  },
  "state": "running",
  "role": "replica",
  "xlog": {
    "received_location": 201326752,
    "replayed_timestamp": null,
    "paused": false,
    "replayed_location": 201326752
  },
  "server_version": 120004
}

import psycopg2

conn=psycopg2.connect("postgres://192.168.234.201:5432,192.168.234.202:5432/postgres?target_session_attrs=read-write&password=123456")

#!/bin/bash

VIP=192.168.234.210
GATEWAY=192.168.234.2
DEV=ens33

action=$1
role=$2
cluster=$3

log()
{
  echo "loadvip: $*"|logger
}

load_vip()
{
ip a|grep -w ${DEV}|grep -w ${VIP} >/dev/null
if [ $? -eq 0 ] ;then
  log "vip exists, skip load vip"
else
  sudo ip addr add ${VIP}/32 dev ${DEV} >/dev/null
  rc=$?
  if [ $rc -ne 0 ] ;then
    log "fail to add vip ${VIP} at dev ${DEV} rc=$rc"
    exit 1
  fi

  log "added vip ${VIP} at dev ${DEV}"

  arping -U -I ${DEV} -s ${VIP} ${GATEWAY} -c 5 >/dev/null
  rc=$?
  if [ $rc -ne 0 ] ;then
    log "fail to call arping to gateway ${GATEWAY} rc=$rc"
    exit 1
  fi
  
  log "called arping to gateway ${GATEWAY}"
fi
}

unload_vip()
{
ip a|grep -w ${DEV}|grep -w ${VIP} >/dev/null
if [ $? -eq 0 ] ;then
  sudo ip addr del ${VIP}/32 dev ${DEV} >/dev/null
  rc=$?
  if [ $rc -ne 0 ] ;then
    log "fail to delete vip ${VIP} at dev ${DEV} rc=$rc"
    exit 1
  fi

  log "deleted vip ${VIP} at dev ${DEV}"
else
  log "vip not exists, skip delete vip"
fi
}

log "loadvip start args:'$*'"

case $action in
  on_start|on_restart|on_role_change)
    case $role in
      master)
        load_vip
        ;;
      replica)
        unload_vip
        ;;
      *)
        log "wrong role '$role'"
        exit 1
        ;;
    esac
    ;;
  *)
    log "wrong action '$action'"
    exit 1
    ;;
esac

postgresql:
...
  callbacks:
    on_start: /bin/bash /pgsql/loadvip.sh
    on_restart: /bin/bash /pgsql/loadvip.sh
    on_role_change: /bin/bash /pgsql/loadvip.sh

patronictl reload pgsql

Sep  5 21:32:24 localvm postgres: loadvip: loadvip start args:'on_role_change master pgsql'
Sep  5 21:32:24 localvm systemd: Started Session c7 of user root.
Sep  5 21:32:24 localvm postgres: loadvip: added vip 192.168.234.210 at dev ens33
Sep  5 21:32:25 localvm patroni: 2020-09-05 21:32:25,415 INFO: Lock owner: pg1; I am pg1
Sep  5 21:32:25 localvm patroni: 2020-09-05 21:32:25,431 INFO: no action.  i am the leader with the lock
Sep  5 21:32:28 localvm postgres: loadvip: called arping to gateway 192.168.234.2

yum install -y keepalived

global_defs {
    router_id LVS_DEVEL
}
vrrp_script check_leader {
    script "/usr/bin/curl -s http://127.0.0.1:8008/leader -v 2>&1|grep '200 OK' >/dev/null"
    interval 2
    weight 10
}
vrrp_script check_replica {
    script "/usr/bin/curl -s http://127.0.0.1:8008/replica -v 2>&1|grep '200 OK' >/dev/null"
    interval 2
    weight 5
}
vrrp_script check_can_read {
    script "/usr/bin/curl -s http://127.0.0.1:8008/read-only -v 2>&1|grep '200 OK' >/dev/null"
    interval 2
    weight 10
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 211
    priority 100
    advert_int 1
    track_script {
        check_can_read
        check_replica
    }
    virtual_ipaddress {
       192.168.234.211
    }
}

systemctl start keepalived

yum install -y haproxy

global
    maxconn 100
    log     127.0.0.1 local2

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

listen pgsql
    bind *:5000
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_192.168.234.201_5432 192.168.234.201:5432 maxconn 100 check port 8008
    server postgresql_192.168.234.202_5432 192.168.234.202:5432 maxconn 100 check port 8008
    server postgresql_192.168.234.203_5432 192.168.234.203:5432 maxconn 100 check port 8008

listen pgsql_read
    bind *:6000
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgresql_192.168.234.201_5432 192.168.234.201:5432 maxconn 100 check port 8008
    server postgresql_192.168.234.202_5432 192.168.234.202:5432 maxconn 100 check port 8008
    server postgresql_192.168.234.203_5432 192.168.234.203:5432 maxconn 100 check port 8008

systemctl start haproxy

global_defs {
    router_id LVS_DEVEL
}
vrrp_script check_haproxy {
    script "pgrep -x haproxy"
    interval 2
    weight 10
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 210
    priority 100
    advert_int 1
    track_script {
        check_haproxy
    }
    virtual_ipaddress {
       192.168.234.210
    }
}

systemctl start keepalived

[postgres@node4 ~]$ psql "host=192.168.234.210 port=5000 password=123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.234.201  | f
(1 row)

[postgres@node4 ~]$ psql "host=192.168.234.210 port=6000 password=123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.234.202  | t
(1 row)


[postgres@node4 ~]$ psql "host=192.168.234.210 port=6000 password=123456" -c 'select inet_server_addr(),pg_is_in_recovery()'
 inet_server_addr | pg_is_in_recovery
------------------+-------------------
 192.168.234.203  | t
(1 row)

tags:
    replicatefrom: pg2

bootstrap:
  dcs:
    standby_cluster:
      host: 192.168.234.210
      port: 5432
      primary_slot_name: slot1
      create_replica_methods:
      - basebackup

slots:
  slot1:
    type: physical

standby_cluster:
  host: 192.168.234.210
  port: 5432
  primary_slot_name: slot1
  create_replica_methods:
  - basebackup