0

On a high level, consider two PostgreSQL instances on two separate nodes: the leader and its hot standby. Replication from leader to follower is configured.

Consider that the leader node crashes. Two questions:

  1. Who is responsible for promoting the follower to be the new leader? Do you have to setup some kind of heartbeat system, or is this handled automatically by PostgreSQL?

  2. The clients relying on PostgreSQL now have to be aware that the leader was changed. What is the best way to make this seamless for the client? It there an automatic procedure that PostgreSQL offers?

For question 2. some ideas come to my mind, in which an external server or the hot standby follower would heartbeat the leader. If the leader is considered dead, do one of the following:

  • The client connects to the PostgreSQL instance via hostname. On failover, the DNS entry would be changed to point to the new leader node by the heartbeat server.
  • The client connects to the PostgreSQL instance via IP. Something along the veins of a floating IP, in which the heartbeat server broadcasts an ARP request to update the MAC tables of the switches, in order to point the IP to the new leader.
  • The client connects to the PostgreSQL instance via an IP that is provided by ZooKeeper. The heartbeat server would update the entry on ZooKeeper to the new leader.

Could you help me answer the first two questions and assert the validity of the proposals for question 2.?

Improve this question

2 Answers 2

Reset to default
1
  1. https://www.postgresql.org/docs/12/warm-standby-failover.html says:

    PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby database server. Many such tools exist and are well integrated with the operating system facilities required for successful failover, such as IP address migration.

Yes you have to setup something.

  1. PostgreSQL does not provide automatic procedure for clients.
Improve this answer
0

Ad 1: You are responsible. You will have to use a third-party software like Patroni. Beware of trying to write a clusterware yourself with a couple of shell scripts.

Ad 2: The PostgreSQL client library (and the JDBC driver) can do that automatically if you use a connection string like this:

host=server1,server2 port=5432,5432 dbname=mydb user=myuser target_session_attrs=read-write
Improve this answer
2
  • Thanks for the answer. Regarding 2., imagine that the client cannot reach the leader (server1), thus timing out. Then the client tries server2, but this server 2 is still set as follower because the problem was the network between client-server1. Couldn't this strategy present a problem?
    – Pedro Lopes
    Commented Mar 4, 2020 at 0:02
  • No. Until failover is completed, you won't get a connection. That's as expected.
    – Laurenz Albe
    Commented Mar 4, 2020 at 6:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.