trying haproxy + pgb + pg

gistfile1.txt

root@demo:~# cat /etc/haproxy/haproxy.cfg 
global
    maxconn 100

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 httpchk
    http-check expect status 206
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg0 127.0.0.1:5001 check port 23267
    server pg1 127.0.0.1:5002 check port 23268
    
listen ReadWrite
   mode tcp
   bind 0.0.0.0:8001
   option httpchk
   http-check expect status 203
   default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
   server pg0 127.0.0.1:5001 check port 23267
   server pg1 127.0.0.1:5002 check port 23268

###########
postgres@demo:~/temp/pgb/db1$ cat db1.ini 
[databases]

postgres = port=5432 host=localhost

[pgbouncer]

;; auth_type = hba ;; if set then only allow user from pg_hba.conf below
auth_type = trust
;; allow via md5 auth
;; auth_hba_file = /etc/pgbouncer/pg_hba.conf
;; auth_file = /etc/pgbouncer/userlist1.txt
;; list of user/md5password text file

admin_users = postgres

auth_user = postgres
auth_query = SELECT p_user, p_password FROM public.lookup($1)

logfile = /var/log/postgresql/db1/pgbouncer.log
pidfile = /var/run/postgresql/db1/pgbouncer.pid

;;;
;;; Where to wait for clients
;;;

;; IP address or * which means all IPs
listen_addr = *
listen_port = 5001
unix_socket_dir = /var/run/postgresql/db1

###############
postgres@demo:~/temp/pgb/db2$ cat db2.ini 
[databases]

postgres = port=5433 host=127.0.0.1

[pgbouncer]

;; auth_type = hba ;; if set then only allow user from pg_hba.conf below
auth_type = trust
;; allow via md5 auth
;; auth_hba_file = /etc/pgbouncer/pg_hba.conf
;; auth_file = /etc/pgbouncer/userlist2.txt
;; list of user/md5password text file

admin_users = postgres

auth_user = postgres
auth_query = SELECT p_user, p_password FROM public.lookup($1)

logfile = /var/log/postgresql/db2/pgbouncer.log
pidfile = /var/run/postgresql/db2/pgbouncer.pid

;;;
;;; Where to wait for clients
;;;

;; IP address or * which means all IPs
listen_addr = 127.0.0.1
listen_port = 5002
unix_socket_dir = /var/run/postgresql/db2


######################
root@demo:~# cat /opt/pgsqlchk1
#!/usr/bin/bash

export PGPASSWORD="1234"


VALUE=`timeout 5 /usr/bin/psql -t -d postgres -h 127.0.0.1 -U postgres -p 5432 -c 'select not pg_is_in_recovery()' 2>/dev/null`
rc=$?
VALUE=`timeout 5 /usr/bin/psql -t -d postgres -h 127.0.0.1 -U postgres -p 5001 -c 'select not pg_is_in_recovery()' 2>/dev/null`
rc=$? || $rc
# Check the output. If it is not empty then everything is fine and we return something. Else, we just do not return anything.

if [ $rc -ne 0 ]
then
    /bin/echo -ne "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "DB Down"
    /bin/echo -ne "\r\n"
elif [ $VALUE == "f" ]
then
    /bin/echo -ne "HTTP/1.1 206 OK\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "Standby"
    /bin/echo -ne "\r\n"
elif [ $VALUE == "t" ]
then
    /bin/echo -ne "HTTP/1.1 203 OK\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "Primary"
    /bin/echo -ne "\r\n"
else
    /bin/echo -ne "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "DB Down"
    /bin/echo -ne "\r\n"
fi

####################
root@demo:~# cat /opt/pgsqlchk2
#!/usr/bin/bash

export PGPASSWORD="1234"


VALUE=`timeout 5 /usr/bin/psql -t -d postgres -h 127.0.0.1 -U postgres -p 5433 -c 'select not pg_is_in_recovery()' 2>/dev/null`
rc=$?
VALUE=`timeout 5 /usr/bin/psql -t -d postgres -h 127.0.0.1 -U postgres -p 5002 -c 'select not pg_is_in_recovery()' 2>/dev/null`
rc=$? || $rc
# Check the output. If it is not empty then everything is fine and we return something. Else, we just do not return anything.

if [ $rc -ne 0 ]
then
    /bin/echo -ne "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "DB Down"
    /bin/echo -ne "\r\n"
elif [ $VALUE == "f" ]
then
    /bin/echo -ne "HTTP/1.1 206 OK\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "Standby"
    /bin/echo -ne "\r\n"
elif [ $VALUE == "t" ]
then
    /bin/echo -ne "HTTP/1.1 203 OK\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "Primary"
    /bin/echo -ne "\r\n"
else
    /bin/echo -ne "HTTP/1.1 503 Service Unavailable\r\n"
    /bin/echo -ne "Content-Type: Content-Type: text/plain\r\n"
    /bin/echo -ne "\r\n"
    /bin/echo "DB Down"
    /bin/echo -ne "\r\n"
fi

###########################
root@demo:~# cat /etc/xinetd.d/pgsqlchk1
service pgsqlchk1  
{  
        flags           = REUSE  
        socket_type     = stream  
        port            = 23267  
        wait            = no  
        user            = nobody  
        server          = /opt/pgsqlchk1  
        log_on_failure  += USERID  
        disable         = no  
        only_from       = 0.0.0.0/0
        per_source      = UNLIMITED  
}
root@demo:~# cat /etc/xinetd.d/pgsqlchk2
service pgsqlchk2
{  
        flags           = REUSE
        socket_type     = stream
        port            = 23268
        wait            = no
        user            = nobody
        server          = /opt/pgsqlchk2
        log_on_failure  += USERID
        disable         = no
        only_from       = 0.0.0.0/0
        per_source      = UNLIMITED
}

#########################
psql "postgresql://127.0.0.1:8002,127.0.0.1:8001/postgres?target_session_attrs=any"

pg_basebackup -D db2 -R -c fast -C -S db2 -d "port=5432"

haproxy does not support multi host connection for pg, even if health check marks node down.....