pg_auto_failover sample cluster

a.md

How to run?

git clone https://gist.github.com/9583fe487bddf853c38a5b8c9db2cf7a.git
cd 9583fe487bddf853c38a5b8c9db2cf7a
docker-compose build
docker-compose down -v
docker-compose up -d
docker-compose exec -u root monitor xinetd

Check if it works

cd 9583fe487bddf853c38a5b8c9db2cf7a
docker-compose exec monitor bash

export PATH=$PATH:/usr/pgsql-11/bin
watch -n 1 /usr/pgsql-11/bin/pg_autoctl show state --pgdata /var/lib/pgsql/11/data

docker-compose.png

docker-compose.yml

version: "3"
services:
  monitor:
    build: .
    restart: always
    command: monitor
    hostname: monitor
  primary:
    build: .
    restart: always
    command: db-server
    hostname: primary
    depends_on:
      - monitor
  standby:
    build: .
    restart: always
    command: db-server
    hostname: standby
    depends_on:
      - monitor
  haproxy:
    image: haproxy:latest
    hostname: haproxy
    depends_on:
      - primary
      - standby
    volumes:
      - ./haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg
    ports:
      - 9090:9090
      - 5432:5432

Dockerfile

FROM centos:7

RUN curl https://install.citusdata.com/community/rpm.sh --output rpm.sh && \
  chmod +x rpm.sh && \
  bash rpm.sh && \
  rm -f rpm.sh

RUN yum install -y \
  pg-auto-failover10_11\
  postgresql11-contrib \
  nmap-ncat \
  xinetd

COPY entrypoint.sh /usr/bin/entrypoint
RUN chmod +x /usr/bin/entrypoint

COPY pgsqlchk_xinetd_primary /etc/xinetd.d/pgsqlchk-primary
COPY pgsqlchk_xinetd_standby /etc/xinetd.d/pgsqlchk-standby
RUN bash -c 'echo "pgsqlchk-primary 23267/tcp" >> /etc/services'
RUN bash -c 'echo "pgsqlchk-standby 23268/tcp" >> /etc/services'
COPY pgsqlchk_check_primary /tmp/pgsqlchk_primary
COPY pgsqlchk_check_standby /tmp/pgsqlchk_standby
RUN chmod +x /tmp/pgsqlchk_primary
RUN chmod +x /tmp/pgsqlchk_standby

USER postgres
ENTRYPOINT ["entrypoint"]

entrypoint.sh

#!/bin/bash

create_monitor () {
  echo "[entrypoint] creating monitor instance..."
  /usr/pgsql-11/bin/pg_autoctl create monitor --pgdata /var/lib/pgsql/11/data --pgctl /usr/pgsql-11/bin/pg_ctl
}

create_sql () {
  echo "[entrypoint] creating SQL instance..."
  /usr/pgsql-11/bin/pg_autoctl create postgres --pgdata /var/lib/pgsql/11/data --monitor postgres://autoctl_node@monitor:5432/pg_auto_failover --nodename $(hostname) --pgctl /usr/pgsql-11/bin/pg_ctl
}

enable_hba_connections () {
  echo "[entrypoint] authorizing neighbors to access monitor instance..."
  echo "host all all all trust" >> /var/lib/pgsql/11/data/pg_hba.conf
  echo "host replication all all trust" >> /var/lib/pgsql/11/data/pg_hba.conf
}

customize_postgres () {
  echo "[entrypoint] customizing pg_auto_failover options..."
  echo "pgautofailover.health_check_period = 1000ms" >> /var/lib/pgsql/11/data/postgresql.conf
  echo "pgautofailover.health_check_max_retries = 4" >> /var/lib/pgsql/11/data/postgresql.conf
  echo "pgautofailover.health_check_retry_delay = 1000ms" >> /var/lib/pgsql/11/data/postgresql.conf
  echo "pgautofailover.health_check_timeout = 900ms" >> /var/lib/pgsql/11/data/postgresql.conf
  echo "pgautofailover.node_considered_unhealthy_timeout = 4000ms" >> /var/lib/pgsql/11/data/postgresql.conf
  echo "pgautofailover.primary_demote_timeout = 100ms" >> /var/lib/pgsql/11/data/postgresql.conf
  echo "pgautofailover.startup_grace_period = 2000ms" >> /var/lib/pgsql/11/data/postgresql.conf
}

