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

[Tatsuo Ishii]

> 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