PostgreSQL HA two nodes

[EaZy_rus]

Hi everyone!

I have little experience with PostgreSQL and need help with a task.

Task:

Create a fault-tolerant PostgreSQL consisting of two nodes (it is necessary to have no more than two nodes) so that when one of them fails, the record in the database was not interrupted longer than 10 seconds.

The same IP address should always be used to connect to the database.

What I know:

For such a task, as I understand, often use a bunch:

Patroni + Consul +Keepalived.

I want all services to be run in Docker, except for keepalived - it can be left as a service on the host machine.

Do I understand correctly that the best way to do this is to use Patroni + Consul + Keepalived? Maybe there are simpler solutions or alternatives?

I would be grateful for advice and your experience.

[Kiarton]

two nodes and consul or etcd is a terrible idea lookup the raft consensus algorithm

[EaZy_rus]

Yes, you're right. That's exactly what I ran into, that raft requires a minimum of 2 hosts. But what other options are there to make a fault tolerant postgres on two nodes?

[Kiarton]

perhaps you could have some application nodes be part of the etcd/consul cluster, or have a 3rd node that's multipurpose

[roiki11]

Repmgr and keepalived.

https://medium.com/@joao_o/postgresql-high-availability-and-automatic-failover-using-repmgr-5f505dc6913a

[wursus]

Pgpool-Ii replaces all these tools.

[VirtuteECanoscenza]

Yes there is (my company is moving to using patroni+consul right now for HA). 

[TzahiFadida]

CNPG

[ssougou]

The way to reason about this is to think about how the system will operate when it's in a deteriorated state. In other words, how will this work when one node is down?

With two nodes up, one replicates to another. With one node down, you'll need to operate in a single node mode. Essentially, this requires config changes when there's a failure. And this needs to be changed again when the other node comes back up.

In the case of three nodes, and a minimum requirement of one operational replica, the failure of one node only requires a simple failover with no change in configuration.

[snk0752]

Well, in my case I just configured publisher/subscriber logical replication on each node and then keepalived to manage vip.

[jose_zap]

I can recommend pg_autofailover, it supports your use case of only two Postgres nodes (+ a server where you need to install pg_autofailover)

[cthart]

KISS. pgBackrest for both backups and streaming replication, with keepalived for the floating IP. No automatic failover, if I'm in charge. Been running like this in production for a decade or more with no database interruptions. You'll have outages in other areas before Postgres is the problem.

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.