Proxy pg_autofailover

[laurent6]

Hi,

What proxy use with pg_autofailover . Pgadmin can't use 2 address pg server so we need one front that redirect on the correct node ( master to write operation )

[s4ke]

I would suggest using haproxy using xinetd on the data nodes.

xinetd as a lightweight http server just for healthchecks. haproxy will then use these http endpoints for healthchecking / selecting the primary.

then you can setup xinetd according to this (sorry for just ansible and templated stuff only):

https://github.com/neuroforgede/pg_auto_failover_ansible/blob/master/roles/postgres-cluster-xinetd/tasks/main.yml https://github.com/neuroforgede/pg_auto_failover_ansible/tree/master/roles/postgres-cluster-xinetd/templates

haproxy config should then look similar to this:

global
    maxconn {{ postgres_haproxy_maxconn }}
 
defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s
 
listen stats
    mode http
    bind *:{{ postgres_haproxy_stats_port }}
    stats enable
    stats uri /
 
listen ReadWrite
    bind *:{{ postgres_haproxy_readwrite_port }}
    option httpchk
    http-check expect status 200
    fullconn {{ postgres_haproxy_maxconn }}
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
{% for upstream in postgres_haproxy_nodes %}
    server {{ upstream.name }} {{ upstream.address }} maxconn {{ upstream.maxconn }} check port {{ upstream.healthcheck_port }}
{% endfor %} 

listen ReadOnly
    bind *:{{ postgres_haproxy_readonly_port }}
    option httpchk
    http-check expect status 206
    fullconn {{ postgres_haproxy_maxconn }}
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
{% for upstream in postgres_haproxy_nodes %}
    server {{ upstream.name }} {{ upstream.address }} maxconn {{ upstream.maxconn }} check port {{ upstream.healthcheck_port }}
{% endfor %} 

My suggestion here: use haproxy on the same host as your application. That's also why we did not include it in our playbooks.

[laurent6]

When autofailover is done how redirect auto in the master

[s4ke]

with the haproxy properly set up, target the readwrite port of the haproxy and it will automatically target the primary.

[s4ke]

This should help for public facing networks: https://github.com/neuroforgede/pg_auto_failover_ansible/wiki/HAProxy