Read-only endpoints problem

[Zvirovyi]

Hi! I've discovered some weird behavior with read-only-endpoints.

Steps to reproduce

Model:

1. Pgbouncer always includes read-only channel in its config upon different database name (main database name + _readonly suffix) if there are postgresql replicas. (example below is on data-integrator/1)

   [databases]
   
   asd = host=10.88.216.205 dbname=asd port=5432 auth_user=pgbouncer_auth_relation_7
   
   asd_readonly = host=10.88.216.56,10.88.216.129 dbname=asd port=5432 auth_user=pgbouncer_auth_relation_7
   

2. But pgbouncer does not notify client application about read-only-endpoints if it's not exposed (external-node-connectivity).

   

3. And even when it exposed, it notifies client application with read-only-endpoints pointing to each non-leader pgbouncer unit IP.

   

Expected behavior

Pgbouncer always must provide application with both RW and read-only channels if there are postgresql replicas active. And, i assume, there is need to move read-only channel to different endpoint, because postgresql_client/v0 assumes that database name will be the same and only endpoint is different.

Actual behavior

When not exposed, pgbouncer will ignore read-only channel, so that application just can't use postgresql replicas.
And when exposed, app will put all the non-leader pgbouncer unit IPs into read-only-endpoints (but it has nothing to do with read-only channels to postgresql replicas, it just another pgbouncer units pointing to the same primary).

Versions

Operating system: Ubuntu 23.10

Juju CLI: 3.4.2-genericlinux-amd64

Juju agent: 3.4.0

postgresql charm revision: edge (rev. 393)
pgbouncer charm revision: 1/edge (rev. 142)

LXD: 5.21.1 LTS

[github-actions]

https://warthogs.atlassian.net/browse/DPE-4071

[taurus-forever]

@dragomirp can you please comment it here. IMHO, we should notify client on the endpoints change... always. Is the UNIX SOCKET pgb concept affects us here?

[taurus-forever]

BTW, on the reported image the pgboucer/1 exposed port, but pgbouncer/2 didn't. why?

[dragomirp]

@dragomirp can you please comment it here. IMHO, we should notify client on the endpoints change... always. Is the UNIX SOCKET pgb concept affects us here?

When there's no external node connectivity, client units are only supposed to use the local subordinated pgbouncer. There's no place to set a read only database name in the interface and, as is, accessing those requires the client charm to be aware of the _readonly databases. To be able to have meaningful read only endpoints, we'll need to add another PGB read only instance serving on a different port, since that can be different in the interface.

With data integrator we'd have the inverse problem, if we provide real readonly endpoint (on a different port), there would be no way to get all available RW endpoints without that the client has to switch ports.

This is preexisting behaviour, since before the charm was made subordinate.