[pgpool-general: 4749] Re: Failover_command is not executed on standby server

[Lucas Luengas]

Hello.

I have not enabled health checking.

I have uninstalled pgpool version 3.4.7 (tataraboshi). After, I have
installed 3.4.6 (tataraboshi) and failover command is executed on standby
server.

Regards.


On Sat, Jun 25, 2016 at 2:37 AM, Tatsuo Ishii <ishii at postgresql.org> wrote:

> > Hello.
> >
> > I am using "load_balance_mode = off".
>
> I think load_balance_mode is irrelevant here.
>
> > When postgresql service on standby node is down, pgpool log file on both
> > nodes shows every 10 second:
> >
> > 2016-06-24 15:12:52: pid 5256: ERROR:  Failed to check replication time
> lag
> > 2016-06-24 15:12:52: pid 5256: DETAIL:  No persistent db connection for
> the
> > node 1
> > 2016-06-24 15:12:52: pid 5256: HINT:  check sr_check_user and
> > sr_check_password
> > 2016-06-24 15:12:52: pid 5256: CONTEXT:  while checking replication time
> lag
> > 2016-06-24 15:12:52: pid 5256: LOG:  failed to connect to PostgreSQL
> server
> > on "172.16.0.2:5432", getsockopt() detected error "Connection refused"
> > 2016-06-24 15:12:52: pid 5256: ERROR:  failed to make persistent db
> > connection
> > 2016-06-24 15:12:52: pid 5256: DETAIL:  connection to host:"
> 172.16.0.2:5432"
> > failed
>
> It's normal because:
>
> > 2016-06-24 15:12:52: pid 5256: DETAIL:  No persistent db connection for
> the
> > node 1
> > 2016-06-24 15:12:52: pid 5256: HINT:  check sr_check_user and
>
> means replicaton delay checking is failing (of course it fails).
>
> Have you enabled health checking? Otherwise pgpool doesn't notice that
> PostgreSQL goes down.
>
> > If I execute sql sentence "show pool nodes", then failover command is
> > executed on standby node.
>
> Even without health checking, if you try to execute an SQL command,
> that could trigger fail over.
>
> > 2016-06-24 15:14:48: pid 5329: LOG:  received degenerate backend request
> > for node_id: 1 from pid [5329]
> > 2016-06-24 15:14:48: pid 5329: FATAL:  failed to create a backend
> connection
> > 2016-06-24 15:14:48: pid 5329: DETAIL:  executing failover on backend
> > 2016-06-24 15:14:48: pid 1096: LOG:  watchdog notifying to start
> > interlocking
> > 2016-06-24 15:14:48: pid 1096: LOG:  watchdog became a new lock holder
> > 2016-06-24 15:14:48: pid 1099: LOG:  sending watchdog response
> > 2016-06-24 15:14:48: pid 1099: DETAIL:  WD_STAND_FOR_LOCK_HOLDER received
> > but lock holder already exists
> > 2016-06-24 15:14:49: pid 1096: LOG:  starting degeneration. shutdown host
> > 172.16.0.2(5432)
> > 2016-06-24 15:14:49: pid 1096: LOG:  Restart all children
> > 2016-06-24 15:14:49: pid 1096: LOG:  execute command:
> > /etc/pgpool-II/failover_stream.sh 1 172.16.0.1 /tmp/trigger_file0
> > 2016-06-24 15:14:49: pid 5307: LOG:  child process received shutdown
> > request signal 3
> > 2016-06-24 15:14:49: pid 5363: LOG:  child process received shutdown
> > request signal 3
> > 2016-06-24 15:14:49: pid 5314: LOG:  child process received shutdown
> > request signal 3
> >
> > (... more similar lines in log file ...)
> >
> > 2016-06-24 15:14:49: pid 5328: LOG:  child process received shutdown
> > request signal 3
> > 2016-06-24 15:14:49: pid 5093: LOG:  child process received shutdown
> > request signal 3
> > 2016-06-24 15:14:49: pid 1096: LOG:  watchdog notifying to end
> interlocking
> > 2016-06-24 15:14:50: pid 1096: LOG:  failover: set new primary node: 0
> > 2016-06-24 15:14:50: pid 1096: LOG:  failover: set new master node: 0
> > 2016-06-24 15:14:50: pid 5412: LOG:  failback event detected
> > 2016-06-24 15:14:50: pid 5412: DETAIL:  restarting myself
> > failover done. shutdown host 172.16.0.2(5432)2016-06-24 15:14:50: pid
> 1096:
> > LOG:  failover done. shutdown host 172.16.0.2(5432)
> > 2016-06-24 15:14:50: pid 5256: ERROR:  Failed to check replication time
> lag
> > 2016-06-24 15:14:50: pid 5256: DETAIL:  No persistent db connection for
> the
> > node 1
> > 2016-06-24 15:14:50: pid 5256: HINT:  check sr_check_user and
> > sr_check_password
> > 2016-06-24 15:14:50: pid 5256: CONTEXT:  while checking replication time
> lag
> >
> >
> > I disable watchdog ("use_watchdog = off"), then failover command is
> > executed on standby server when postgresql service is down.
> >
> > Is this working as expected?
> >
> >
> > On Fri, Jun 24, 2016 at 3:52 PM, Tatsuo Ishii <ishii at postgresql.org>
> wrote:
> >
> >> Works for me (without watchdog). When I shutdown standy node, it
> >> triggers failover.
> >>
> >> 2016-06-24 22:46:28: pid 24757: LOG:  reading and processing packets
> >> 2016-06-24 22:46:28: pid 24757: DETAIL:  postmaster on DB node 1 was
> >> shutdown by administrative command
> >> 2016-06-24 22:46:28: pid 24757: LOG:  received degenerate backend
> request
> >> for node_id: 1 from pid [24757]
> >> 2016-06-24 22:46:28: pid 24740: LOG:  starting degeneration. shutdown
> host
> >> /tmp(11003)
> >> 2016-06-24 22:46:28: pid 24740: LOG:  Restart all children
> >> 2016-06-24 22:46:28: pid 24740: LOG:  execute command:
> >> /home/t-ishii/work/pgpool-II/current/aaa/etc/failover.sh 1 /tmp 11003
> >> /home/t-ishii/work/pgpool-II/current/aaa/data1 0 0 /tmp 0 11002
> >> /home/t-ishii/work/pgpool-II/current/aaa/da
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS, Inc. Japan
> >> English: http://www.sraoss.co.jp/index_en.php
> >> Japanese:http://www.sraoss.co.jp
> >>
> >> > Hello.
> >> > I have two nodes with postgresql with streaming replication and pgpool
> >> with
> >> > watchdog.
> >> >
> >> > Using pgpool version 3.4.7 (tataraboshi):
> >> > - If postgres service of standby server is down, then
> failover_command is
> >> > not executed (no lines showed in pgpool log file).
> >> > - If postgres service of primary server is down, then
> failover_command is
> >> > executed (it is showed in pgpool log file).
> >> >
> >> > Using pgpool version 3.4.6 (tataraboshi):
> >> > - If postgres service of standby server is down, then
> failover_command is
> >> > executed (it is showed in pgpool log file).
> >> > - If postgres service of primary server is down, then
> failover_command is
> >> > executed (it is showed in pgpool log file).
> >> >
> >> > Why does failover_command is not executed with version 3.4.7 when
> >> postgres
> >> > service in standby server is down?
> >> >
> >> > Thanks in advance.
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20160701/cdfec46e/attachment.htm>