SQL - pgpool-II (Step 2)
I've already detailed the 1st step to getting Slony to work in a previous blog. (that was on a development machine/vmware image. When I tried it on the production/slave server, I was faced with some issues which I might elaborate in another post. It all boils down myself shooting my own foot. What to do, it was a time when I wasn't connected to the internet and thus, no googling privileges.)
So, here are my experience with pgpool and it's also a little bit like shooting myself in the foot (again!)
First off, I started out with using the _wrong_ version of pgpool. The newest version of pgpool-II (note that -> pgpool-II and not pgpool-I) is 2.0.1 and the newest version of pgpool found on the yum mirrors (I'm using centos4/5) was 2.01 (well, the numbers match don't they?) The only different was the one available on the yum mirrors was that of pgpool-I and not pgpool-II. However, since documentation on pgpool were sparse (I googled everywhere, read all the relevant and NON-relevant mailing list and found nothing much to go on by.)
It was not until I signed up to the pgpool mailing list (which was very low volume by the way) and interacting with one of the Japanese developer did I find out that I was in-fact using the OLD version of pgpool which was pgpool-I which, unfortunately had the same version as pgpool-II!
(I even downloaded the tarball from pgfoundry[but I _did_ download the _correct_ tarball] and searched through the source to figure out what was happening.)
By that little(big!) mistake I did, I was tearing my hair out for the past 3+ weeks. (well, I didn't play with it everyday and in-between my dayjob and such....) However, I did get pgpool-I to work properly with a little tweaking and I could get load-balancing to work, albeit it was not as advertise as in I can't get it to work without it functioning as replication as well. (of sorts anyway, which was the reason I can't deploy it as I was using slony)
So, after I found out my mistake last friday, I started to google for a new RPM of pgpool-II (newest version 2.0.1) but was unable to locate it in any place. The latest RPM I could find was that of version 1.3 which was _too_ old in a sense. (It's always better to have the latest stable version) So, I had to engineer a way to get a RPM from the tarball. Luckily, the tarball from pgfoundry also contained the pgpool.spec file, which was packaged by Devrim. Unfortunately for my, the spec file was a little old in that it refered to the 2.0 beta1 version. It wasn't too much of an issue as all it needed was a little hack here and a little hack there. (I was getting bad owner/group permission error which I narrowed down to the .spec file not having valid user/groups.)
After that was done a rpmbuild -ba pgpool.spec and I got an RPM.
After that, I just installed it, configured the pgpool.conf and got it up and running as advertised with replication mode off, master slave mode on and load balancing mode on.
Cool.. I'm rolling this to production on Monday.
So, this means I'll have 1x Master (1.7G celeron/768MB ram, 500G Raid1 with ~200GB of data), 1xSlave (1.7G celeron/512MB ram 3x160GB raid0). I still have another box sitting under my desk which has even poorer specs than the above, but I think it'll work out just fine.
Cool..Ultra Cool Even!!
If anyone wants the RPM or the modified spec file, do drop me a line and I'll post it to you or something.