pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Best option for expanding beyond 1 pg server in this situation 3+ messages / 3 participants [nested] [flat]
* Best option for expanding beyond 1 pg server in this situation @ 2008-11-26 03:48 Jamie Tufnell <diesql@googlemail.com> 0 siblings, 2 replies; 3+ messages in thread From: Jamie Tufnell @ 2008-11-26 03:48 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best option for expanding beyond 1 pg server in this situation @ 2008-11-26 05:12 Scott Marlowe <scott.marlowe@gmail.com> parent: Jamie Tufnell <diesql@googlemail.com> 1 sibling, 0 replies; 3+ messages in thread From: Scott Marlowe @ 2008-11-26 05:12 UTC (permalink / raw) To: Jamie Tufnell <diesql@googlemail.com>; +Cc: pgsql-admin 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best option for expanding beyond 1 pg server in this situation @ 2008-11-26 08:39 Jaume Sabater <jsabater@linuxsilo.net> parent: Jamie Tufnell <diesql@googlemail.com> 1 sibling, 0 replies; 3+ messages in thread From: Jaume Sabater @ 2008-11-26 08:39 UTC (permalink / raw) To: pgsql-admin ----- "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 failover and failback (online recovery) via PCP commands of pgpool-II and it's working fine. Only real restriction 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 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 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 case, just that my database is 30 GB in size. pgpool-II is working fine by me. Given that inserts happen only because of nightly batch processes, I wouldn't really mind locking 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 (I was, too, at the beginning), please let me know. -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas" ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-11-26 08:39 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-11-26 03:48 Best option for expanding beyond 1 pg server in this situation Jamie Tufnell <diesql@googlemail.com> 2008-11-26 05:12 ` Scott Marlowe <scott.marlowe@gmail.com> 2008-11-26 08:39 ` Jaume Sabater <jsabater@linuxsilo.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox