Postgres-operator: Question: Failover mechanism

[koh-osug]

1. Is the replication to replicas executed synchronously? I.e. if the master fails is the data up to date in the replica?

2. In the case of a failed master is the replica ready at once?

3. Is there no connection loss from the perspective of the client? Does the client must support some fail over mechanisms? E.g. retry if connection failed to support no service interruption?

4. In case of a db version upgrade, does this work with zero downtime and no connection loss? Are you using logical replication starting with PQ 10?

5. The comparison mentioned on https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-3/ mentions a downtime when the master is down? Can this be minimized or even reduced to zero?

6. Also the comparison mentions that if the patroni service is stopped it is not automatically restarted. Is this monitored or solved?

[CyberDem0n]

Is the replication to replicas executed synchronously? I.e. if the master fails is the data up to date in the replica?

It is up to you how to configure it. Patroni supports synchronous replication and there will be no data loss after failover, but one should keep in mind about performance penalty from sync replication.

In the case of a failed master is the replica ready at once?

It could take up to 30-40 seconds by default to notice master unavailability.

Is there no connection loss from the perspective of the client?

In case of failover clients must reconnect.

Does the client must support some fail over mechanisms?

There is a single endpoint (service) in K8s which always connects to the current master.

E.g. retry if connection failed to support no service interruption?

Yes, the client MUST implement some sane retry algorithm, preferable with exponential back-off.

In case of a db version upgrade, does this work with zero downtime and no connection loss?

The minor upgrade requires a restart or switchover. In any case existing connections will be terminated, and the client must retry.

Are you using logical replication starting with PQ 10?

No, logical replication is not suitable for building HA systems. It replicates only data (tuples), this is not enough.

The comparison mentioned on https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-3/ mentions a downtime when the master is down? Can this be minimized or even reduced to zero?

Zere-downtime? There is a CAP theorem. We want to be Consistent and Partition tolerant (nobody wants a split-brain). Therefore zero downtime is not possible. One could get very close to zero, but not with Patroni and will cost a lot.

Also the comparison mentions that if the patroni service is stopped it is not automatically restarted. Is this monitored or solved?

Neither service can monitor and restart itself. This is not a Patroni problem. In case of normal shutdown Patroni also stops postgres. In case of a crash, the init system should be able to take care of the restart. Spilo implements runit for that (it used to be supervisord before).
Another option is using a software linux watchdog, it will restart the whole node is Patroni stopped pinging the watchdog. The third option - run Patroni in docker with PID=1. In case of the Patroni crash the whole container goes down (including postgres).

[koh-osug]

Thanks for the quick response. Maybe such information can be included in a FAQ.

[koh-osug]

Still some questions left:

It is up to you how to configure it. Patroni supports synchronous replication and there will be no data loss after failover, but one should keep in mind about performance penalty from sync replication.

I browsed the configuration parameters, but could not find a setting allowing to specify sync or async behavior. How can this be configured? In which situation is a non sync approach tolerable? I'm just thinking about the classical bank account example and I'm wondering where lost updates can be acceptable as default setting.

It could take up to 30-40 seconds by default to notice master unavailability.

How can this influenced? Is this a health or readiness check of the kubelet against the container? Which parameter is responsible for this? What are the negative effects about lowering the check intervals?

[CyberDem0n]

Automatic failover and sync replication is handled by Patroni, you can find all information in the Patroni documentation.

The synchronous_mode parameter is enabled per postgres cluster in the patroni section.

[koh-osug]

The minor upgrade requires a restart or switchover. In any case existing connections will be terminated, and the client must retry.

Is this handled by postgres operator? AFAIK major version upgrade can use a different data storage format. Is here an update supported where this update is transparent to the client with no interruption?

[Jan-M]

As Alexander already states, the Postgres operator does support minor version update with minimal client impact, basically part of the rolling Docker image update there will be exactly one switch from the master with old minor version to pod with new minor version. Forcing clients to reconnect.

For major version upgrades, Postgres operator and Spilo support this using clone. This is a new cluster, that runs the new major version. Clients can then start to use the new cluster. This is not transparent and not in place and requires planning and downtime (depending on how long the clone/restore takes).

[koh-osug]

Does this mean, that if I'm searching for a solution that gives availability during major updates, Postgresql is in general the wrong solution? I was hoping there is a sync mechanism which also supports two different major versions at least allowing only a small service downtime comparable to the fail-over situation.

[Jan-M]

We need to be careful here not to mix things. These is fail overs and fail overs, planned fail overs (we call them switch overs) e.g. via patroni and done for the minor version or docker image update are quick. (<=15 sec normally).

The upgrade process is more tricky, stop systems, run pg upgrade, somehow sync replicas (rsync, reinit). The pg_upgrade is also a very quick process, but we do not have it in the operator and spilo right now.

You can also try to use logical replication, intended for this purpose. It allows to replicate across major versions with some limitations. We have also used this with success.

[koh-osug]

Regarding the logical replication: I have read about this, but it was mentioned that large objects and sequences are not handled by this. I'm using both, so I thought this is not yet a workable solution for major updates.

[Jan-M]

Start with 12 now, then you have 2-3 years without upgrades ;) and then you can do the local upgrade and rsync the other pods when you understand it. This is usally a process that takes 1-10min only. Something we did prefer at night over spending hours on other options.

[koh-osug]

OK, thanks. This approach seems to be acceptable.

[siku4]

The synchronous_mode parameter is enabled per postgres cluster in the patroni section.

@CyberDem0n are you really sure? See here https://github.com/zalando/postgres-operator/issues/505 and here: https://github.com/zalando/postgres-operator/blob/535517cd1b0cef2c307eb1592fc8e17fad918f34/pkg/apis/acid.zalan.do/v1/postgresql_type.go#L112-L121

[CyberDem0n]

Patroni supports it for sure, and you can enable with the help of patronictl by execing into the pod.

[siku4]

Patroni supports it for sure, and you can enable with the help of patronictl by execing into the pod.

@CyberDem0n thanks for your reply! But consequently it is not sufficient to just set the synchronous_mode parameter within the cluster resource, isn't it?

[CyberDem0n]

Probably, and it should be fixed