Execute on each Postgresql node the following actions # Create check script sudo vi /usr/lib/postgresql/13/bin/pgsqlchk #!/bin/bash # This script checks if a postgres server is healthy running on localhost. It will return: # "HTTP/1.x 200 OK\r" (if postgres is running smoothly) # - OR - # "HTTP/1.x 500 Internal Server Error\r" (else) # The purpose of this script is make haproxy capable of monitoring postgres properly # It is recommended that a low-privileged postgres user is created to be used by this script. # For eg. create user healthchkusr login password 'hc321'; PGBIN=/usr/pgsql-10/bin PGSQL_HOST="localhost" PGSQL_PORT="5432" PGSQL_DATABASE="postgres" PGSQL_USERNAME="postgres" export PGPASSWORD="passwd" TMP_FILE="/tmp/pgsqlchk.out" ERR_FILE="/tmp/pgsqlchk.err" # We perform a simple query that should return a few results VALUE=`/usr/lib/postgresql/13/bin/psql -t -h localhost -U postgres -p 5432 -c "select pg_is_in_recovery()" 2> /dev/null` # Check the output. If it is not empty then everything is fine and we return something. Else, we just do not return anything. if [ $VALUE == "t" ] 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 "Standby" /bin/echo -e "\r\n" elif [ $VALUE == "f" ] 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 # Make script executable sudo chmod a+x /usr/lib/postgresql/13/bin/pgsqlchk # install xinetd and telnet sudo apt-get install xinetd telnet -y # Create xinitd file sudo vi /etc/xinetd.d/pgsqlchk service pgsqlchk { flags = REUSE socket_type = stream port = 23267 wait = no user = nobody server = /usr/lib/postgresql/13/bin/pgsqlchk log_on_failure += USERID disable = no only_from = 0.0.0.0/0 per_source = UNLIMITED } sudo bash -c 'echo "pgsqlchk 23267/tcp # pgsqlchk" >> /etc/services' sudo systemctl restart xinetd # Check if service is working telnet 127.0.0.1 23267 Trying 127.0.0.1... Connected to 127.0.0.1. Escape character is '^]'. HTTP/1.1 206 OK Content-Type: Content-Type: text/plain Standby Connection closed by foreign host. Execute on HaProxy following actions # Install HaProxy sudo apt-get install haproxy -y sudo vi /etc/hosts Add pi6 192.168.2.106 Add pi7 192.168.2.107 sudo vi /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 *:10000 stats enable stats refresh 10s stats uri / listen Postgres bind *:5432 option httpchk http-check expect status 200 default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions server pi6 pi6:5432 maxconn 100 check port 23267 server pi7 pi7:5432 maxconn 100 check port 23267 systemctl restart haproxy Check HaProxy # Install postgres client sudo apt-get install postgresql-client # Start psql client psql -h localhost -U postgres postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
Check HaProxy portal http://192.168.2.107:5000
