Thread: pgpool II, streaming replication and HA

Hi.

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?
--
Best regards,
Alexander Pyhalov,
system administrator of Southern Federal University IT department

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

On 01/27/2016 11:53, Tatsuo Ishii wrote:

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

Thank you for clarification.


--
Best regards,
Alexander Pyhalov,
system administrator of Southern Federal University IT department