[pgpool-general: 3059] Re: PGPool connection dropped when using pg_detach

[Tatsuo Ishii]

I cannot imagine how you maintenance the database while a user
connects to the database:-)

BTW I'm not sure if this is usefull for you or not,
but... pcp_detach_node accepts -g option which force pcp_detach_node
wait until all clients exit.

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

> Cool,
> 
> Thanks Tatsuo.
> 
> Is there a way of taking a postgresql node out of a pool without causing
> connections to other postgresql nodes to drop?
> 
> This would be used in a situation such as database maintenance.
> 
> Cheers,
> 
> 
> James Sewell,
> PostgreSQL Team Lead / Solutions Architect
> ______________________________________
> 
> 
>  Level 2, 50 Queen St, Melbourne VIC 3000
> 
> *P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099
> 
> 
> 
> On Fri, Jul 25, 2014 at 2:43 PM, Tatsuo Ishii <ishii at postgresql.org> wrote:
> 
>> > Hey all,
>> >
>> > This is a seemingly a pretty bad problem which I uncovered as part of my
>> > last post, so the start of the message will be similar.
>> >
>> > I have two pgpool nodes which I am using a TCP load balancer to spread
>> > between. I am using watchdog to synchronise PostgreSQL node information
>> > between the two and an external HA solution (with ALLOW_TO_FAILOVER).
>> >
>> > If I start both my pgpool nodes up I get the following initial state:
>> >
>> > postgres=# show pool_nodes;
>> >  node_id |  hostname   | port | status | lb_weight |  role
>> > ---------+-------------+------+--------+-----------+---------
>> >  0       | 10.10.10.1   | 5432 | 2      | 0.500000  | standby
>> >  1       | 10.10.10.2   | 5432 | 2      | 0.500000  | primary
>> > (2 rows)
>> >
>> > Now I open a PSQL connection and do the following:
>> >
>> > postgres=# SELECT inet_server_addr();
>> >  inet_server_addr
>> > ------------------
>> >  10.10.10.2
>> > (1 row)
>> >
>> > This shows I am connected to the primary.
>> >
>> > I can run this multiple times and I will always be connected to the
>> > primary, as long as I don't close the psql session.
>> >
>> > Then from another window I run the following command:
>> >
>> >  pcp_detach_node 1 load_balancer 9898 postgres postgres 0
>> >
>> > And in the same PSQL session run the command again:
>> >
>> > postgres=# SELECT inet_server_addr();
>> > SSL SYSCALL error: EOF detected
>> > The connection to the server was lost. Attempting reset: Succeeded.
>> >
>> > This is strange. Why has my master connection been severed?
>>
>> It's an expected behavior of pcp_detach_node, which causes failover
>> and all existing sessions are disconnected.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
> 
> -- 
> 
> 
> ------------------------------
> The contents of this email are confidential and may be subject to legal or 
> professional privilege and copyright. No representation is made that this 
> email is free of viruses or other defects. If you have received this 
> communication in error, you may not copy or distribute any part of it or 
> otherwise disclose its contents to anyone. Please advise the sender of your 
> incorrect receipt of this correspondence.