Questions regarding pgBouncer

[v3nturetheworld]

Which example are you working with?

I'm not working with an example but I'm trying to setup a basic, single replica cluster with pgBouncer for connection pooling to avoid database disconnects on fail overs. The cluster was created with the following command:
pgo create cluster -n crunchy-test hacluster --pod-anti-affinity="preferred" --replica-count=1 --pgbouncer

What is the current behavior?
All of the services and pods associated with the cluster correctly run, however the pgBouncer doesn't seem to connect to either the primary or secondary database.

What is the expected behavior?
Unless there is additional required configuration that I am missing (I'm new to pgBouncer and working with PostgreSQL), I believe I should be able to use psql to connect through the pgBouncer Service to the databases such as userdb using psql -U pgbouncer -p 5432 -h hacluster-pgbouncer userdb

Other information (e.g. detailed explanation, related issues, etc)
From within the pgBouncer pod I am able to use psql to connect directly to the hacluster service using: psql -U postgres -p 5432 -h hacluster userdb. I am also able to connect to the 'virtual database' pgBouncer has. I was testing a few queries to see if I was missing something configuration wise, but if I should try something else here please let me know:

Trying to test creating a connection to the userdb within the pgbouncer container resulted in this:

Next, I tested from the hacluster-replica container:

I may be missing something obvious here, or maybe its my lack of understanding on how pgBouncer works, but from the documentation and all that I can find online, adding the --pgbouncer flag seems like it should automatically do all the configuration needed. For an application I should be able to point it to the pgBouncer Service and I guess either provide the pgbouncer credentials or by using pgo create user use those credentials for the application. One additional change I made was adding the replica configuration option to pgbouncer.ini with: {{.PG_REPLICA_SERVICE_NAME}} = host={{.PG_REPLICA_SERVICE_NAME}} port={{.PG_PORT}} auth_user={{.PG_USERNAME}} dbname={{.PG_DATABASE}}

Additionally, I noticed in the Environment variables for pgBouncer deployment that PG_PRIMARY_SERVICE_NAME and PG_REPLICA_SERVICE_NAME were both empty. Should these fields be populated?

Please tell us about your environment:

• Operating System:
  Docker images are CentOS7, cluster OS is RHEL7
• Where is this running ( Local , Cloud Provider): Local 3 node OpenShift3.11 Cluster.
• Storage being used (NFS, Hostpath, Gluster, etc): NFS
• Container Image Tag: centos7-4.2.2 and centos7-12.2-4.2.2
• PostgreSQL Version: 12.2
• Platform (Docker, Kubernetes, OpenShift): OpenShift
• Platform Version: 3.11

If possible please run the following on the kubernetes or OpenShift (oc) commands and provide the result:
kubectl describe yourPodName
kubectl describe pvc
kubectl get nodes
kubectl log yourPodName

[jkatz]

The short answer is that in the upcoming release the pgBouncer integration received significant improvements, including some of the foundational ones found in c31fae3

In 4.2, For connecting to "any" database within your PostgreSQL cluster, you want to use the * setting, as such:

https://github.com/CrunchyData/postgres-operator/blob/master/conf/postgres-operator/pgbouncer.ini#L1-L2

You can edit this file in the pgo-config ConfigMap and then redeploy your pgBouncer Deployment.

If you installed with the Ansible installer, it appears the two pgbouncer.ini files have deviated. I am going to bring them into alignment. Turns out I was looking across branches.

[v3nturetheworld]

@jkatz Thanks for getting back to me. I look forward to the 4.3 release, it looks like it will have a lot of great new features and improvements, I particularly look forward to the installation container.

I made the change to config map you suggested but I think I may still be missing something. If I understand the pgBouncer correctly, an application would use the pgbouncer username and password, along with the pgbouncer service hostname to connect to the databases in the cluster, correct? Is any other additional configuration required, or should the --pgbouncer flag be enough in pgo create cluster command?

So I'm trying to connect to the userdb database in the master database via the pgbouncer (using psql in pgbouncer pod), and I get the following results:

Do I have to expose ports anywhere using oc commands or edit any additional files?

As a side note, I believe this may be a bug but I discovered if you delete the pgbouncer in a cluster then create a new pgbouncer, the new pgbouncer never gets deployed.

Update:
So I was able to get it to connect, the issue was that the pgbouncer user was never added to the databases somehow. I manually added the pgbouncer user with same autogenerated password in the pgbouncer secret. There is also an issue with using the pgo create command, it times out and never successfully creates a new user. I can view users but not create new PostgreSQL users (passwords are randomly generated so ill just include raw pictures):

Logs from Operator

I believe that the pgbouncer user should automatically created and added to users of the PostgreSQL databases right? And I am unable to create any users using the pgo create user command. I can provide any additional logs if needed.

[jkatz]

I appreciate the very detailed feedback and logs, this certainly helps a lot when trying to troubleshoot.

That being said, I will give you an answer that may not be totally satisfying, but will make your pgBouncer experience much more enjoyable. Wait for 4.3 😄 A lot of what you mentioned above was fixed in this upcoming release as the pgBouncer support was completely re-engineered.

To try to address a few of the above, regarding:

* = host={{.PG_PRIMARY_SERVICE_NAME}} port={{.PG_PORT}} auth_user=pgbouncer

This basically says "connect to any database via the primary service over the supplied port using the pgbouncer user to deliver the authentication credentials for my other users" (bear with me on this one).

What happens is that there is a special SECURITY DEFINER function that queries the pg_catalog.pg_authid table to grab the hashed credentials for users that can log into the PostgreSQL database. This allows both pgBouncer to verify the person who is logging in can actually log in.

In 4.2, I would not be surprised if the pgBouncer user was not added to the database -- this is all fixed in 4.3, as I personally spent well over a week tightening up our pgBouncer integration.

That all said, you should be able to create user with the pgo create user command in general. They may not be immediately available for logging in via pgBouncer (or you may need to bounce the Pod, no pun intended). I also spent some time on pgo create user in 4.3 as well which coincides with the pgBouncer improvements too.

Anyway,in a few weeks, this will make much more sense 😄

[v3nturetheworld]

Awesome, thanks again for the reply. I figured something like that was happening with the user accounts in pgBouncer and the databases after reading a bit more on what pgBouncer does and how it works. I think for now I'll just go without the bouncer and use the primary db service name and live with the 30-40s time for failover to occur until 4.3 gets released. I'll close this issue now and look forward to the release of 4.3... so far Crunchy has been pretty awesome though 👍.