[pgpool-general: 2908] Re: Automatically attaching a degenerate node on failback

[Alexandru Cardaniuc]

Anupama Ramaswamy <anumr_0123 at yahoo.com> writes:

> Hi,

> I have a pgpool setup with 2 DB nodes with Postgres streaming replication. So my config is
> load_balance_mode = on
> master_slave_mode = on
> master_slave_sub_mode ='stream'

> When the standby disconnects (postgres is stopped / n/w is down on the standby or standby is sthutdown), pgpool correctly degenerates the standby node. pgpool directs all queries (updates and select) to the primary. 

> Lets say the standby node comes backup after say an hour later. pgpool detects that the node / service is up, but keeps the node detached from the pool. Is there a way to attach the node automatically to the pool when the node comes
> backup. A script which can call pcp_attach_node should suffice for my purpose. Since I am using streaming replication, that will take care of syncing the data to the standby, anyway.

> I thought failback_command will be of use for this, but just learnt that failback_command will be called only after the node is attached.
> I don't want to use the online recovery option, because I want master failback to be a manual step.

> How should I do an automatic re-attach of the secondary when it comes back up ?


I was thinking about a similar thing recently. Because after you bring
the node back up in my case if the number of transactions it's behind is
not too large streaming replication will bring the node to the current
state and it will be part of the database cluster again, but the pgpool
won't attach it back to the node. I think the main idea here is that
through the postgres can bring it back into streaming replication
cluster, it may still take time for it to catch up with the primary node
and until that happens you don't want the read transactions to be sent
to that database. Note that 'load_balance_mode = on' in this case. So, I
guess the idea would be to probe and see if the slave node caught up
with the master, and at that point run pcp command to attach the node.

May be pgpool devs can confirm if that's the approach that we are
supposed to be taking in cases like this.

-- 
"One machine can do the work of fifty ordinary men. No machine can do
the work of one extraordinary man."  
- Elbert Hubbard