Обсуждение: Best option for expanding beyond 1 pg server in this situation
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
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.
----- "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"