Postgresql connections

[marosg42]

When I run a lot of MAAS commands I eventually get FATAL: remaining connection slots are reserved for non-replication superuser connections
This workaround worked for me, of course I don't know the magic number and parameter is currently in beta only

juju refresh postgresql --channel 14/beta
juju config postgresql experimental_max_connections=500

[taurus-forever]

Hi @marosg42 , it is a duplicate of MM reply.

The option experimental_max_connections is really an experimental option and not recommended for the production usage. Data Team recommends the pgbouncer charm in front of PostgreSQL (as a connection pooled). We are happy to assist you with your testing!

[skatsaounis]

Hi @marosg42, I just added a draft PR that is including pgbouncer in PostgreSQL installation with maas-anvil. As I mentioned inside, for the moment it is blocked by this issue: canonical/pgbouncer-operator#245

[taurus-forever]

@skatsaounis please share the test results for #16
Are we moving the right direction?

Thank you!

[skatsaounis]

Hi @taurus-forever . As you can observe in the linked PR's checks: https://github.com/canonical/maas-anvil/actions/runs/9480678236/job/26510157069?pr=16, we have a successful run.

When I was informed that a new revision is released in pgb 1/edge, I re-triggered the failed job and it succeeded. This job is producing a complete single node anvil deployment. If it ends with all charmed apps in active status that means maas-agent confirmed that maas-region has finished the maas init. That was the place we were failing before the pgb fix.

[skatsaounis]

Hi @taurus-forever, @dragomirp, @delgod

After the merge of the initial PR #16 which introduced pgbouncer and some test runs with the change included in the anvil snap, we came to the conclusion that inevitably we have to choose pool_mode session for pgbouncer and 50 max_connections per pgbouncer unit, which are the maximum required per MAAS region (40) plus a buffer of 10. In addition, we discussed with @dragomirp that we should set the postgres experimental_max_connections as a number that is the max(100, 10 + 50 * n_regions).

Since this change leads to database restarts, we also considered that if the maas-anvil practitioner knows beforehand the total number of regions, then they can set the corresponding experimental_max_connections from the beginning, to avoid restarts.

The linked #32 is trying to handle all the above configuration choices. Hopefully, we will be able to test it and confirm whether is the appropriate solution for charmed MAAS use case. I will keep you updated.

[skatsaounis]

In addition, this is a diagram shared by @dragomirp that includes pgbouncer, postgres cluster and 3 MAAS regions.

This is the outcome of setting:

pgbouncer pool_mode                    session
pgbouncer max_connections              50
postgres  experimental_max_connections 160