haproxy external check

gistfile1.txt

postgres@demo:~/temp$ psql -p 5432 -h 127.0.0.1 -d postgres -U postgres -c 'select pg_is_in_recovery();'
 pg_is_in_recovery 
-------------------
 f
(1 row)

postgres@demo:~/temp$ psql -p 5433 -h 127.0.0.1 -d postgres -U postgres -c 'select pg_is_in_recovery();'
 pg_is_in_recovery 
-------------------
 t
(1 row)

postgres@demo:~/temp$ psql -p 5434 -h 127.0.0.1 -d postgres -U postgres -c 'select pg_is_in_recovery();'
 pg_is_in_recovery 
-------------------
 t
(1 row)

postgres@demo:~/temp$ cat pgdb1.ini 
[databases]

postgres = port=5432 host=127.0.0.1

[pgbouncer]


;; auth_type = trust
admin_users = postgres
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

auth_user = postgres
logfile = /tmp/db1.log
pidfile = /tmp/db1.pid

listen_addr = *
listen_port = 5001
unix_socket_dir = /var/run/postgresql
;; unix_socket_dir = /var/run/postgresql ;; /tmp/db1
;; query_wait_timeout = 50
server_connect_timeout = 2
server_login_retry = 5
postgres@demo:~/temp$ cat pgdb2.ini 
[databases]

postgres = port=5433 host=127.0.0.1

[pgbouncer]


auth_type = trust
admin_users = postgres
auth_user = postgres
logfile = /tmp/db2.log
pidfile = /tmp/db2.pid

listen_addr = 127.0.0.1
listen_port = 5002
unix_socket_dir = /tmp/db2
;; query_wait_timeout = 50
server_connect_timeout = 2
server_login_retry = 5
postgres@demo:~/temp$ cat pgdb3.ini 
[databases]

postgres = port=5434 host=127.0.0.1

[pgbouncer]


auth_type = trust
admin_users = postgres
auth_user = postgres
logfile = /tmp/db3.log
pidfile = /tmp/db3.pid

listen_addr = 127.0.0.1
listen_port = 5003
unix_socket_dir = /tmp/db3
;; query_wait_timeout = 50
server_connect_timeout = 2
server_login_retry = 5
postgres@demo:~/temp$ cat /opt/primary.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 == 'f' ]
then
	logger -s "I am primary"
	exit 0
else
	logger -s "I am not primary"
	exit 1
fi

exit 0
postgres@demo:~/temp$ cat /opt/replica.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 5434 -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
else
	logger -s "I am not replica"
	exit 1
fi

exit 0
postgres@demo:~/temp$ 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 ReadOnly
    mode tcp
    bind 0.0.0.0:8002
    option log-health-checks
    option external-check
    external-check path "/opt"
    external-check command /opt/replica.sh
    server pg0 127.0.0.1:5001 check
    server pg1 127.0.0.1:5002 check
    server pg2 127.0.0.1:5003 check

listen ReadWrite
   mode tcp
   bind 0.0.0.0:8001
   option log-health-checks
   option external-check
   external-check path "/opt"
   external-check command /opt/primary.sh
   server pg0 127.0.0.1:5001 check
   server pg1 127.0.0.1:5002 check
   server pg2 127.0.0.1:5003 check

#note the ports returned are from the respective pg server

postgres@demo:~$ psql -p 8001 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5432
(1 row)

postgres@demo:~$ psql -p 8002 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5434
(1 row)

postgres@demo:~$ psql -p 8002 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5433
(1 row)

i take primary down

postgres@demo:~/temp$ export PATH=/usr/lib/postgresql/14/bin:$PATH
postgres@demo:~/temp$ pg_ctl -D db1 -l db1.log stop
waiting for server to shut down.... done
server stopped
postgres@demo:~/temp$ psql -p 8001 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
FATAL:  server login has been failing, try again later (server_login_retry)
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
connection to server was lost
postgres@demo:~/temp$ psql -p 8002 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5434
(1 row)

postgres@demo:~/temp$ psql -p 8002 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5433
(1 row)

i take one replica down (have some hardcoding due to single server but)

postgres@demo:~/temp$ pg_ctl -D db1 -l db1.log start
waiting for server to start.... done
server started
postgres@demo:~/temp$ pg_ctl -D db2 -l db2.log stop
waiting for server to shut down.... done
server stopped
postgres@demo:~/temp$ psql -p 8001 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5432
(1 row)

postgres@demo:~/temp$ psql -p 8002 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5434
(1 row)

postgres@demo:~/temp$ psql -p 8002 -h 127.0.0.1 -d postgres -U postgres -c 'select inet_server_port();'
 inet_server_port 
------------------
             5434
(1 row)