[pgpool-general: 7472] Re: Recovery after a backend has failed
Emond Papegaaij
emond.papegaaij at gmail.com
Thu Apr 1 21:09:27 JST 2021
Hi,
I'm managed to get to logs of the 4 pgpool nodes. This is testing
environment for one of our customers and they did a test with a
network split. I do not know the exact steps the customer took that
resulted in this situation, but at 30-03-2021 10:00:00 3 of the 4
pgpool backends were reported as being down. 172.29.30.4 was the
primary database and 172.29.30.1 probably was the pgpool master at
that moment. A quorum existed between the pgpool watchdogs, but I do
not know if all 4 pgpool instances were up.
At 10:34:49 we restarted the database at .1. This database was running
but not replicating. After the restart, it still was not running as
standby. We therefore at about 10:41:24 we forced a recovery, which
involved the following steps:
- stop the database
- wipe the entire data directory
- pg_basebackup from 172.29.30.4, with --write-recovery-conf
- start the database
- instruct the local pgpool node to attach the recovered node
After that the database at 172.29.30.1 was up and streaming from .4.
We collected the following watchdog status:
docker exec tkh-pgpool pcp_watchdog_info -U pcp -h localhost -p 9898 -w -v
Watchdog Cluster Information
Total Nodes : 4
Remote Nodes : 3
Quorum state : QUORUM EXIST
Alive Remote Nodes : 3
VIP up on local node : NO
Master Node Name : 172.29.30.1:5432 Linux 6b513bbeced9
Master Host Name : 172.29.30.1
Watchdog Node Information
Node Name : 172.29.30.4:5432 Linux adacd5a47a97
Host Name : 172.29.30.4
Delegate IP : Not_Set
Pgpool port : 5432
Watchdog port : 9009
Node priority : 1
Status : 7
Status Name : STANDBY
Node Name : 172.29.30.1:5432 Linux 6b513bbeced9
Host Name : 172.29.30.1
Delegate IP : Not_Set
Pgpool port : 5432
Watchdog port : 9009
Node priority : 1
Status : 4
Status Name : MASTER
Node Name : 172.29.30.2:5432 Linux 1207501818e3
Host Name : 172.29.30.2
Delegate IP : Not_Set
Pgpool port : 5432
Watchdog port : 9009
Node priority : 1
Status : 7
Status Name : STANDBY
Node Name : 172.29.30.3:5432 Linux 082ff4a56d9a
Host Name : 172.29.30.3
Delegate IP : Not_Set
Pgpool port : 5432
Watchdog port : 9009
Node priority : 1
Status : 1
Status Name : LOADING
Thereafter, we collected backend status at node 1 and 4:
sudo docker exec tkh-pgpool pcp_node_info -U pcp -h localhost -p 9898 -w -v 0
[sudo] password for keyhub:
Hostname : 172.29.30.1
Port : 5432
Status : 3
Weight : 0.250000
Status Name : down
Role : standby
Replication Delay : 0
Replication State : streaming
Replication Sync State : async
Last Status Change : 2021-03-26 14:54:44
sudo docker exec tkh-pgpool pcp_node_info -U pcp -h localhost -p 9898 -w -v 0
Hostname : 172.29.30.1
Port : 5432
Status : 3
Weight : 0.250000
Status Name : down
Role : standby
Replication Delay : 0
Replication State : streaming
Replication Sync State : async
Last Status Change : 2021-03-29 16:02:09
As you can see, both pgpool nodes report the backend as being down but
also streaming/async.
At about 11:24 we started restring the pgpool nodes one by one, but
that didn't help. At 11:45 we stopped all pgpool nodes and restarted
them. After that, the database nodes were correctly reported as UP or
WAITING.
The full pgpool logs can be found here (7.5mb download):
https://files.topicus-keyhub.com/download/pgpool.zip
I hope this information helps in tracking down this issue.
Best regards,
Emond
On Wed, Mar 31, 2021 at 7:47 AM Bo Peng <pengbo at sraoss.co.jp> wrote:
>
> Hi,
>
>
>
> On Tue, 30 Mar 2021 16:33:08 +0200
> Emond Papegaaij <emond.papegaaij at gmail.com> wrote:
>
> > Hi,
> >
> > We are working on a configuration to get a cluster that requires
> > minimal effort to keep running and is mostly resilient to failures. We
> > use streaming replication on PG 12/Pgpool 4.1.4 with the following
> > settings:
> >
> > master_slave_mode = on
> > master_slave_sub_mode = 'stream'
> > sr_check_period = 5
> > sr_check_database = 'postgres'
> > delay_threshold = 0
> >
> > Health checks are configured as:
> >
> > health_check_period = 5
> > health_check_timeout = 20
> > health_check_database = ''
> > health_check_max_retries = 0
> > health_check_retry_delay = 1
> > connect_timeout = 10000
> >
> > For failover/failback and consensus we use:
> >
> > failover_on_backend_error = on
> > detach_false_primary = off
> > search_primary_node_timeout = 0
> > auto_failback = on
> > auto_failback_interval = 10
> >
> > failover_when_quorum_exists = on
> > failover_require_consensus = on
> > allow_multiple_failover_requests_from_node = off
> > enable_consensus_with_half_votes = off
> >
> > With this setup we get a fairly reliable failover when a backend node
> > is lost. However, when connectivity to that node is restored, it
> > sometimes does not rejoin the cluster. Using pcp_node_indo to get the
> > status of the node, we get quite inconsistent results:
> >
> > Hostname : 172.29.30.1
> > Port : 5432
> > Status : 3
> > Weight : 0.250000
> > Status Name : down
> > Role : standby
> > Replication Delay : 0
> > Replication State : streaming
> > Replication Sync State : async
> > Last Status Change : 2021-03-29 16:02:09
> >
> > So pgpool detects that the node is streaming/async with 0 delay, but
> > still reports it as down. I would expect this node to be re-attached
> > automatically with because auto_failback = on. In this situation, we
> > tried restarting the pgpool nodes one by one, but the status = down
> > remained persistent in the cluster. Only when we stopped all pgpool
> > nodes simultaneously, they were able to recover. Is this behavior as
> > expected or do we need to change something in our configuration for
> > pgpool to recover in a situation like this?
>
> If you turn on "auto_failback", the DOWN node with "Replication State : streaming"
> should be re-attached automatically.
>
> To figure out the cause, could you share the pgpool log?
>
> > Best regards,
> > Emond Papegaaij
> > _______________________________________________
> > pgpool-general mailing list
> > pgpool-general at pgpool.net
> > http://www.pgpool.net/mailman/listinfo/pgpool-general
>
>
> --
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS, Inc. Japan
More information about the pgpool-general
mailing list