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

Parallel Operations With pl/pgSQL

Hi, I am pretty sure that, there will be a right heading for this post. For now, i am going with this. If you could suggest me proper heading, i will update it :-) OK. let me explain the situation. Then will let you know what i am trying to do here, and how i did it. Situation here is, We have a table, which we need to run update on “R” no.of records. The update query is using some joins to get the desired result, and do update the table.  To process these “R” no.of records, it is taking “H” no.of hours. That too, it’s giving load on the production server. So, we planned to run this UPDATE as batch process.  Per a batch process, we took “N” no.or records. To process this batch UPDATE, it is taking “S” no.of seconds. With the above batch process, production server is pretty stable, and doing great. So, we planned to run these Batch updates parallel.  I mean, “K” sessions, running different record UPDATEs. Of-course, we can also increase the Batch size here.  But

How To Send E-Mail From PostgreSQL

Hi , If you want to send E-Mails from PostgreSQL, then use the below Python 3.2 Script as below. I have used ActivePython 3.2 with PostgreSQL 9.1 for sending E-Mails from PostgreSQL. If you want to configure the Python 3.2 with PostgreSQL 9.1 then, please refer the below steps. http://manojadinesh.blogspot.in/2012/06/fatal-python-error-pyinitialize-unable.html Once, your Python 3.2 successful then follow the below steps to send an e-mail. Step 1 ===== postgres=# CREATE OR REPLACE FUNCTION public.send_email(_from Text,_password Text,smtp Text,port INT,receiver text, subject text, send_message text) RETURNS TEXT  LANGUAGE plpython3u AS $function$ import smtplib sender = _from receivers = receiver message = ("From: %s\nTo: %s\nSubject: %s\n\n %s"  % (_from,receiver,subject,send_message)) try:   smtpObj = smtplib.SMTP(smtp,port)   smtpObj.starttls()   smtpObj.login(_from, _password)   smtpObj.sendmail(sender, receivers,message)   print ('Successf

::Pipelined in Oracle as well in PostgreSQL::

Pipelined Table Functions:- [ORACLE] =========================== If you want to return multiple rows to the calling environment, then piplined table functions is prefred. It will increase the dbperformance as well. Ex:- Step 1: ----------- CREATE TABLE EMP(EMPNO INT,ENAME VARCHAR2(10),SAL INT); Step 2: ----------- Insert sample data. Step 3: ----------- Create an object for the row type casting. CREATE OR REPLACE TYPE emp_row AS OBJECT ( empno INT, ename VARCHAR2(20), SAL INT ); Step 4: ----------- Create a Return Type for the pipelined function. CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_row; Step 5: ----------- CREATE OR REPLACE FUNCTION emp_pipe_function RETURN emp_table_type PIPELINED IS BEGIN FOR rec in (select * from emp) LOOP PIPE ROW (emp_row(rec.empno,rec.ename,rec.sal)); END LOOP; RETURN; END; Step 6: ---------- SQL> select * from table(emp_pipe_function); EMPNO ENAME SAL ---------- ----