postgresql replication using slony-I

As most postgresql users must be knowing, postgresql does not provide any inbuilt replication solution. There are lots of 3rd party replication products available for postgresql. Slony is one of them. Slony is a trigger based replication solution, that is it used triggers to push data to the slave. Slony is supposed to be one of the most stable replication solutions for postgresql.

You can download slony from www.slony.info. There are two major versions of slony – slony-I & slony-II. Slony-I is a simple master-slave replication solution. Whereas slony-II is a advanced multi-master replication solution. We will go ahead with simple master-slave replication solution. So we will download Slony-I. The latest version available is Slony-I 1.2.15. Slony-I 2.0 is in RC and should be soon released. But we will go with a stable release – 1.2.15.

Postgresql version being used is 8.3.3. To install slony, simply untar the downloaded file and run
./configure –with-pgconfigdir=<path to pg_config>
make
sudo make install

I have used two machines for setting up replication. Installed postgresql and slony-I on both of them.

master server ip : 172.16.3.211
slave server ip : 172.16.3.173

We will be working with the superuser postgres which is used to start and stop the postgresql server.

Quick steps

  • Define environment variables on master & slave. The main purpose is to make our task easier. Lets create an env.sh file containing all the definitions.

    #!/bin/sh

    REPLICATIONUSER=postgres
    CLUSTERNAME=replcluster
    MASTERDBNAME=repltestdb
    SLAVEDBNAME=repltestdb
    MASTERHOST=172.16.3.211
    SLAVEHOST=172.16.3.173
    MASTERPORT=5432
    SLAVEPORT=5432
    MASTERDBA=postgres
    SLAVEDBA=postgres
    PSQL=/usr/local/pgsql/bin/psql
    CREATEDB=/usr/local/pgsql/bin/createdb
    CREATELANG=/usr/local/pgsql/bin/createlang
    CREATEUSER=/usr/local/pgsql/bin/createuser
    PGDUMP=/usr/local/pgsql/bin/pg_dump

    export REPLICATIONUSER CLUSTERNAME MASTERDBNAME SLAVEDBNAME MASTERHOST SLAVEHOST PSQL CREATEDB CREATELANG CREATEUSER PGDUMP MASTERPORT SLAVEPORT MASTERDBA SLAVEDBA

    As you can see here, my postgresql is installed in /usr/local/pgsql. I have defined the IP addresses & ports of master and slave servers. I have used the superuser postgres for replication. And i have defined the master and slave databases to be used for replication. You can replicate between databases with different names on master and slave – just change the names in all the scripts.

  • Create database on master & slave
    On master run
    /usr/local/pgsql/bin/createdb -O $REPLICATIONUSER -h $MASTERHOST -p $MASTERPORT $MASTERDBNAME
    On slave run
    /usr/local/pgsql/bin/createdb -O $REPLICATIONUSER -h $SLAVEHOST -p $SLAVEPORT $SLAVEDBNAME
  • Since slony-I depends on triggers for replication, you will need to install the plsql procedural language on master to generate and run triggers & stored procedures for pushing data to slave.
    /usr/local/pgsql/bin/createlang -h $MASTERHOST -p $MASTERPORT plpgsql $MASTERDBNAME
  • Put some tables in the $MASTERDBNAME on master, which you want to replicate. And port the tables to slave. It has to be done manually.

    Dump the tables on master
    /usr/local/pgsql/bin/pg_dump -s -U $MASTERDBA -h $MASTERHOST -p $MASTERPORT $MASTERDBNAME > replmaster.sql

    Import the tables on slave
    /usr/local/pgsql/bin/psql -U $SLAVEDBA -h $SLAVEHOST -p $SLAVEPORT $SLAVEDBNAME < replmaster.sql

  • And now configure the databases for replication. When you install Slony-I, it puts two binaries slonik and slon in the pgsql/bin directory. Slonik is the tool which is used for creating configuration tables, stored procedures and triggers. All we need to do is create a configuration file to pass it to the slonik tool. Here i am assuming that there are two tables which need to be replicated – parent & child.

    vim replconfig.cnf
    # define the namespace the replication system uses in our example it is
    # replcluster
    cluster name = replcluster;
    # admin conninfo’s are used by slonik to connect to the nodes one for each
    # node on each side of the cluster, the syntax is that of PQconnectdb in
    # the C-API
    node 1 admin conninfo = ‘dbname=repltestdb host=172.16.3.211 port=5432 user=postgres’;
    node 2 admin conninfo = ‘dbname=repltestdb host=172.16.3.173 port=5432 user=postgres’;
    # init the first node. Its id MUST be 1. This creates the schema
    # _$CLUSTERNAME containing all replication system specific database
    # objects.
    init cluster ( id=1, comment = ‘Master Node’);
    # Add unique keys to table that do not have one.
    # This command adds a bigint column named “_Slony-I_$CLUSTERNAME_rowID” to the table which will have a default value of nextval(‘_$CLUSTERNAME.s1_rowid_seq’) and have UNIQUE & NOT NULL constraints applied on it.
    # table add key (node id = 1, fully qualified name = ‘table_name’);
    # Slony-I organizes tables into sets. The smallest unit a node can
    # subscribe is a set.
    # you need to have a set add table() for each table you wish to replicate
    create set (id=1, origin=1, comment=’parent child table’)
    set add table (set id=1, origin=1, id=1, fully qualified name = ‘public.parent’, comment=’parent table’);
    set add table (set id=1, origin=1, id=2, fully qualified name = ‘public.child’, comment=’child table’);
    # Create the second node (the slave) tell the 2 nodes how to connect to
    # each other and how they should listen for events.
    store node (id=2, comment = ‘Slave node’);
    store path (server = 1, client = 2, conninfo=’dbname=repltestdb host=172.16.3.211 port=5432 user=postgres’);
    store path (server = 2, client = 1, conninfo=’dbname=repltestdb host=172.16.3.173 port=5432 user=postgres’);
    store listen (origin=1, provider = 1, receiver =2);
    store listen (origin=2, provider = 2, receiver =1);

    Pass the config file to slonik for creating required triggers & config tables.

    /usr/local/pgsql/bin/slonik replconfig.cnf

  • Lets start the replication daemons on master & slave

    On master run
    /usr/local/pgsql/bin/slon $CLUSTERNAME “dbname=$MASTERDBNAME user=$MASTERDBA host=$MASTERHOST port=$MASTERPORT” > slon.log &

    On slave run
    /usr/local/pgsql/bin/slon $CLUSTERNAME “dbname=$SLAVEDBNAME user=$SLAVEDBA host=$SLAVEHOST port=$SLAVEPORT” > slon.log &

    Check out the output in slon.log files

  • Now everything is setup and from the slon.log files on master and slave you can see that both the servers are trying to sync with each other. But still replication is not on way. To start replication we need to make the slave subscribe to the master. Here is the required config file for doing this

    startrepl.cnf
    # This defines which namespace the replication system uses
    cluster name = replcluster;
    # connection info for slonik to connect to master & slave
    node 1 admin conninfo = ‘dbname=repltestdb host=172.16.3.211 port=5432 user=postgres’;
    node 2 admin conninfo = ‘dbname=repltestdb host=172.16.3.173 port=5432 user=postgres’;
    # Node 2 subscribes set 1
    subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);

    Passing this file to slonik will do the trick and replication would start happening.

    /usr/local/pgsql/bin/slonik startrepl.cnf

