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