start_postgres () {
  echo "[entrypoint] starting postgres..."
  /usr/pgsql-11/bin/pg_autoctl run --pgdata /var/lib/pgsql/11/data
}

wait_for_monitor () {
  while ! nc -z monitor 5432
  do
    echo "[entrypoint] waiting for monitor to start..."
    sleep 1
  done
  # delay for a random period, to avoid collissions of two or more instances starting simultaneously
  sleep $(shuf -i 5-15 -n 1)
}

case "${@}" in
  monitor)
    create_monitor
    enable_hba_connections
    customize_postgres
    start_postgres
    ;;
  db-server)
    wait_for_monitor
    create_sql
    enable_hba_connections
    wait_for_monitor
    start_postgres
    ;;
  *)
    echo "command '${@}' not supported"
    exit 1
esac

haproxy.cfg

global
	daemon
	maxconn 1024
	pidfile /var/run/haproxy.pid

defaults
	balance roundrobin
	timeout client 60s
	timeout connect 60s
	timeout server 60s

listen stats
  bind *:9090
  balance
  mode http
  stats enable
  stats uri /

listen hapostgres
  bind *:5432
  option httpchk
  http-check expect status 200
	default-server inter 1s fall 2 rise 1
  server primary primary:5432 check addr monitor port 23267
  server standby standby:5432 check addr monitor port 23268

pgsqlchk_check_primary

#!/bin/bash

SERVER_TYPE=$(/usr/pgsql-11/bin/psql -d pg_auto_failover -t -c "select reportedstate from pgautofailover.node where nodename = 'primary'" 2> /dev/null)

if [ $SERVER_TYPE == "secondary" ]
then
    /bin/echo -e "HTTP/1.1 206 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "Secondary"
    /bin/echo -e "\r\n"
elif [ $SERVER_TYPE == "primary" ]
then
    /bin/echo -e "HTTP/1.1 200 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "Primary"
    /bin/echo -e "\r\n"
else
    /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "DB Down"
    /bin/echo -e "\r\n"
fi

pgsqlchk_check_standby

#!/bin/bash

SERVER_TYPE=$(/usr/pgsql-11/bin/psql -d pg_auto_failover -t -c "select reportedstate from pgautofailover.node where nodename = 'standby'" 2> /dev/null)

if [ $SERVER_TYPE == "secondary" ]
then
    /bin/echo -e "HTTP/1.1 206 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "Secondary"
    /bin/echo -e "\r\n"
elif [ $SERVER_TYPE == "primary" ]
then
    /bin/echo -e "HTTP/1.1 200 OK\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "Primary"
    /bin/echo -e "\r\n"
else
    /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -e "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -e "\r\n"
    /bin/echo "DB Down"
    /bin/echo -e "\r\n"
fi

pgsqlchk_xinetd_primary

service pgsqlchk-primary
{
  flags           = REUSE
  socket_type     = stream
  port            = 23267
  wait            = no
  user            = postgres
  server          = /tmp/pgsqlchk_primary
  log_on_failure  += USERID
  disable         = no
  only_from       = 0.0.0.0/0
  per_source      = UNLIMITED
}

pgsqlchk_xinetd_standby

service pgsqlchk-standby
{
  flags           = REUSE
  socket_type     = stream
  port            = 23268
  wait            = no
  user            = postgres
  server          = /tmp/pgsqlchk_standby
  log_on_failure  += USERID
  disable         = no
  only_from       = 0.0.0.0/0
  per_source      = UNLIMITED
}