[pgpool-general: 7056] Re: Rebooting the cluster?

[Tatsuo Ishii]

> Hi again,
> 
> Thank you for a quick answer.
> 
> Booting the services on one node is not a problem. But booting the
> whole cluster is. The booting sequence for all the nodes one at a time
> goes like this:
> 
> 1st node starts up
> ==================
> 
> PgPool:
> centos8-1v2-int 5432 9000 4 MASTER
> centos8-2v2-int 5432 9000 0 DEAD
> centos8-3v2-int 5432 9000 0 DEAD
> 
> Database:
>  node_id | hostname | port | status | role | replication_state
> ---------+-----------------+------+------------+---------+-------------------
>  0       | centos8-1v2-int | 5433 | up         | primary |
>  1       | centos8-2v2-int | 5433 | quarantine | standby |
>  2       | centos8-3v2-int | 5433 | quarantine | standby |
> 
> => Delegate IP is not up yet because there is no quorum. This is ok.
> 
> 2nd node starts up
> ==================
> 
> PgPool:
> centos8-1v2-int 5432 9000 4 MASTER
> centos8-2v2-int 5432 9000 7 STANDBY
> centos8-3v2-int 5432 9000 0 DEAD
> 
> At first we are waiting...
> 
>  node_id | hostname | port | status | role | replication_state
> ---------+-----------------+------+---------+---------+-------------------
>  0       | centos8-1v2-int | 5433 | up      | primary |
>  1       | centos8-2v2-int | 5433 | up      | standby |
>  2       | centos8-3v2-int | 5433 | waiting | standby |
> 
> And then PgPool detaches the last node:
> 
>  node_id | hostname | port | status | role | replication_state
> ---------+-----------------+------+--------+---------+-------------------
>  0       | centos8-1v2-int | 5433 | up     | primary |
>  1       | centos8-2v2-int | 5433 | up     | standby | streaming
>  2       | centos8-3v2-int | 5433 | down   | standby |
> 
> => The PgPool service is up and delegate IP is also up.
> 
> 3rd node starts up
> ==================
> 
> centos8-1v2-int 5432 9000 4 MASTER
> centos8-2v2-int 5432 9000 7 STANDBY
> centos8-3v2-int 5432 9000 7 STANDBY
> 
>  node_id | hostname | port | status | role | replication_state
> ---------+-----------------+------+--------+---------+-------------------
>  0       | centos8-1v2-int | 5433 | up     | primary |
>  1       | centos8-2v2-int | 5433 | up     | standby | streaming
>  2       | centos8-3v2-int | 5433 | down   | standby |
> 
> So the 3rd node stays down. I've configured failback.sh so that it
> recreates the replication slot on primary DB on failback event, so
> pcp_attach_node is enough to bring the last node up:
> 
>  node_id | hostname | port | status | role | replication_state
> ---------+-----------------+------+--------+---------+-------------------
>  0       | centos8-1v2-int | 5433 | up     | primary |
>  1       | centos8-2v2-int | 5433 | up     | standby | streaming
>  2       | centos8-3v2-int | 5433 | up     | standby | streaming
> 
> Otherwise my configuration is pretty much the same as the
> PgPool+Watchdog example in the PgPool web pages.
> 
> So, would it be possible to configure this so that just starting up
> all the nodes is enough to bring the whole cluster online? It may be
> so if I start them all at the same time but in production this is not
> always possible.

There's a parameter called "auto_failback" which automatically
attaches a PostgreSQL standby node if it's safe. auto_failback is
available in 4.1 or later.

https://www.pgpool.net/docs/latest/en/html/runtime-config-failover.html#GUC-AUTO-FAILBACK

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