High Available PostgreSQL with Patroni and Replication

Question

I want to build a highly available PostgreSQL cluster using patroni with etcd. I want to have 3 server (1 master and 2 slaves) in my cluster, so if something goes wrong one of the slaves automatically takes over the master role. Only the master server should accept write operators, the slaves are only for safety and read operations.

On the other hand i also need approximately 10 servers with an up-to-date copy of the data on the cluster. These servers are only there to read data and none of them ever can be promoted to a master server. They shouldn't even be part of the high available cluster. They are just supposed to get the data using streaming replication etc.

Is such a setup even possible? Can i use replication to other servers when they are part of a patroni cluster?

Answer 1

Only the master server should accept write operators

This is guaranteed by patroni for almost all cases. Essentially, you need to be malicious to provoke a "split-brain" scenario where more than one database accepts writes in a patroni cluster. There are several layers of security against this scenario built into patroni.

On the other hand i also need approximately 10 servers with an up-to-date copy of the data on the cluster. These servers are only there to read data and none of them ever can be promoted to a master server.

This is simple and straigh-forward to do in patroni: In the configuration file for each of these nodes you can enable a special "tag", nofailover: true, so these database instances will never be considered during leader election, even thought they are in the same patroni cluster. The advantage of doing this is that patroni still takes care of all management, by creating replication slots, running pg_basebackup, keeping the configuration in sync etc.

https://patroni.readthedocs.io/en/latest/SETTINGS.html?highlight=nofailover#tags

In the end, if you prefer not having these replicas directly in your patroni cluster, you can always set up additional streaming replication independently, ontop of what patroni configures, though I think this only makes life more difficult...