GoWiki | ston1th | Postgres HA Patroni

# Postgres HA Patroni

## Setup

* pg1
  * `etcd` `postgres` `patroni`
  * `192.168.0.10`
* pg2
  * `etcd` `postgres` `patroni`
  * `192.168.0.11`
* pg3
  * `etcd`
  * `192.168.0.12`

## Install

Nodes: `pg1` `pg2`

```
apt install -y apt-transport-https
add-apt-repository universe
echo "deb https://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
apt update
apt install -y postgresql-11 patroni arping
systemctl stop postgresql
systemctl disable postgresql
```

Nodes: `pg1` `pg2` `pg3`

```
apt install -y etcd
```

## Configuration

### etcd

```
cat <<EOF>> /etc/hosts
192.168.0.10 pg1
192.168.0.11 pg2
192.168.0.12 pg3
EOF
```

```
cat <<EOF> /etc/default/etcd
ETCD_LISTEN_PEER_URLS="http://0.0.0.0:2380"
ETCD_LISTEN_CLIENT_URLS="http://0.0.0.0:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://$(hostname):2380"
ETCD_INITIAL_CLUSTER="pg1=http://pg1:2380,pg2=http://pg2:2380,pg3=http://pg3:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://$(hostname):2379"
ETCD_INITIAL_CLUSTER_TOKEN="pg12"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_NAME="$(hostname)"
EOF
systemctl enable etcd
systemctl restart etcd
```

### Patroni

```
cat <<EOF> /etc/patroni/dcs.yml
etcd:
  hosts: pg1:2379,pg2:2379,pg3:2379
EOF
```

Nodes `pg1` `pg2`:

```
# fix the config script
sed -i 's#/@DCS_CONFIG@/${DCS_CONFIG}/#;@DCS_CONFIG@;${DCS_CONFIG};#' /usr/bin/pg_createconfig_patroni

pg_createconfig_patroni --hostip=$(host $(hostname)|cut -d" " -f4) --network=192.168.0.0/24 11 pg12
mv /etc/patroni/11-pg12.yml /etc/patroni/config.yml
sed -i '/^postgresql:/ a\  callbacks:\n    on_role_change: /usr/bin/sudo /usr/bin/failover.sh' /etc/patroni/config.yml
```

Custom service:

```
systemctl disable patroni
sed '/^# Start/ i\ExecStartPre=/bin/mkdir -m 2750 -p /var/run/postgresql/11-pg12.pg_stat_tmp' /lib/systemd/system/patroni.service > /etc/systemd/system/patroni.service
systemctl enable patroni
```

### Virtual IP Manager

vipman config:
```
cat <<EOF> /etc/patroni/vipman.conf
master=192.168.0.13/24
replica=192.168.0.14/24
iface=ens18
version=11
cluster=pg12
namespace=postgresql-common
etcd_endpoints=http://pg1:2379,http://pg2:2379,http://pg3:2379
loop_sleep=10
EOF
chown root:root /etc/patroni/vipman.conf
chmod 640 /etc/patroni/vipman.conf
```

