[pgpool-general: 3835] Re: PGPool-II 3.4.2-1 not saving db state on node detach

[Tatsuo Ishii]

I have tested with 3.4.2 and could not reproduce your problem.
(I do not use rpm init file but do use pgpool_setup which comes with
pgpool source tree for testing purpose).

t-ishii at localhost: cat log/pgpool_status  <--- check initial pgpool_status. Both node up.
up
up
t-ishii at localhost: sh startall
waiting for server to start....24428 2015-06-27 12:49:00 JST LOG:  redirecting log output to logging collector process
24428 2015-06-27 12:49:00 JST HINT:  Future log output will appear in directory "pg_log".
 done
server started
waiting for server to start....24442 2015-06-27 12:49:01 JST LOG:  redirecting log output to logging collector process
24442 2015-06-27 12:49:01 JST HINT:  Future log output will appear in directory "pg_log".
 done
server started
t-ishii at localhost: pcp_detach_node 0 localhost 11001 t-ishii t-ishii 1 < --- let node 1 down.
t-ishii at localhost: psql -p 11000 -c "show pool_nodes" test <-- confirm it.
 node_id | hostname  | port  | status | lb_weight |  role   
---------+-----------+-------+--------+-----------+---------
 0       | /tmp      | 11002 | 2      | 0.500000  | primary
 1       | localhost | 11003 | 3      | 0.500000  | standby
(2 rows)

t-ishii at localhost: sh shutdownall <-- shudown pgpool and PostgreSQL.
2015-06-27 12:49:21: pid 25079: LOG:  stop request sent to pgpool. waiting for termination...
.done.
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
t-ishii at localhost: cat log/pgpool_status <--- check pgpool_status
up
down
t-ishii at localhost: sh startall <-- restart pgpool and PostgreSQL
waiting for server to start....25093 2015-06-27 12:49:32 JST LOG:  redirecting log output to logging collector process
25093 2015-06-27 12:49:32 JST HINT:  Future log output will appear in directory "pg_log".
 done
server started
waiting for server to start....25107 2015-06-27 12:49:33 JST LOG:  redirecting log output to logging collector process
25107 2015-06-27 12:49:33 JST HINT:  Future log output will appear in directory "pg_log".
 done
server started
t-ishii at localhost: psql -p 11000 -c "show pool_nodes" test <-- check pgpool status
 node_id | hostname  | port  | status | lb_weight |  role   
---------+-----------+-------+--------+-----------+---------
 0       | /tmp      | 11002 | 2      | 0.500000  | primary
 1       | localhost | 11003 | 3      | 0.500000  | standby
(2 rows)

> Thanks Tatsuo,
> What I am seeing is that the status file is *not* updated after a
> pcp_detatch_node so after a restart, the node is considered up again.
> 
> When pgpool detects the node is down by itself however, it is written to
> file as expected.
> 
> Regards,
> James Elsdon
> 
> On Thursday, 25 June 2015, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > Hi there,
>> >
>> > I'm not sure if it's the intended behavior, but I'm seeing on PGPool-II
>> > 3.4.2-1 that if I use pcp_detach_node - it works successfully but is not
>> > persistent across pgpool restarts.
>> >
>> > When the node dies naturally (pgpool detaches it), the status file works
>> > correctly - this is only on a manual pcp_detach_node
>> >
>> > [me at host ~]$ /usr/pgsql-9.2/bin/psql -U psgsuser -h psghost -p pgport -c
>> > 'show pool_nodes'
>> >  node_id |    hostname    | port | status | lb_weight |  role
>> > ---------+----------------+------+--------+-----------+--------
>> >  0       | host1          | 5432 | 2      | 0.500000  | master
>> >  1       | host2          | 5432 | 2      | 0.500000  | slave
>> > (2 rows)
>> >
>> > [me at host ~]$ /usr/pgpool-9.2/bin/pcp_detach_node 10 localhost 9898
>> pcpuser
>> > pcppass 1
>> >
>> > [me at host ~]$ /usr/pgsql-9.2/bin/psql -U psgsuser -h psghost -p pgport -c
>> > 'show pool_nodes'
>> >  node_id |    hostname    | port | status | lb_weight |  role
>> > ---------+----------------+------+--------+-----------+--------
>> >  0       | host1          | 5432 | 2      | 0.500000  | master
>> >  1       | host2          | 5432 | 3      | 0.500000  | slave
>> > (2 rows)
>> >
>> > [me at host ~]$ sudo cat /var/log/pgpool/pgpool_status
>> > up
>> > up
>> >
>> > [me at host ~]$ sudo /etc/init.d/pgpool-II-92 stop
>> > Stopping pgpool-II-92 service:                             [  OK  ]
>> >
>> > [me at host ~]$ sudo cat /var/log/pgpool/pgpool_status
>> > up
>> > up
>> >
>> > [me at host ~]$ sudo /etc/init.d/pgpool-II-92 start
>> > Starting pgpool-II-92 service:                             [  OK  ]
>> >
>> > [me at host ~]$ /usr/pgsql-9.2/bin/psql -U psgsuser -h psghost -p pgport -c
>> > 'show pool_nodes'
>> >  node_id |    hostname    | port | status | lb_weight |  role
>> > ---------+----------------+------+--------+-----------+--------
>> >  0       | host1          | 5432 | 2      | 0.500000  | master
>> >  1       | host2          | 5432 | 2      | 0.500000  | slave
>> > (2 rows)
>> >
>> > [me at host ~]$ sudo cat /var/log/pgpool/pgpool_status
>> > up
>> > up
>> >
>> >
>> > Is this the intended behavior of pcp_detach_node ?
>>
>> Yes. pgpool-II will not change the contents of the status file until
>> it is told a failover event (it could be triggered by either
>> pcp_detach_node or failover caused by PostgreSQL backend down).
>>
>> If you do not like it, you can discard the status file and rebuild the
>> file by using current status, which is maintained by health checking.
>> (hint: pgpool -D)
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>