etcd postgres patroni192.168.0.10etcd postgres patroni192.168.0.11etcd192.168.0.12Nodes: 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
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
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
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
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
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