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

[Lucas Luengas]

Hello.

I am using "load_balance_mode = off".

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

If I execute sql sentence "show pool nodes", then failover command is
executed on standby node.

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/20160624/693c4078/attachment.htm>