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

[Tatsuo Ishii]

Hi,

> Hi Tatsuo
> 
> I was sure that there is a respective configuration option that makes
> "autoattaching" for standby nodes, if replication between nodes works well.

That's "auto_failback" configuration parameter.

> What's a reason to keep a well functioning node in a detached state?

When follow primary script gets executed, the streaming replication
primary has been changed. This means the standby nodes cannot connect
to the new primary without appropriate care. The follow primary script
is responsible for this. If the "care" is just changing
primary_conninfo, it's not necessary to make the standby in a detached
state. Just reloading postgresql.conf is enough. However, there's no
guarantee that it always succeed. For example, due to missing WAL
records. So pgpool allows the follow primary script to run pg_rewind
or pg_basebackup in this case. If the script needs to execute those
commands, it needs to stop the standby node beforehand. If we do not
set the detached state, stopping the standby node will trigger another
failover, which is confusing for the follow primary script and users.
By setting the standby to detached state should prevent the confusion
because stopping detached state standby node does not trigger failover
event.

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

> On Sun, 20 Aug 2023, 6:52 Tatsuo Ishii, <ishii at sraoss.co.jp> wrote:
> 
>> > 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
>>
>> One thing I noticed is, after reloading PostgreSQL, the script does
>> not execute pcp_attach_node. You need to execute the command because
>> without the command, the standby status keeps on "down" from pgpool's
>> point of view.
>>
>> Best reagards,
>> --
>> Tatsuo Ishii
>> SRA OSS LLC
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>> _______________________________________________
>> pgpool-hackers mailing list
>> pgpool-hackers at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-hackers
>>