I am trying to connect with two standby postgresql servers using haproxy one standby server is configured against pgbouncer port 6432 (10.114.16.33) and other on with postgresql port 5432 (10.114.16.34) but when I connect through haproxy ip and port it always connects with port 5432 (10.114.16.33)
psql -h 10.114.16.50 -p 5001 -U postgres -d ctdb
global
log /dev/log local0
log /dev/log local1 notice
#chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin
stats timeout 30s
user haproxy
group haproxy
daemon
# Default SSL material locations
ca-base /etc/ssl/certs
crt-base /etc/ssl/private
# Default ciphers to use on SSL-enabled listening sockets.
# For more information, see ciphers(1SSL). This list is from:
# https://hynek.me/articles/hardening-your-web-servers-ssl-ciphers/
#ssl-default-bind-ciphers PROFILE=SYSTEM
#ssl-default-bind-options no-sslv3
defaults
log global
option dontlognull
timeout connect 500000ms
timeout client 50m # 10 minutes
timeout server 50m # 10 minutes
# timeout client-fin 10s
# timeout server-fin 10s
Stats configuration
listen stats
# Set mode to HTTP
mode http
# Bind to port 8080
bind *:8080
# Enable stats
stats enable
# Stats URI
stats uri /
Frontend for write operations on port 5000
frontend postgresql_write
bind 10.114.16.50:5000
mode tcp
option tcplog
Route all traffic to the write backend
default_backend write_backend
Frontend for read operations on port 5001
frontend postgresql_read
bind 10.114.16.50:5001
mode tcp
option tcplog
# Route all traffic to the read backend
default_backend read_backend
Backend for write operations (primary node)
backend write_backend
mode tcp
option tcp-check
default-server inter 3s fall 3 rise 2
server primary_node 10.114.16.28:5432 check
Backend for read operations (secondary nodes)
backend read_backend
mode tcp
option tcp-check
balance roundrobin
default-server inter 3s fall 3 rise 2
server secondary_node1 10.114.16.33:5432 check
server secondary_node2 10.114.16.34:5432 check
[postgres@testbed06 haproxy]$ haproxy -vv
HAProxy version 3.0.1-471a1b2 2024/06/10 - https://haproxy.org/
Status: long-term supported branch - will stop receiving fixes around Q2 2029.
Known bugs: http://www.haproxy.org/bugs/bugs-3.0.1.html
Running on: Linux 5.14.0-427.26.1.el9_4.x86_64 #1 SMP PREEMPT_DYNAMIC Thu Jul 18 03:21:12 PDT 2024 x86_64
Build options :
TARGET = linux-glibc
CC = cc
CFLAGS = -O2 -g -fwrapv -DMAX_SESS_STKCTR=12
OPTIONS = USE_LINUX_TPROXY=1 USE_CRYPT_H=1 USE_GETADDRINFO=1 USE_OPENSSL=1 USE_LUA=1 USE_SLZ=1 USE_SYSTEMD=1 USE_PROMEX=1 USE_PCRE2=1
DEBUG =
Feature list : -51DEGREES +ACCEPT4 +BACKTRACE -CLOSEFROM +CPU_AFFINITY +CRYPT_H -DEVICEATLAS +DL -ENGINE +EPOLL -EVPORTS +GETADDRINFO -KQUEUE -LIBATOMIC +LIBCRYPT +LINUX_CAP +LINUX_SPLICE +LINUX_TPROXY +LUA +MATH -MEMORY_PROFILING +NETFILTER +NS -OBSOLETE_LINKER +OPENSSL -OPENSSL_AWSLC -OPENSSL_WOLFSSL -OT -PCRE +PCRE2 -PCRE2_JIT -PCRE_JIT +POLL +PRCTL -PROCCTL +PROMEX -PTHREAD_EMULATION -QUIC -QUIC_OPENSSL_COMPAT +RT +SHM_OPEN +SLZ +SSL -STATIC_PCRE -STATIC_PCRE2 +SYSTEMD +TFO +THREAD +THREAD_DUMP +TPROXY -WURFL -ZLIB
Default settings :
bufsize = 16384, maxrewrite = 1024, maxpollevents = 200
Built with multi-threading support (MAX_TGROUPS=16, MAX_THREADS=256, default=8).
Built with OpenSSL version : OpenSSL 3.0.7 1 Nov 2022
Running on OpenSSL version : OpenSSL 3.0.7 1 Nov 2022
OpenSSL library supports TLS extensions : yes
OpenSSL library supports SNI : yes
OpenSSL library supports : TLSv1.0 TLSv1.1 TLSv1.2 TLSv1.3
OpenSSL providers loaded : default
Built with Lua version : Lua 5.4.4
Built with the Prometheus exporter as a service
Built with network namespace support.
Built with libslz for stateless compression.
Compression algorithms supported : identity("identity"), deflate("deflate"), raw-deflate("deflate"), gzip("gzip")
Built with transparent proxy support using: IP_TRANSPARENT IPV6_TRANSPARENT IP_FREEBIND
Built with PCRE2 version : 10.40 2022-04-14
PCRE2 library supports JIT : no (USE_PCRE2_JIT not set)
Encrypted password support via crypt(3): yes
Built with gcc compiler version 11.4.1 20231218 (Red Hat 11.4.1-3)
Available polling systems :
epoll : pref=300, test result OK
poll : pref=200, test result OK
select : pref=150, test result OK
Total: 3 (3 usable), will use epoll.
Available multiplexer protocols :
(protocols marked as <default> cannot be specified using 'proto' keyword)
h2 : mode=HTTP side=FE|BE mux=H2 flags=HTX|HOL_RISK|NO_UPG
h1 : mode=HTTP side=FE|BE mux=H1 flags=HTX|NO_UPG
<default> : mode=HTTP side=FE|BE mux=H1 flags=HTX
fcgi : mode=HTTP side=BE mux=FCGI flags=HTX|HOL_RISK|NO_UPG
none : mode=TCP side=FE|BE mux=PASS flags=NO_UPG
<default> : mode=TCP side=FE|BE mux=PASS flags=
Available services : prometheus-exporter
Available filters :
[BWLIM] bwlim-in
[BWLIM] bwlim-out
[CACHE] cache
[COMP] compression
[FCGI] fcgi-app
[SPOE] spoe
[TRACE] trace
Sep 05 06:34:05 testbed06 systemd[1]: Started HAProxy Load Balancer.
Sep 05 06:34:20 testbed06 haproxy[2483649]: 10.114.16.50:36520 [05/Sep/2024:15:34:20.840] postgresql_read read_backend/secondary_node1 1/0/4 14 -- 1/1/0/0/0 0/0
Sep 05 06:34:31 testbed06 haproxy[2483649]: 10.114.16.50:57930 [05/Sep/2024:15:34:27.982] postgresql_read read_backend/secondary_node2 1/0/3196 2736 SD 1/1/0/0/0 0/0
Sep 05 06:39:43 testbed06 haproxy[2483649]: 10.114.16.50:43264 [05/Sep/2024:15:39:43.302] postgresql_read read_backend/secondary_node1 1/1/5 14 -- 1/1/0/0/0 0/0
Sep 05 06:39:49 testbed06 haproxy[2483649]: 10.114.16.50:43272 [05/Sep/2024:15:39:47.470] postgresql_read read_backend/secondary_node2 1/0/1735 2736 SD 1/1/0/0/0 0/0
Sep 05 06:39:54 testbed06 haproxy[2483649]: 10.114.16.50:45238 [05/Sep/2024:15:39:54.610] postgresql_read read_backend/secondary_node1 1/1/4 14 -- 1/1/0/0/0 0/0
Sep 05 06:40:02 testbed06 haproxy[2483649]: 10.114.16.50:45242 [05/Sep/2024:15:39:58.289] postgresql_read read_backend/secondary_node2 1/1/4050 2736 SD 1/1/0/0/0 0/0
Any idea ?