PostgreSQL failover: Who is responsible for handling it? How do clients connect to the new leader

dnsfailoverNetworkpostgresqlreplication

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.?

Best Answer

  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.
Related Question