Skytools - Londiste Replication

PGQ-Londiste Replication
===================
We all know that the Skytools are popular PostgreSQL Replication tools which have been developed in C/Python and PL/PGSQL. Londiste is the replication which has been implemented on top of PGQ tool which is a snapshot based queuing mechanism. Londiste replication is a Consumer of PGQ which takes the all modifications from PGQ.

How to setup PGQ and Londiste Replication
---------------------------------------------------------

PGQ
------
PGQ is a queue mechanism which gathers all the transactions/modifications from the provider and keep them ready to consumers{Londiste Replication Demon}.

Step 1
----------
Download the Skytool from the below link.

http://pgfoundry.org/frs/download.php/3232/skytools-2.1.13.tar.gz

tar -zxvf skytools-2.1.13.tar.gz

Step 2
---------
Configure make and make install.

[root@localhost skytools-2.1.13]# ./configure  --prefix=/opt/PostgreSQL/9.2/Sky --with-pgconfig=/opt/PostgreSQL/9.2/bin/pg_config

[root@localhost skytools-2.1.13]# make

[root@localhost skytools-2.1.13]# make install

Step 3
------
PYTHONPATH

export PYTHONPATH=/opt/PostgreSQL/9.2/Sky/lib64/python2.6/site-packages/

OR

Copy all the contents of "/opt/PostgreSQL/9.2/Sky/lib64/python2.6/site-packages/" to "/usr/lib64/python2.6/site-packages/".

Step 4
------
Create ticker.ini file which is responsible to create the ticks{Batches}.

-bash-4.1$ more Sky/etc/ticker.ini 

[pgqadm]

job_name = myticker_name

db = dbname =  producer port = 5432 host = localhost 

maint_delay = 1

loop_delay = 0.1

logfile = /tmp/%(job_name)s.log

pidfile = /tmp/%(job_name)s.pid

Step 5
------
Starting PGQ ticker Demon.

-bash-4.1$ pwd

/opt/PostgreSQL/9.2/Sky/bin

-bash-4.1$ ./pgqadm.py ../etc/ticker.ini install

2012-11-06 18:39:45,973 9599 INFO plpgsql is installed

2012-11-06 18:39:45,974 9599 INFO txid_current_snapshot is installed

2012-11-06 18:39:45,975 9599 INFO Installing pgq

2012-11-06 18:39:46,010 9599 INFO   Reading from /opt/PostgreSQL/9.2/Sky/share/skytools/pgq.sql

The above step creates all it's required PGQ catalog in the database producer.

-bash-4.1$ ./pgqadm.py ../etc/ticker.ini ticker -d

Step 6
------
Create Provider_Subscriber details in a file as below.

-bash-4.1$ more Sky/etc/Producer_Consumer.ini 

[londiste]

job_name = Londiste_Job 

provider_db = dbname=producer port=5432 host=127.0.0.1

subscriber_db = dbname=consumer port=5432 host=127.0.0.1

pgq_queue_name = testing

logfile = /tmp/%(job_name)s.log

pidfile = /tmp/%(job_name)s.pid

Step 7
------
Install the Londiste catalogs into Provider database.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini provider install

2012-11-06 18:41:40,761 9726 INFO plpgsql is installed

2012-11-06 18:41:40,772 9726 INFO txid_current_snapshot is installed

2012-11-06 18:41:40,772 9726 INFO pgq is installed

2012-11-06 18:41:40,773 9726 INFO Installing londiste

2012-11-06 18:41:40,773 9726 INFO   Reading from /opt/PostgreSQL/9.2/Sky/share/skytools/londiste.sql

Install the Londiste catalogs into Subscriber database.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini subscriber install

2012-11-06 18:41:53,003 9742 INFO plpgsql is installed

2012-11-06 18:41:53,003 9742 INFO Installing londiste

2012-11-06 18:41:53,004 9742 INFO   Reading from /opt/PostgreSQL/9.2/Sky/share/skytools/londiste.sql

Step 8
------
Start the Londiste Replication Demon.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini replay -d

Step 9
------
Create a sample PRIMARY KEY table in Provider and Receiver

CREATE TABLE TEST(T INT PRIMARY KEY);



producer=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | test | table | postgres

(1 row)

producer=# insert into test values(generate_series(1,1000));

INSERT 0 1000

consumer=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | test | table | postgres

(1 row)

Step 10
----------
Add this table to Londiste Replication.

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini provider add public.test

2012-11-06 18:44:37,767 9914 INFO Adding public.test

-bash-4.1$ ./londiste.py ../etc/Producer_Consumer.ini subscriber add public.test

2012-11-06 18:44:44,554 9927 INFO Checking public.test

2012-11-06 18:44:44,561 9927 INFO Adding public.test

Step 11
-----------
Check the replication sync..

producer-# SELECT * FROM pgq.get_consumer_info();

 queue_name | consumer_name |       lag       |    last_seen    

------------+---------------+-----------------+-----------------

 testing    | Londiste_Job  | 00:00:27.837503 | 00:00:27.128354

(1 rows)

producer=# insert into test values(generate_series(-100,-10));

INSERT 0 91

producer=# \c consumer 

You are now connected to database "consumer" as user "postgres".

consumer=# select count(*) from test;

 count 

-------

  1091

(1 row)

దినేష్ కుమార్ 

Dinesh Kumar