[pgpool-general: 5387] Re: pgpool-ii across different countries different data centers)

[Tatsuo Ishii]

Essentially you want to set up pgpool.conf like this:

For USA Pgpool-II:
backend_hostname0 = 'usasever.com'
backend_weight0 = 1
backend_hostname1 = 'asiasever.com'
backend_weight1 = 0

For Asia Pgpool-II:
backend_hostname0 = 'usasever.com'
backend_weight0 = 0
backend_hostname1 = 'asiasever.com'
backend_weight1 = 1

Notice the difference of weight0/1 parameter between USA and Aisa
Pgpool-II.

This way, all read/write access will be sent to usasever.com via USA
Pgpool-II. While all read access will be sent to asiaserver.com and
all write access will be sent to usaserver.com via Asia Pgpool-II.

Problem is, Pgpool-II generates internal queries to the master server
(in your case usaserver.com). This happens when Pgpool-II needs to
make sure that particular table is not temporary table for
example. This is not a problem for USA Pgpool-II because it sends all
queries to usaserver.com anyway. 

However, this is a problem for Asia Pgpool-II, because it sends queries
to USA server across the ocean, which may be slow.  If you are sure
that you are not using temp tables, you can disable the query by
setting "check_temp_table = off". Same thing can be said to
"check_unlogged_table".

Also Pgpool-II keeps internal cache to reduce issuing such that
queries. If you access many tables you might want to increase
"relcache_size".

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi,
> 
> New to the list and have been busy setting up pgpool-ii this last week or
> two.
> 
> I've previously used drbd/slony for replication but pgpool looked like a
> good choice, so i'm trying it!
> 
> I am wondering if it is possible to have pgpool-ii run across servers far
> apart (different countries):
> 
> I have postgresql streaming replication working, from my USA to my Asia
> server and it seems ok.
> 
> However i want pgpool to be used to automatically send reads to the Asia
> server and writes to the USA server for users in Asia.
> 
> Ideally I want:
> - Master in the USA with pgpool-ii - with a webserver that will serve most
> of the world, reads/writes are local
> - Slave in Asia with pgpool-ii - wtih a webserver that will server reads
> from Asia/Slave and send writes to the Master
> - Both servers only have public ip's
> - For the Master/USA server, i'm happy for all reads/writes to go to the
> Master.
> - For the Slave/Asia server I would like pgpool-ii to proxy database
> requests - sending writes to the USA server and doing reads from the Asia
> server....
> 
> *In this situation I don't think i need a 'virtual ip' (i'm not trying to
> load balance USA requests). Is this correct?*
> 
> Is this setup even possible?
> 
> Thanks in advance.
> 
> Jon