Now simply make some inserts, updates and deletes on the master and check out whether they are happening on the slave as well. Officially, since replication is on full swing all changes in master tables should be replicated on the slave.

Please note that new tables & changes to table structures wont be replicated automatically. So whenever a new table is created or an existing table is altered the changes has to be manually propagated to slave and the scripts need to be run to make appropriate changes in the triggers and config tables.

Another important thing to note is that postgresql on master and slave should be able to communicate with both the ip addresses. For this add the ip addresses in the pgsql/data/pg_hba.conf.

For the able replication i had added the lines
host all all 172.16.3.211/32 trust
host all all 172.16.3.173/32 trust
to the pg_hba.conf file in both master & slave.

An auto ride in Delhi

I have been coming back from baroda and it was around 7 AM in the morning. The train was already 2 hours late and would stop at new delhi railway station in some time. I was ready with my backpack. As soon as the train stopped, i got down and proceeded to the exit (Ajmeri gate side) to catch an auto for noida (my residence).

As soon as i came to the road outside the station and almost half a kilometer from the auto stand, as usual i saw lots of people who were asking me for auto and taxi. The taxi agents came first – yes i called them agents cause they try to catch you first and take a commission from the auto/taxi. The quotes i got for noida was 250/-. I was like WTF!!! I said no to all of them and came out and approached the auto stand. There were tons of auto but only a few were going to noida and their fare was 200/- min. Officially the fare is around 120/-. Then i saw this man who was shouting “noida, noida”, and i asked him for a quote. He told me 150/-, I bargained and finally we froze at 130/- I went to his auto and saw that it was already full. There were two good looking ladies in the back seat and i was supposed to ride with the driver. The “ladies” were paying 250/- for the trip to noida. So the auto was going to make 380/- for this ride. Ghosh!!!

