[pgpool-general: 1392] Re: pgpool using an out of sync database ?

[Nozomi Anzai]

Hi,

Finally, I think pgpool worked correctly.

> Hello,
> 
> I attach the logs for pgpool on server1 and server2.
> 
> The sequence is as described in my first email. Server 2 was made out of
> sync (simply stoppping postgres on server2) :
> 
> [root ¡÷ daphne-d pg_log]# psql -p 5431 -h 192.168.0.109 -c "show pool_nodes;"
> -U postgres
> psql: [root ¡÷ daphne-d pg_log]# psql -p 5431 -h 192.168.0.109 -c "show
> pool_nodes;" -U postgres
>  node_id |   hostname    | port | status | lb_weight |  role
> ---------+---------------+------+--------+-----------+--------
>  0       | 192.168.0.102 | 5432 | 2      | 0.500000  | master
>  1       | 192.168.0.103 | 5432 | 3      | 0.500000  | slave
> (2 rows)
> 
> and, then:
> 
> - stop pgpool 1 and pgpool2
> - stop postgres1,
> - start postgres2
> - start pgpool2

At this time, pgpool tried to connect postgres1 and failed, and failover
was executed. So pgpool marked postgres1 as down and desided postgres2
(node 1) is new master.
If pgpool2 started with both of postgres1 and postgres2, failover didn't
occur.

----
2013-02-13 22:07:45 ERROR: pid 18382: connect_inet_domain_socket: connect() failed: Connection refused
2013-02-13 22:07:45 ERROR: pid 18382: connection to 192.168.0.102(5432) failed
(snip)
2013-02-13 22:07:45 LOG:   pid 18341: Restart all children
2013-02-13 22:07:45 LOG:   pid 18341: failover: set new primary node: -1
2013-02-13 22:07:45 LOG:   pid 18341: failover: set new master node: 1
2013-02-13 22:07:45 LOG:   pid 18341: failover done. shutdown host 192.168.0.102(5432)
----

Why postres2, which is out of sync in fact, can be the master node as
follows:
Pgpool don't know if postgres2 is exactly synced or not when pgpool
just starts.
It can recognize the differences among nodes by results of queries
(succeeded or failed, and if each counts of rows is the same).


And,

> - stop pgpool2
> - start pgpool2

at this time, failover was executed due to the same reason, too.

> 
> and, after those events, the situation was inverse:
> 
> [root ¡÷ daphne-d pg_log]# psql -p 5431 -h 192.168.0.109 -c "show pool_nodes;"
> -U postgres
>  node_id |   hostname    | port | status | lb_weight |  role
> ---------+---------------+------+--------+-----------+--------
>  0       | 192.168.0.102 | 5432 | 3      | 0.500000  | slave
>  1       | 192.168.0.103 | 5432 | 2      | 0.500000  | master
> (2 rows)
> 
> Server2 was out of sync and only stopping postgres on server1 and
> restarting pgpool on server2 created that situation. Server2 was never
> synchronized explicitly but at it end of this sequence, it is the only live
> node.
> 
> Thanks
> 
> Gilbert
> 
> 
> 
> On Wed, Feb 13, 2013 at 7:07 PM, Tatsuo Ishii <ishii ¡÷ postgresql.org> wrote:
> 
> > > Hello,
> > >
> > > I am still testing pgpool before deploying with our production database.
> > >
> > > When one of the databases goes down, my understanding is that it should
> > be
> > > marked as down by pgpool until we resync it manually. However, in the
> > > following (admittedly paranoiac) scenario, I think that it does not:
> > >
> > > Here is the case. We have 2 identical computers both running pgpool and
> > > postgres. Here is sequence of events:
> > >
> > > - we have DB1 and DB2 running fine, perfectly in sync (replication mode)
> > > - DB2 goes down for some reason and comes back a little later, enough
> > > though to be marked as down by pgpool
> > > - we run like that a little while (DB2 now gets seriously out of sync)
> > > - now, just as a test, do the following:
> > >     - stop everything (pgpool1, pgpool2, DB1, DB2)
> > >     - start DB2  and then pgpool2
> > >     - everything is good, pgpool refuses to use DB2 (which is out of
> > sync)
> > >    - however, stop and start again pgpool2 (with DB1 down and DB2 up) and
> > > now pgpool hapilly starts using DB2 (while it was marked out of sync)
> >
> > > Is that a normal/desired behavior?  I would expect that DB2 should not be
> > > used until I run a sync manually or I tell pgpool to forget about the
> > > previous down status.
> >
> > Pgpool uses DB2 while it is marked out of sync? (or it *was* out of
> > sync, but now it is in sync?) This should not happen unless you are in
> > raw mode(that means both replication_mode = off and master_slave_mode
> > = off).
> >
> > Any can you please show me pgpool log?
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> > English: http://www.sraoss.co.jp/index_en.php
> > Japanese: http://www.sraoss.co.jp
> >


-- 
Nozomi Anzai
SRA OSS, Inc. Japan