Re: pgpool II, streaming replication and HA

I don't know anything about repmgr. I neglect the questions to repmgr.

> I'd like to setup HA PostgreSQL streaming replication cluster with two
> nodes. The main issue which I'd like to protect from is HW node
> failure or reboot. I supposed to use repmgr to control PostgreSQL
> cluster and two pgpool instances as load balancers/DB proxies.
>
> The issue which I still don't understand how to handle is temporary
> master failure. Let's say, we have host A (master) and host B
> (slave). Now A reboots. Pgpool (or repmgr, doesn't matter) promotes B
> to master. Now A comes online (and still considers itself master). How
> can Pgpool determine that A host is "bad one" and stop routing queries
> to A?

There's a file called "pool_status" which records the previous status
of the DB servers. So the file records that "A is down, B is up". In
your case, when A reboots, the old primary server on A may start as a
primary, but from the file pgpool knows that the PostgreSQL on A was
down and never automatically regards it online. So any query sent to
pgpool will be routed to the new primary running on B, never routed to
A.

To make the old primary on A usable, you need to shutdown the
PostgreSQL (if it's running) and turn it to a new standby connecting
to the current primary on B. You can do it by either "online recovery"
feature of pgpool or do it manually using pg_basebackup etc. In the
latter case, you need to "attach" 'the new standby to make it online
by using pcp_attach_node.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp