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

pgBucket v1.0 is ready

pgBucket v1.0 pgBucket v1.0 (concurrent job scheduler for PostgreSQL) is released. This version is more stable and fixed the issues which was observed in the previous beta releases.
Highlights of this tool are Schedule OS/DB level jobsCron style syntax {Schedule up to seconds}On fly job modificationsInstant daemon status by retrieving live job queue, job hashEnough cli options to deal with all the configured/scheduled job Here is the URL for the pgBucket build/usage instructions. https://bitbucket.org/dineshopenscg/pgbucket
I hope this tool will be helpful for the PostgreSQL users to get things done in the scheduled time. Note: This tool requires c++11{gcc version >= 4.9.3} to compile.
--Dinesh