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

Best option for expanding beyond 1 pg server in this situation

From
"Jamie Tufnell"
Date:
Hi,

We have been wanting to HA our pgsql server for some time now.  We're
actually reaching the limits of our current server now, so there is a
pressing need to do something to ease the load too.

There are several individual solutions to both of these problems.  I
was hoping I could get some advice from someone more experienced
though, about what the simplest most effective way of moving forward
would be?  Hopefully I can squash both HA and LB with the same
solution.

So far I've narrowed it down to

Slony-I master/slave replication with failover

or

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?

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).

Any advice/help much appreciated.

Jamie

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

From
"Scott Marlowe"
Date:
On Tue, Nov 25, 2008 at 8:48 PM, Jamie Tufnell <diesql@googlemail.com> wrote:
> Hi,
>
> We have been wanting to HA our pgsql server for some time now.  We're
> actually reaching the limits of our current server now, so there is a
> pressing need to do something to ease the load too.
>
> There are several individual solutions to both of these problems.  I
> was hoping I could get some advice from someone more experienced
> though, about what the simplest most effective way of moving forward
> would be?  Hopefully I can squash both HA and LB with the same
> solution.
>
> So far I've narrowed it down to
>
> Slony-I master/slave replication with failover

We use this where I work and it works quite well.  We have some custom
scripts that detect failure of the master db and initiates failover to
the slave if necessary.  We do this from the applicaiton level.

> 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?

Seems to be.  I haven't played with pgpool in a long time so don't
take my word as authoritative.

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

From
Jaume Sabater
Date:
----- "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"