Haproxy with Postgresql

Help!
1

Hi,

For a POC, I am testing a haproxy config to allow access to different Postgresql instances on the same server, based on a hostname.
Serveur RHEL8 / HAProxy 1.8 / Postgresql 14
IE :
iaspX.domain.tld → postgresql1:54321
iaspY.domain.tld → postgresql2:54322
etc.

Here is the tested config.

#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
    log         /dev/log local0 debug

    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    # turn on stats unix socket
    stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
    mode                tcp
    log                 global
    option              tcplog
    retries             3
    timeout connect     10s
    timeout client      1m
    timeout server      1m
    timeout check       10s
    maxconn             3000

#---------------------------------------------------------------------
# statistics
#---------------------------------------------------------------------
listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
    stats refresh 20s
    stats realm PSQL Haproxy\ Statistics  # Title text for popup window
    stats show-node
    stats show-legends
    stats show-desc PSQL load balancer stats (master)

#---------------------------------------------------------------------
# main frontend which proxys to the backends
#---------------------------------------------------------------------
frontend pgsql_in
    bind *:5432
    mode tcp
    log global
    tcp-request inspect-delay 10s

    use_backend bk_iasp1 if { hdr(host) -m beg pgsql-lat-iasp1. }
    use_backend bk_iasp2 if { hdr(host) -m beg pgsql-lat-iasp2. }

    #default_backend iasp1

#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
backend bk_iasp1
    mode tcp
    option tcp-check
    tcp-check connect
    log global
    server psql1 127.0.0.1:54321 check

backend bk_iasp2
    mode tcp
    log global
    option tcp-check
    tcp-check connect
    server psql2 127.0.0.1:54322 check

It seems that the use_backend directives, depending on the host, do not work. If I add the default_backend directive, on one of the two postgres instances, the connection works.

Thanks for your help

2

You are trying use HTTP Header (“Host”) for a protocol that is not HTTP (but postqresql). What you are trying to do will not work.

3

Hi,

I’m a stupid guy !
I should have thought of that, it is obvious that there are no http headers in tcp mode.

Thnaks