[Pgpool-general] SQL sessions die when a node fails

[J. Carlos Muro]

Well, I expect redundancy.. I mean, if one db node fails, applications to
work as if nothing has happened... (at least that is what I understand as
HA). From that, I would expect my boss to be so happy that he could improve
my salary, or at least not to be fired, hehe..
Is there any way to achieve it? I have a webapplication (based on
JBoss/Hibernate) that connects to the database through pgpool. This
JBoss/Hibernate thingy keeps a pool of connections open (in this case
against pgpool). If one of my nodes fails, all those connections would be
death from pgpool and the application wont work...
Thank you!!
J. Carlos Muro

pd. I resent this email as I realized that the last time I sent it not to
the list but just to Tatsuo. Sorry!

2009/6/26 Tatsuo Ishii <ishii at sraoss.co.jp>

> Yes, it's pretty normal. What behavior are you expecting?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
> > Hi! If I shutdown a postgresql node while I am in a sql session, it will
> > stop working. Is that normal?
> >
> > I have the next scenario:
> >   vapp1a: server with pgpool, listening port 5431
> >   vpgsql1a: server with postgresql, backend 0
> >   vpgsql1b: server with postgresql, backend 1
> >
> > Setups for pgpool:
> >     replication_mode = true
> >     load_balance_mode = true
> >     replicate_select = true
> >
> > Pgpool start logs look this way:
> >   Jun 25 18:31:49 vapp1a pgpool: 2009-06-25 18:31:41 DEBUG: pid 30820:
> > num_backends: 2 num_backends: 2 total_weight: 2.000000
> >   Jun 25 18:31:49 vapp1a pgpool: 2009-06-25 18:31:41 DEBUG: pid 30820:
> > backend 0 weight: 1073741823.500000
> >   Jun 25 18:31:49 vapp1a pgpool: 2009-06-25 18:31:41 DEBUG: pid 30820:
> > backend 1 weight: 1073741823.500000
> >   [....]
> >   Jun 25 18:31:51 app1a pgpool: 2009-06-25 18:31:42 LOG:   pid 30820:
> pgpool
> > successfully started
> >   Jun 25 18:31:51 app1a pgpool: 2009-06-25 18:31:42 DEBUG: pid 30861: I
> am
> > PCP 30861
> >
> > I log-in to the database through  pgpool and execute some inserts:
> >   pgpool at vapp1:~$ psql -h localhost -p 5431 -U auser mybase
> >   mybase=> create table test (id integer);
> >   mybase=> insert into test (1);
> >
> > I see that row on both vpgsql1a and vpgsql1b. I won't exit the psql
> session
> > yet...
> >
> > Now I stop backend 1 and get the next pgpool logs:
> >   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30860:
> > detect_error: kind: E
> >   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30860:
> > detect_stop_postmaster_error: receive admin shutdown error from a node.
> >   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30860:
> > notice_backend_error: 1 fail over request from pid 30860
> >   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> > failover_handler called
> >   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> > failover_handler: starting to select new master node
> >   Jun 25 18:36:59 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30820:
> > starting degeneration. shutdown host vpgsql1b(5432)
> >   Jun 25 18:36:59 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> > failover_handler: kill 30829
> >   [....]
> >   Jun 25 18:37:00 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30820:
> > failover_handler: set new master node: 0
> >   Jun 25 18:37:00 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30852:
> child
> > receives shutdown request signal 3
> >   [....]
> >   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30820:
> > failover done. shutdown host vpgsql1b(5432)
> >   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> > reap_handler called
> >   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> > reap_handler: call wait3
> >   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> child
> > 30829 exits with status 0 by signal 0
> >   [....]
> >   Jun 25 18:37:06 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> > reap_handler: normally exited
> >
> > In the psql session I try to insert a new row, getting:
> >   mybase=> insert into kk values (2);
> >   server closed the connection unexpectedly
> >           This probably means the server terminated abnormally
> >           before or while processing the request.
> >   The connection to the server was lost. Attempting reset: Succeeded.
> >
> > Now I exit psql, and log-in again, now successfully inserting a new row:
> >   pgpool at vapp1:~$ psql -h localhost -p 5431 -U auser mybase
> >   mybase=> create table test (id integer);
> >   mybase=> insert into test (2);
> >   INSERT 0 1
> >
> > Is that normal?
> > Thanks in advance,
> > J. Carlos Muro
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20090630/15c19b93/attachment-0001.html>