Hi <div><br></div><div>Thanks for the reply. </div><div><br></div><div>In my tests, it seems that as long as one of the pgpool stays up, the state of a database (in-sync or not) is correctly preserved. When they both go down, the out-of-sync state of the databases can be lost.</div>
<div><br></div><div>In your description, it might be technically ok that pgpool accepts to use postgres2 (which is out of sync) but I was not expecting this behavior. I think that the database that is marked out of sync should remain marked out of sync untill a human goes and says to resync it. If a database previously marked out of sync is made the new master just because of a specific sequence of start up events, I think that this is risky.</div>
<div><br></div><div>I suppose that it is up to us to implement a failover command ( failover_command in pgpool.conf ) to forbid a database marked as out of sync to start again until we can sync it properly and explicitly (ex: from backup and transaction log). I will probably do something like that. Luckily, the sequence of events to reproduce this problem is unlikely but still certainly possible.</div>
<div><br></div><div>Thanks</div><div><br></div><div>Gilbert<br><br><div class="gmail_quote">On Thu, Feb 14, 2013 at 9:47 PM, Nozomi Anzai <span dir="ltr"><<a href="mailto:anzai@sraoss.co.jp" target="_blank">anzai@sraoss.co.jp</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
Finally, I think pgpool worked correctly.<br>
<div class="im"><br>
> Hello,<br>
><br>
> I attach the logs for pgpool on server1 and server2.<br>
><br>
> The sequence is as described in my first email. Server 2 was made out of<br>
> sync (simply stoppping postgres on server2) :<br>
><br>
> [root@daphne-d pg_log]# psql -p 5431 -h 192.168.0.109 -c "show pool_nodes;"<br>
> -U postgres<br>
> psql: [root@daphne-d pg_log]# psql -p 5431 -h 192.168.0.109 -c "show<br>
> pool_nodes;" -U postgres<br>
> node_id | hostname | port | status | lb_weight | role<br>
> ---------+---------------+------+--------+-----------+--------<br>
> 0 | 192.168.0.102 | 5432 | 2 | 0.500000 | master<br>
> 1 | 192.168.0.103 | 5432 | 3 | 0.500000 | slave<br>
> (2 rows)<br>
><br>
> and, then:<br>
><br>
> - stop pgpool 1 and pgpool2<br>
> - stop postgres1,<br>
> - start postgres2<br>
> - start pgpool2<br>
<br>
</div>At this time, pgpool tried to connect postgres1 and failed, and failover<br>
was executed. So pgpool marked postgres1 as down and desided postgres2<br>
(node 1) is new master.<br>
If pgpool2 started with both of postgres1 and postgres2, failover didn't<br>
occur.<br>
<br>
----<br>
2013-02-13 22:07:45 ERROR: pid 18382: connect_inet_domain_socket: connect() failed: Connection refused<br>
2013-02-13 22:07:45 ERROR: pid 18382: connection to 192.168.0.102(5432) failed<br>
(snip)<br>
2013-02-13 22:07:45 LOG: pid 18341: Restart all children<br>
2013-02-13 22:07:45 LOG: pid 18341: failover: set new primary node: -1<br>
2013-02-13 22:07:45 LOG: pid 18341: failover: set new master node: 1<br>
2013-02-13 22:07:45 LOG: pid 18341: failover done. shutdown host 192.168.0.102(5432)<br>
----<br>
<br>
Why postres2, which is out of sync in fact, can be the master node as<br>
follows:<br>
Pgpool don't know if postgres2 is exactly synced or not when pgpool<br>
just starts.<br>
It can recognize the differences among nodes by results of queries<br>
(succeeded or failed, and if each counts of rows is the same).<br>
<br>
<br>
And,<br>
<div class="im"><br>
> - stop pgpool2<br>
> - start pgpool2<br>
<br>
</div>at this time, failover was executed due to the same reason, too.<br>
<div class="HOEnZb"><div class="h5"><br>
><br>
> and, after those events, the situation was inverse:<br>
><br>
> [root@daphne-d pg_log]# psql -p 5431 -h 192.168.0.109 -c "show pool_nodes;"<br>
> -U postgres<br>
> node_id | hostname | port | status | lb_weight | role<br>
> ---------+---------------+------+--------+-----------+--------<br>
> 0 | 192.168.0.102 | 5432 | 3 | 0.500000 | slave<br>
> 1 | 192.168.0.103 | 5432 | 2 | 0.500000 | master<br>
> (2 rows)<br>
><br>
> Server2 was out of sync and only stopping postgres on server1 and<br>
> restarting pgpool on server2 created that situation. Server2 was never<br>
> synchronized explicitly but at it end of this sequence, it is the only live<br>
> node.<br>
><br>
> Thanks<br>
><br>
> Gilbert<br>
><br>
><br>
><br>
> On Wed, Feb 13, 2013 at 7:07 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>> wrote:<br>
><br>
> > > Hello,<br>
> > ><br>
> > > I am still testing pgpool before deploying with our production database.<br>
> > ><br>
> > > When one of the databases goes down, my understanding is that it should<br>
> > be<br>
> > > marked as down by pgpool until we resync it manually. However, in the<br>
> > > following (admittedly paranoiac) scenario, I think that it does not:<br>
> > ><br>
> > > Here is the case. We have 2 identical computers both running pgpool and<br>
> > > postgres. Here is sequence of events:<br>
> > ><br>
> > > - we have DB1 and DB2 running fine, perfectly in sync (replication mode)<br>
> > > - DB2 goes down for some reason and comes back a little later, enough<br>
> > > though to be marked as down by pgpool<br>
> > > - we run like that a little while (DB2 now gets seriously out of sync)<br>
> > > - now, just as a test, do the following:<br>
> > > - stop everything (pgpool1, pgpool2, DB1, DB2)<br>
> > > - start DB2 and then pgpool2<br>
> > > - everything is good, pgpool refuses to use DB2 (which is out of<br>
> > sync)<br>
> > > - however, stop and start again pgpool2 (with DB1 down and DB2 up) and<br>
> > > now pgpool hapilly starts using DB2 (while it was marked out of sync)<br>
> ><br>
> > > Is that a normal/desired behavior? I would expect that DB2 should not be<br>
> > > used until I run a sync manually or I tell pgpool to forget about the<br>
> > > previous down status.<br>
> ><br>
> > Pgpool uses DB2 while it is marked out of sync? (or it *was* out of<br>
> > sync, but now it is in sync?) This should not happen unless you are in<br>
> > raw mode(that means both replication_mode = off and master_slave_mode<br>
> > = off).<br>
> ><br>
> > Any can you please show me pgpool log?<br>
> > --<br>
> > Tatsuo Ishii<br>
> > SRA OSS, Inc. Japan<br>
> > English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
> > Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
> ><br>
<br>
<br>
</div></div><span class="HOEnZb"><font color="#888888">--<br>
Nozomi Anzai<br>
SRA OSS, Inc. Japan<br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</font></span></blockquote></div><br></div>