pgbouncer haproxy external-check primary replica failover

gistfile1.txt

primary : 192.168.64.11
replica : 192.168.64.12

cat /etc/haproxy/haproxy.cfg
global
    maxconn 100
    stats socket ipv4@127.0.0.1:9999 level admin
    stats socket /var/run/haproxy.sock mode 666 level admin
    stats timeout 2m
    external-check

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 postgres
    mode tcp
    bind 0.0.0.0:8000
    option log-health-checks
    # option pgsql-check user postgres
    # server srv1 192.168.64.11:6432 check
    # server srv2 192.168.64.12:6432 check backup

    option external-check
    external-check path "/opt"
    external-check command /opt/run.sh
    server srv1 192.168.64.11:6432 check inter 10s  fall 5  rise 5
    server srv2 192.168.64.12:6432 check inter 10s  fall 5  rise 5 backup
    
    
######## /opt/run.sh
root@primary:~# cat /opt/run.sh 
#!/bin/bash

haproxy_ip=$1
haproxy_port=$2
lb_ip=$3
lb_port=$4

export PATH=$PATH:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
export PGPASSWORD=1234
logger -s "psql -p $4 -h $3 -U postgres -d postgres -c 'select 1'"

VALUE=`timeout 5 /usr/bin/psql -t -d postgres -h $3 -U postgres -p 5432 -c 'select pg_is_in_recovery()' 2>/dev/null`
rc=$?
if [ $rc -ne 0 ]
then
	logger -s "cannot connect to postgres, exiting"
	exit 1
fi

VALUE=`timeout 5 /usr/bin/psql -t -d postgres -h $3 -U postgres -p $4 -c 'select pg_is_in_recovery()' 2>/dev/null`
rc=$?
if [ $rc -ne 0 ]
then
	logger -s "cannot connect to pgbouncer, exiting"
	exit 1
fi

if [ $VALUE == 't' ]
then
	logger -s "I am replica"
	exit 0
elif [ $VALUE == 'f' ]
then
	logger -s "I am primary"
	exit 0
else
	logger -s "I am down"
	exit 1
fi

exit 0


### pgbouncer config
root@primary:~# cat /etc/pgbouncer/pgbouncer.ini | grep -v '^;' | grep -v '^$'
[databases]
* = port=5432 host=127.0.0.1
[users]
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
admin_users = postgres
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

### pgbouncer config on replica
root@replica:~# cat /etc/pgbouncer/pgbouncer.ini | grep -v '^;' | grep -v '^$'
[databases]
* = port=5432 host=127.0.0.1
[users]
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
admin_users = postgres
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

####### tests
on primary
timeout 5 psql -p 8000 -h 127.0.0.1 -U postgres -d postgres -c 'select pg_is_in_recovery()'
 pg_is_in_recovery 
-------------------
 f
(1 row)

## when primary db us down but pgbouncer is up, it still fails over

# fig 1
timeout 5 psql -p 8000 -h 127.0.0.1 -U postgres -d postgres -c 'select pg_is_in_recovery()'
 pg_is_in_recovery 
-------------------
 t
(1 row)


# when the db server is up
timeout 5 psql -p 8000 -h 127.0.0.1 -U postgres -d postgres -c 'select pg_is_in_recovery()'
 pg_is_in_recovery 
-------------------
 f
(1 row)