[pgpool-general: 7480] Re: Promote specific backend to primary in streaming replication

[Tatsuo Ishii]

Hi Nathan,

>> OK - this sounds great. I can have a stab at getting something like that
>> written.
>> 
>> So, in terms of implementation this sounds like:
>> 1) New pcp command to set a next primary backend ID. I'm not sure how this
>> would be stored, perhaps update the parsed configuration state (is this
>> pool_config), so it could also be in the configuration file? Or, it could
>> update a new flag on bkinfo?
>> 2) Modify get_next_main_node to include a check for the above, and check
>> that it is a valid backend and not the current (failed/detached) primary
>> backend.
>> 
>> Is modifying get_next_main_node() appropriate here? My thinking is that
>> doing this means we can continue to use the existing scripts, which use %m
>> and %H and so on.
>> Perhaps this is best implemented as a backend "primary-ship" priority -
>> i.e. set a priority for each backend (via config and pcp) to become the
>> primary, as a uint8 on bkinfo, then in get_next_main_node sort first by
>> priority then by node id.
> 
> Thank you for the great ideas. I need to think about these. Will be
> back tomorrow.

I think we don't need create new pcp command. Instead, we can have new
parameter something like:

backend_promote_priority0 = 0
backend_promote_priority1 = 1
backend_promote_priority2 = 2

The higher number, the higher priority. For example suppose node 0 is
current primary. If node 0 goes down, node 2 will be chosen because it
has the highest priority 2 (if node 2 is already down, node 1 will be
chosen). The selected node info will be set to %m, %H, %r of failover
script as you said.

If these parameters are not specified, we will chose the youngest live
node, which is currently Pgpool is doing.

If we want to change the priority, pgpool reload should work.

As you already pointed out, the advantage of this way is, existing
failover script can be used to without any change.

As of implementation details, I think we need to have new member in
POOL_REQUEST_INFO structure.  Currently we have main_node_id and
primary_node_id on it. I think we should leave them as they are and
have new member something like
"new_primary_candidate". get_next_main_node() will calculate the value
for new_primary_candidate depending on pgpool.conf. Finally we pass
the new_primary_candidate info to trigger_failover_command's
new_main_node parameter to handle failover.

Also those priority info should be added to BackendInfo structure.

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