search

CrunchyData / postgres-operator

Production PostgreSQL for Kubernetes, from high availability Postgres clusters to full-scale database-as-a-service.
https://access.crunchydata.com/documentation/postgres-operator/v5/
Apache License 2.0
3.93k stars 591 forks source link

Deploying replication slots #3900

Open Pluggi opened 6 months ago

Pluggi commented 6 months ago

Hello,

I am looking to replicate a table from one PGO cluster to another. For that, I wanted to use Postgres replication slots and Patroni's dynamic configuration use_slots to ensure it is created on the leader and its replicas and continue working in case of a failover.

However I found this piece of code which forces it to false : https://github.com/CrunchyData/postgres-operator/blob/master/internal/patroni/config.go#L207

What was the rationale behind this setting? What could go wrong if I set it to true ? Can it change the behavior of the replication inside a PGO cluster?

andrewlecuyer commented 5 months ago

Hi @Pluggi! Thanks for reaching out.

By default, PGO leverages pgBackRest for the management of WAL archives (meaning pgBackRest ensures WAL is properly archived and available to the various replicas within the PostgresCluster - while also providing a ton of great features to ensure WAL is effectively and efficiently archived & available). So because of this, PGO simply sets use_slots to false by default.

That being said, PGO does allow this setting to be turned on. In fact, certain functionality provided by the HA system (Patroni) requires use_slots to be enabled:

https://patroni.readthedocs.io/en/latest/dynamic_configuration.html#dynamic-configuration-settings

slots: define permanent replication slots. ... Enabling permanent replication slots requires postgresql.use_slots to be set to true.

So use of the setting really depends on your use. And I'll note that if you are seeing any issues with WAL that has you reaching this setting, I would definitely be curious to hear any further details/specifics.

Hope this helps!

Pluggi commented 5 months ago

Hello !

It's not clear to me what pgbackrest has to do with patroni using replication slots? I thought the writing of WAL files to disk was enabled using archive_mode.

My use case is, we have two PGO clusters A and B. I want to replicate data from table T in cluster A to cluster B. The most simple way to do that seemed to be to just use replication slots. However, I think if I just deploy replication slots using pg_create_physical_replication_slot() on the leader of cluster A and then a failover happens on cluster A, the replication slot will not be automatically recreated on the new leader? So I wanted to use Patroni for that.

tjmoore4 commented 2 months ago

Hi @Pluggi. To follow up on this issue, based on your description above, have you considered using one of PGO's built in standby cluster options? There are examples in the documentation that should be useful if you decide to try this out. Hope this helps!