I had avoided the prepaid booth cause of the long queue over there. As we rode out of the stand a white clothed police man stopped us and asked the auto wala for his prepaid receipt. Usually the prepaid booth issues a receipt which i have to give to the auto at the destination, and the auto wala presents this receipt to the prepaid booth to collect his payment. Since this auto was a totally self arranged one, we did not have any receipt. So, instead of issueing a receipt for us (convenience for passenger), we were asked to get down and another couple who had a receipt were asked to board the auto. I was again like WTF!!!. After half an hour of bargain, i get an auto and the police man simply hands it over to someone else.

Well, so i thought, why not get a receipt from the prepaid booth. I stood in the queue for 15 minutes and got a receipt for 140/- to a place known as ashok nagar which is on the border of noida & delhi. If you would ask, why not into noida to my destination, i would say that the prepaid booth does not provide service to U.P. It was just that i remembered the name of the place and got the receipt. Now, the police man who was assigning passengers to autos were not visible. I stood for some time and looked around and i spotted a crowd. The crowd would move to an auto and the auto would depart with passengers, then the crowd would move to the next auto. I went near “the crowd”, and saw that it was a junior policeman surrounded with lots and lots of passengers all havine prepaid receipts. Everyone was trying to put his receipt on priority and the policeman was enjoying the attention he was getting. He would go to an auto, assign it a passenger, scold the auto-wala and then talk on his mobile and then move to the next auto – all at his liesure. He had to do this whole day, and it seemed he was enjoying his work.

Finally, the policeman saw my receipt and assigned me an auto. I jumped in, glad that finally i am going to get home. It was almost 8:30 – 1.5 hours to get an auto. We departed. But my adventure does not end here. As soon as we were out of the auto stand and on the road, the auto wala turned his head to me and asked me where was i going.

Let me try to paint a picture of the auto wala. He is a male, 40ish, with black and white hair. He is very untidy and smelly. It seems that he has not bathed in few days. He is smoking a bidi (an indian version of ciggerate) from the side of his mouth. And he has an attitude – “i am the king of the road”.

So, i told him that i have to go to sector 12 in noida, but he can leave me at ashok nagar and i will take a manual rickshaw from there, if he wants. the auto wala says from the side of his mouth (he has bidi on the other side). He will drop me at my place – sector 12 noida, if i pay him 100/- extra. I was a bit shocked. After spending 1.5 hours in getting an auto i am still at the same price. I told him that i will pay him 40/- extra. After everything, we finally bargained a deal at 200/-. And, i finally relaxed on the back of the auto.

No sooner had i done this that, a bus overtook the auto from the wrong side and at a very close distance. I jumped up. The auto wala was cool. I asked him whether he had given indicator or not. And he showed me his foot which was sticking out in the direction where we were about to turn. I said “wow…”.

Now, i began to notice the way the auto was being driven. We were exactly in the center of the road, in such a manner that no car can overtake us from the right side. If a vehicle has to overtake us, it has to do it from the wrong side. There were lots of honking behind us. A car was getting frustrated. We were moving at a constant speed of 35, and the car was trying to overtake us from the right side, but the auto would not give way to it. Finally the car overtook us from the wrong side. The car-driver glared at the auto-driver but the auto driver did not look at him.

We were half way and suddenly the auto started struggling. It coughed and died. I got down and pushed the auto to the side of the road. The auto-driver got out a bottle and put in some oil in his tank. I asked him what is this oil, and he told me it is kerosene. The auto was started and i could see the whitish black and smelly smoke from its exhaust. I got back in and we continued our journey. Now we were climbing a flyover and the auto’s speed dropped to 15 kmph. It was not able to pull me up but the auto driver was very persistent, so we finally made it to the top.

Again we were in the middle of the road and some car was trying to overtake us. I looked to my side and i could see another auto just next to mine. Both autos were at their top speed i.e. 20 kmph and both were trying to race. There was a smile on my auto-driver’s face because he was an inch ahead of the other auto. Behind, i could see cars and busses trying to adjust to this low speed and honking, so that they would be given side for overtaking. The flyover ended and so did the race (i think) and my auto driver won.

As soon as we entered noida, we started jumping red lights. Delhi traffic police are a bit strict and you cant jump red ligts there, but in noida, there are hardly any police at the red lights. Anyways, after saving his auto from 2-3 near collisions, we finally turned and parked the auto at my place. I handed over the prepaid receipt and another 100/- Rs. And the auto driver got out 40/- and paid me back. So, after a delay of 2.5 hours and after spending 200/- rs, i was finally home.