[bitnami/postgresql-ha] watchdog capabilities

[DorBreger]

Name and Version

bitnami/postgesql-ha

What is the problem this feature will solve?

Currently the chart offers a Deployment that is able to create multiple Pgpool-II instances. Unfortunately the pgpools aren't clustered, despite Pgpool-II itself having that capability. This can cause multiple Pgpool-II instances not working together to promote different standby servers to primary in case of the primary node failing, or even a single Pgpool-II instance losing connection to the primary node due to in-cluster networking problems, causing a split-brains scenario.

What is the feature you are proposing to solve the problem?

To enable watchdog, the Pgpool-II clustering capability. the pgpool will have to switch from a Deployment to a Statefulset, To have deterministic DNS names, or allow the choice between Deployment and Statefulset to be configured in the values.yaml. I would be very glad to open a PR for the change, as I already have something cooking locally.

[github-actions]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[CeliaGMqrz]

Hi @DorBreger

Thanks for your feature request!

We are glad to hear that you are willing to open a PR to make this change. It sounds like a great improvement to enable watchdog and I appreciate your efforts to make this happen. The Bitnami team will be happy to review it and provide feedback. Here you can find the contributing guidelines.

[DorBreger]

I have experimented a bit and I was wondering if such a feature would be necessary? It seems the pgpools don't promote a new primary themselves, rather they only look for a new one in case of a failed primary. Watchdog would mean that only one pgpool is active at a time, meaning if that pod becomes inactive for any reason (even a node drain being ran) the database is inaccessible until failover happens at the pgpool/watchdog level. On the other hand, watchdog will help in some corner cases of network partition. Waiting to hear your feedback.

[github-actions]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[CeliaGMqrz]

Hi @DorBreger,

Sorry for the delay.

Thanks for your feedback and your initiative to contribute to this feature. To better understand the impact and necessity of this improvement, we need more information about the split-brains scenarios you mentioned, especially in the context of a single pg pool.

We acknowledge that this feature would be beneficial to improve high availability. However, if implemented, it should be made optional. Also, we think it's important to include a clear warning about the drawbacks you mentioned, specifically regarding the database becoming unreachable.

We would greatly appreciate it if you could provide us with more details on this matter.

[DorBreger]

Hi @CeliaGMqrz , thank you for responding.
In certain possible configurations, a pgpool initiates a failover when it finds the primary node backend unavailable. When using watchdog, the pgpools vote on whether the primary node is actually available.
However, it seems like in our configuration it is repmgr that initiates failovers and when a failover is detected the pgpool simply looks for a new primary. Therefore if we will be using Watchdog we would either need to have it initiate failovers instead of repmgr in order to benefit from it. Otherwise, the current possible configuration of multiple pgpools that aren't clustered works well enough, and it has the advantage that if a pod goes down for any reason, even a planned worker outage, the database stays available, unlike in a watchdog configuration in which the primary pgpool is the only one that can serve the application database services.

[DorBreger]

I think that the other issue I opened, in regards to a pgpool exporter is more beneficial to improving the availability of the services this helm chart provides. it is rather easy to implement and I'm willing to do so, I only need some guidance from youm

[CeliaGMqrz]

Hi @DorBreger

After considering the incompatibilities and discussing them with the team, we have decided to focus on the other open request. Thanks for your feedback.