[pgpool-general: 1438] Re: postgresql node to other geographic region

[Videanu Adrian]

Hi,

Indeed i use streaming replication, i will study the pgpool replication fro my nr1 problem. As for the second i will give it a try.
Thanks very much for the quick response.


 
Regards,
Adrian Videanu


________________________________
 From: Tatsuo Ishii <ishii at postgresql.org>
To: videanuadrian at yahoo.com 
Cc: pgpool-general at pgpool.net 
Sent: Wednesday, February 27, 2013 1:52 PM
Subject: Re: [pgpool-general: 1436] postgresql node to other geographic region
 
> Hi all, i currently have a pgpool cluster (v 3.1.3) built from 3 nodes. 
> First problem is that i have some apps that perform operations like this : 
> 
> 1. insert one row (executes on node1)
> 2. select some info from previous insert (executes on node 1 or 2 or 3)  <- i guess that here is the problem because the commands are successive and i pgpool does not has enough time to replicate the data, and this query returns nothing. Also this behavior is random, sometimes works as expected sometimes not. 

Assuming you are using streaming replication mode, the replication
delay you are observing is caused by PostgreSQL's streaming
replication nature, not by pgpool, because it is *asynchronous*
replication.  If you don't like it, you'd better to use pgpool's
replication mode.

> 3. perform some insert based on previous select.
> 
> I have "managed" this problem by reading and writing only from master node, and the other two nodes are used hot standby. I`m aware of the fact that if master fails only slave 2 will become master and the 3rd will not switch to node 2 as master.

No. Which standby promotes is depending on the fail over script. And
it is users's responsibility to write appropreate fail over script.

> So the second problem would be: 
> 
> how will pgpool behaves if i move that 3rd node to other geographic location and in a different subnet ?  I expect to have so downtimes to the order of seconds once in a while..

That should not be much longer than the case which all nodes are on
the same LAN. Rather I'm afraid the replication delay caused by the
network delay.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20130227/db433bcc/attachment.html>