Date: Wed, 26 Nov 2008 08:39:05 +0000 (GMT)
From: Jaume Sabater <jsabater@linuxsilo.net>
To: pgsql-admin@postgresql.org
Message-ID: <29278214.7021227688745147.JavaMail.root@zimbra.linuxsilo.net>
In-Reply-To: <b0a4f3350811251948u21b90730p604d93a4b97c71c2@mail.gmail.com>
Subject: Re: Best option for expanding beyond 1 pg server in this
 situation
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

----- "Jamie Tufnell" <diesql@googlemail.com> escribi=C3=B3:
=20
> pgpool-II with heartbeat for failover.  The pgpool-II configuration
> matrix (http://pgpool.projects.postgresql.org/) isn't terribly clear
> (there's no legend) but it leads me to believe failover and load
> balancing are mutually exclusive options.  Is that so?

No, they are not. I have a PostgreSQL cluster at work with connection pooli=
ng, replication and load balancing. It does failover and failback (online r=
ecovery) via PCP commands of pgpool-II and it's working fine. Only real res=
triction of pgpool-II is that you can't use CURRENT_TIMESTAMP, SERIALS and =
such things that could produce different results on each node because the r=
eplication happens by replicating SQL sentences.

My next step is to try Master/Slave with pgpool-II and Slony-I. In this set=
up, pgpool-II lets Slony-I do the replication and it does the rest of stuff=
. As far as I know, Slony-I does not have the above mentioned restrictions =
(not that we have them at work as we use an ORM, but I can understand that =
for many people they are huge restrictions).

> About our environment: PostgreSQL 8.3.  Over 90% reads.  Low data
> turnover on all tables involved in most of those reads.  High data
> turnover on a select few tables.  Overall database size is quite
> small
> (1GB).

As I said, I cannot talk about Slony-I yet, but your case is basically my c=
ase, just that my database is 30 GB in size. pgpool-II is working fine by m=
e. Given that inserts happen only because of nightly batch processes, I wou=
ldn't really mind locking the tables if I had to use serials and such, alth=
ough I would not like it.

Hope this helps. If you have any more questions about pgpool-II, or if you =
feel confused by the features in the README (I was, too, at the beginning),=
 please let me know.

--
Jaume Sabater
http://linuxsilo.net/

"Ubi sapientas ibi libertas"