Thread: Configuring more than one hot standby server

At Tue, 19 May 2020 22:39:20 +0530, Santhosh Kumar <krssanthosh@gmail.com> wrote in 
> Hi Community,
>  I read about "hot stand by" server and as far as the document goes, it
> does not explicitly explain about, whether is it possible to configure more
> than on database as a Hot stand by server. If we can configure more than
> one hot stand by server, which database will take the role of master, in
> case the original master instance crashes? Does a leader election happen?

You can configure two or more standbys for one primary and cofigure
some of or oall of them as synchronous standbys.  Howerver, PostgreSQL
doesn't choose the next-primary automatically.

A replication cluster involving three or more servers can be
configured in a couple of ways.  You may configure them in a cascade
replication chain, or in fanned-out configuration, or mixture of the
two.

If you made a cascaded replication set, you don't have a choice other
than the first standby in the chain.

   before: [primary] - [standby 1] - [standby 2] ...
   after : [old standby 1] - [standby 2] ...

If you made a fanned-out replication set. You can configure some of or
all of them to be synchronous standbys, which is guaranteed to have
the same data for all-commited transactions on the primary.  If you
configured priority-mode synchronous standbys, you can choose the next
primary among the first n active standbys.  If you didn't configured
synchronous standbys, you would need to check up the all standbys and
choose the most-"advanced" one. That is, the next primary is the
standby having the largest receive-LSN.  You need to reconstruct the
replication set anyways.

   before: [primary] -+- [standby 1] LSN = x - 8
                      +- [standby 2] LSN = x - 2  <= the next primary
                      +- [standby 3] LSN = x - 6

   after : [old standby 2] -+- [standby 1]
                            +- [standby 3]

That is the same for the case of quorum-mode synchronous standbys
setting.


regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center