vipman script:
```
cat <<'EOF'> /usr/bin/vipman
#!/bin/bash

errx() { echo "error: $1" >&2; exit 1; }
add_ip() { /sbin/ip addr add $1 dev $2 label $2:$3 2>/dev/null; announce $1 $2 & }
del_ip() { /sbin/ip addr del $1 dev $2 2>/dev/null; }
has_ip() { /sbin/ip addr show dev $2 2>/dev/null | /bin/grep -q "$1"; }
add_master() { [[ ${has_master} -eq 0 ]] && { add_ip ${master} ${iface} "master"; has_master=1; }; }
del_master() { [[ ${has_master} -eq 1 ]] && { del_ip ${master} ${iface}; has_master=0; }; }
add_replica() { [[ ${has_replica} -eq 0 ]] && { add_ip ${replica} ${iface} "replica"; has_replica=1; }; }
del_replica() { [[ ${has_replica} -eq 1 ]] && { del_ip ${replica} ${iface}; has_replica=0; }; }
del_ips() { del_master; del_replica; }
announce() {
  /usr/sbin/arping -q -A -c1 -i $2 ${1%%/*} 2>/dev/null
  sleep 2
  /usr/sbin/arping -q -U -c1 -i $2 ${1%%/*} 2>/dev/null
}
etcdctl() { /usr/bin/etcdctl --endpoints "${etcd_endpoints}" $@ 2>/dev/null; }

role_master='"master"'
role_replica='"replica"'
state_running='"running"'
state_stopped='"stopped"'
maintenance_mode='"pause":true'

conf="/etc/patroni/vipman.conf"
[[ ! -f ${conf} ]] && errx "missing config file: ${conf}"
. ${conf}

[[ -z "${master}" ]] && errx "master ip is empty"
[[ -z "${replica}" ]] && errx "replica ip is empty"
[[ -z "${iface}" ]] && errx "interface is empty"
[[ -z "${namespace}" ]] && errx "namespace is empty"
[[ -z "${version}" ]] && errx "version is empty"
[[ -z "${cluster}" ]] && errx "cluster is empty"
[[ -z "${etcd_endpoints}" ]] && errx "etcd_endpoints is empty"
[[ -z "${loop_sleep}" ]] && errx "loop_sleep is empty"

path="/${namespace}/${version}-${cluster}"
members="${path}/members"
config="${path}/config"
host="$(hostname)"
unhealthy=0
maintenance=0

has_ip ${master} ${iface} && has_master=1 || has_master=0
has_ip ${replica} ${iface} && has_replica=1 || has_replica=0

while :; do
  if ! etcdctl cluster-health >/dev/null; then
    [[ ${unhealthy} -eq 0 ]] && { echo "etcd cluster is unhealthy!"; unhealthy=1; }
    /bin/sleep ${loop_sleep}
    continue
  else
    [[ ${unhealthy} -eq 1 ]] && { echo "etcd cluster is healthy again!"; unhealthy=0; }
  fi
  if etcdctl get ${config} | /bin/grep -q "${maintenance_mode}"; then
    [[ ${maintenance} -eq 0 ]] && { echo "patroni entered maintenance mode!"; maintenance=1; }
    /bin/sleep ${loop_sleep}
    continue
  else
    [[ ${maintenance} -eq 1 ]] && { echo "patroni left maintenance mode!"; maintenance=0; }
  fi
  member="$(etcdctl ls ${members} | /bin/grep -v "/${host}$")"
  role="$(etcdctl get ${members}/${host} | /usr/bin/jq -M .role)" || { /bin/sleep ${loop_sleep}; continue; }
  state="$(etcdctl get ${members}/${host} | /usr/bin/jq -M .state)" || { /bin/sleep ${loop_sleep}; continue; }
  case ${role} in
    ${role_master})
      case ${state} in
        ${state_running})
          add_master
          if [[ -z "${member}" ]]; then
            add_replica
          else
            member_state="$(etcdctl get ${member} | /usr/bin/jq -M .state)"
            case ${member_state} in
              ${state_running}) del_replica ;;
              ${state_stopped}) add_replica ;;
            esac
          fi
        ;;
        ${state_stopped}) del_ips ;;
      esac
    ;;
    ${role_replica})
      case ${state} in
        ${state_running}) del_master; add_replica ;;
        ${state_stopped}) del_ips ;;
      esac
    ;;
    *) del_ips ;;
  esac
  /bin/sleep $loop_sleep
done
EOF
chmod 755 /usr/bin/vipman

cat <<EOF> /etc/systemd/system/vipman.service
[Unit]
Description=Virtual IP Manager for Patroni two-node Cluster
After=network.target

[Service]
Type=simple

User=root
Group=root

ExecStart=/usr/bin/vipman
Restart=always

[Install]
WantedBy=multi-user.target
EOF

systemctl enable vipman
systemctl start vipman
```

```
systemctl restart patroni
```

## Commands

Create `patronictl.yaml`:

```
mkdir -p /root/.config/patroni
grep -E "^scope:|^namespace:" /etc/patroni/config.yml > /root/.config/patroni/patronictl.yaml
grep -A1 "^etcd:" /etc/patroni/config.yml >> /root/.config/patroni/patronictl.yaml
```

Cluster Status:

```
patronictl list
etcdctl cluster-health
```

Manual failover:

```
patronictl switchover
```

# Sources

https://github.com/cybertec-postgresql/vip-manager

https://postgresconf.org/system/events/document/000/000/228/Patroni_tutorial_4x3-2.pdf

https://github.com/zalando/patroni/blob/master/docs/SETTINGS.rst