You are viewing lotso

SQL - Slony-I (step 1)

Been playing around with some level of replication for Postgresql. Like in all FOSS based software, there is lots of choices to choose from and that, in itself, though a blessing is also a curse. There’s just too many choices! (Both Foss and Non-Foss per se)

1. Sequoia
2. PgCluster
3. CyberCluster
4. Slony-I
5. PgPool
6. Skytools (this is skype)

and i believe the list goes on. In any case, my requirements are just 2 I think. (for now anyway)

i. Replicate only a subset of the tables. (not the entire db)
(AFAIK, pgcluster, while easier to configure is also an entire DB replication solution, which is not what I wanted)

ii. Connection load balancing to a few read-only slaves (for select queries only)

Hence, based on the overflowing amount of information of which option to choose, I finally arrived at using slony-I and pgpool and of the two options, I’ve (more or less) already completed the configuration of Slony-I.

For Slony-I, I made sure that I understood how to do the “old-style” which is by using the cli, before I moved on to doing the rest of the configuration using pgadmin which is way easier.

There are a few caveats when using Slony-I and I’ll list down my experiences when I’m playing with it using both gentoo and centos 4 (this is running in a VM)

1st off, version 1.2.12 is out from the slony-website but gentoo is still at 1.2.10. The easiest thing to do with this is just to hack the ebuild and change the version from 1.2.10--> 1.2.12 (gentoo bug #143600) and move it to /usr/local/portage.

So, in that sense, building on gentoo was relatively straightforward and less than 10 min job (excluding compilation)

But on centos, it’s another matters since there’s no default rpm supplied. Only a src rpm was supplied and not being too utterly familiar with it, (i’ve switched to using gentoo nearly 4/5 years ago as I hated fedora’s upgrade cycle and centos was “supposed” to be server-grade.)

In anycase, most of the caveats are when dealing with centos. For one, since this is a src.rpm, you have to compile it 1st.

Hence, you need these additional packages :

1. bison
2. flex
3. gcc (and all it’s dependencies)
4. rpm-build
5. postgresql-devel
6. docbook-style-dsssl
7. netpbm-progs (and netpbm dependency)
6. (there might be more as I didn’t document it)

Once you start compiling it, you’ll run into 1 error which is caused by the NAMELEN of the docs. (this is marked as bug #159382 and the solution is to either upgrade to centos 5 (supposed to be fixed by this release. Keyword = supposed) or to hack it. (I chose to hack it)

depending on where your docbook files are, you can do this
cd /usr/share/sgml && perl -pi.bak -e ‘s/(NAMELEN\s+)44/${1}256/’ ‘find . -type f |xargs grep ’NAMELEN.*44’|sed -e ‘s/:.*//’‘

So, after that is resolved, (which took 1-2 hours w/ scouring net etc.) Then move on to the experimenting stage. I used articles from these few locations :

slony-i official docs
WhoAmI’s Blog
OnLamp Article from 2005
Pgadmin Archives
Pgadmin Docs

Anyway, a few more caveats with the configuration is.

1. Ensure you use a .pgpass file for the passwords (chmod go-rwx ~/.pgpass)*:postgres:pguserpassword*:postgres:pguserpassword

2. Ensure that you use sane configs for your pg_hba.conf file (use trust/ident authentication 1st just in case, to ensure it’s not due to that if it’s not working)

3. ensure that the connection string used for slon/slonik also uses the “user=postgres” line.
(notice that this guide doesn’t have the user to connect as in the slonik shell script. This caused me some headache as I was getting both a password error as well as some “cannot connect admin node xxx issues)

4. Create the replication using either directly using shellscripts or using pgadmin3. (i followed both the examples from the pgadmin docs as well as the mail I found on the pgadmin mailing list - links provided above, with the exception that I didn’t make it 2 way as in slave<-->master but only master-->slave and slave-->master.)

5. starting the slon process is as simple as (I used a config file instead)
$cat > slon_master.conf
cluster_name = ‘pgcluster’
conn_info = ‘dbname=testcluster host= user=postgres’

$slon -d4 -f slon_master.conf

(-d4 to give lots of debug output)

on the Master DB
2007-12-24 01:04:12 MYT DEBUG2 syncThread: new sl_action_seq 1 - SYNC 217
2007-12-24 01:04:16 MYT DEBUG2 localListenThread: Received event 10,217 SYNC
2007-12-24 01:04:17 MYT DEBUG2 remoteListenThread_1: queue event 1,195 SYNC
2007-12-24 01:04:17 MYT DEBUG2 remoteListenThread_1: UNLISTEN
2007-12-24 01:04:22 MYT DEBUG2 syncThread: new sl_action_seq 1 - SYNC 218

on the Slave DB
2007-12-23 22:05:02 MYT DEBUG2 remoteWorkerThread_10: SYNC 227 processing
2007-12-23 22:05:02 MYT DEBUG2 remoteWorkerThread_10: no sets need syncing for this event
2007-12-23 22:05:04 MYT DEBUG2 remoteListenThread_10: queue event 10,228 SYNC
2007-12-23 22:05:04 MYT DEBUG2 remoteWorkerThread_10: Received event 10,228 SYNC
2007-12-23 22:05:04 MYT DEBUG3 calc sync size - last time: 1 last length: 2005 ideal: 29 proposed size: 3

6. BTW, there’s no such need to do a database dump and restore of the tables you want to be replicated. It’s as good to just create the schema w/o any data and start the slon processes. I learned that all my effort to dump and restore the replicated tables just ended up in the drain as slony-I will just truncate the table (this was a command I caught a glimpse of when slon started) and restart from scratch. (i really wonder if this is intended behaviour. What happens when the slon processes goes down? and it seems that it’s quite fragile, so I’ll have to look into that.)

Next up is to look at pg-pool. That’ll be another fun(?) thing to look at??

BTW, I’m looking to do the replication to another (low end celeron) box and perhaps just do a raid0 out of 3 drives for greater performance(?) and then pg-pool to load balance it to the raid0 box.

Build performance and redundancy through multiple un-reliablie boxes eh? The google philosophy.
I’ve got a few low end boxes lying around in the office which can be put to use I suspect.
Tags: ,


hello, i am watching your thread

thanks, very helpful


April 2009

Powered by