Patroni: "connect_address" parameter and replication

[vitabaks]

I need to provide a client connections via a pgbouncer (port 6432).
App--5000-->haproxy(+confd)--6432-->pgbouncer--5432-->PostgreSQL(master).

And for replication nodes to use 5432 (without pgbouncer).
PostgreSQL(master)<--5432--PostgreSQL(replica)

patroni.yml:
.....
postgresql:
listen: 10.128.64.136:5432
connect_address: 10.128.64.136:6432
.....

postgresql log:
FATAL: could not connect to the primary server: ERROR: Unsupported startup parameter: replication

cat /var/lib/postgresql/9.4/main/recovery.conf
primary_slot_name = 'node3'
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'user=replicator password=rep-pass host=10.128.64.131 port=6432 sslmode=prefer sslcompression=1 application_name=node3'

https://patroni.readthedocs.io/en/latest/SETTINGS.html
connect_address: IP address + port through which Postgres is accessible from other nodes and applications.

listen: IP address + port that Postgres listens to; must be accessible from other nodes in the cluster, if you’re using streaming replication. Multiple comma-separated addresses are permitted, as long as the port component is appended after to the last one with a colon, i.e. listen: 127.0.0.1,127.0.0.2:5432. Patroni will use the first address from this list to establish local connections to the PostgreSQL node.

---

Debian 9 (4.9.0-6-amd64)
postgresql-9.4.18
patroni version 1.4.4 (pip)
pgbouncer 1.8.1
haproxy 1.8.9
confd 0.16.0
etcd Version: 3.3.6

[CyberDem0n]

Hi, @vitabaks,

why do you have port 6543 in the connect_address? This is pgbouncer port, not postgres.

Whatever there is in the connect_address is written in DCS and used by other Patroni nodes for service discovery.

I thought documentation is quite clear, it says: IP address + port through which Postgres (not pgbouncer) is accessible from other nodes and applications.

[vitabaks]

Hi, Alexander!
I realized that the replicas use the ip and port from listen parameter.

If I specify port 5432 in the connect_address parameter, then confd updates the configuration of the haproxy, and then forward the clients to PostgreSQL (without pgbouncer).
App--5000-->haproxy(+confd)--5432-->PostgreSQL(master). :(
Ok...
To implement my connection scheme, I need to disable the confd service, and manage of the haproxy configuration manually?

[CyberDem0n]

There are two options, and which one to go is depends on what you want to achieve.

1. It seems that now you are running pgbouncer in front of each postgres node, but you can run it in front of haproxy.

2. another option would be to update confd template. {{base (replace (index (split $data.conn_url "/") 2) "@" "/" -1)}} this construction extracts 'host:port' from conn_url, which is stored in the /service/cluster-name/members/member-name. You have to change it in such way, that it will extract only host. Port in your case will be always 6543

[vitabaks]

You have to change it in such way, that it will extract only host

I'm sorry, but how to correctly change the template?

[vitabaks]

{{range gets "/members/*"}} server {{base .Key}} {{$data := json .Value}}{{base (replace (index (split (index (split $data.conn_url ":") 1) "/") 2) "@" "/" -1)}}:6432 maxconn 400 check port {{index (split (index (split $data.api_url "/") 2) ":") 1}}

[jberkus]

So the connect_address is really there for Patroni's own internal use; if you're proxying application connections, it's not for those.

Is this on Kubernetes, or not?

[vitabaks]

@jberkus
No, it's not kubernetes.
I wanted to place a pgbouncer in front of the postgreql node.