[pgpool-hackers: 4381] Re: Load balancing after failover not working in 3-node HA PostgreSQL cluster

[Tatsuo Ishii]

> Sorry i don't think i understand that could you please be more specific
> about this " solution

Maybe I was not clear about degeneration of standby nodes before
failover command runs.  Pgpool-II degenerate standby nodes before
starting failover command, which means Pgpool-II just changes the
status of the standbys from "up" to "down". But, Pgpool-II does not
stop/shutdown the standbys. So you don't need to worry that the
standby server actually being stopped if it's your concern.

> just for a reference here are our
> follow_primary_command:
> https://github.com/stormatics/pg_cirrus/blob/589cc51a12339a13b2a64dc25d7d7e0952e3a6a2/3-node-cluster/ansible/playbooks/setup-pgpool.yml#L45
> follow_primary_script file:
> https://github.com/stormatics/pg_cirrus/blob/main/3-node-cluster/pgpool-internals/follow_primary.sh
> 
>>
>> > NOTE, I attempted to resolve the situation by restarting pgpool2 using
>> the
>> > -d switch. After the restart, everything seemed to work fine, and
>> standby2
>> > node was correctly marked as standby again. Is restart of pgpool really
>> > necessary?
>>
>> No. You just should have waited a little bit longer so that the follow
>> primary command completed the job: recovering node 2.
> 
> I waited almost an hour after the failover but still getting above log that
> there is no standby node

Then there's something wrong in your follow primary script. To debug
it, I suggest to look into pgpool log and the standby's PostgreSQL
log. I suspect the standby server failed to follow the new standby for
some reason (maybe some WAL files are missing?). In general you should
fall back to use pg_rewind/pg_base_backup to sync with the new primary
in this case.

See the official sample script for more details.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=blob_plain;f=src/sample/scripts/follow_primary.sh.sample;hb=refs/heads/V4_4_STABLE

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp