Skip to main content

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

Comments

  1. Very Helpful! Thanks

    ReplyDelete
  2. Hi Dinesh, can this be used to replicate a non Amazon RDS Postgres database to RDS?

    ReplyDelete
    Replies
    1. Hi.
      Londisite require host and port connections. If they are able to connect from Londisite, I hope this works.

      Delete

Post a Comment

Popular posts from this blog

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads


2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.

3. Install the pgpool by executing the below commands:

./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool  …

pgBucket - A new concurrent job scheduler

Hi All,

I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database.

I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ],


pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals.

Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works.


Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way.
Features are: OS/DB jobsCron style sytaxOnline job modificationsRequired cli options

N-Node Mutlimaster Replication With Bucardo...!

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL.

We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo.

Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..?

As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.}

Please follow the below steps to set up the 1 - 1 multi master replication.

1. Follow the below steps to get all the pre-requisites for the Bucardo.

yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here. tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For this, download…