Re: Best option for expanding beyond 1 pg server in this situation

----- "Jamie Tufnell" <diesql@googlemail.com> escribió:

> 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 pooling, replication and load balancing. It does
failoverand failback (online recovery) via PCP commands of pgpool-II and it's working fine. Only real restriction of
pgpool-IIis that you can't use CURRENT_TIMESTAMP, SERIALS and such things that could produce different results on each
nodebecause the replication happens by replicating SQL sentences. 

My next step is to try Master/Slave with pgpool-II and Slony-I. In this setup, pgpool-II lets Slony-I do the
replicationand it does the rest of stuff. As far as I know, Slony-I does not have the above mentioned restrictions (not
thatwe 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 case, just that my database is 30 GB in size.
pgpool-IIis working fine by me. Given that inserts happen only because of nightly batch processes, I wouldn't really
mindlocking the tables if I had to use serials and such, although 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
(Iwas, too, at the beginning), please let me know. 

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

"Ubi sapientas ibi libertas"