Londiste Replication with PostgreSQL 9.0

Londiste, Asynchronous Master/Slave replication tool developed by Skytools. Its very simple and user-friendly created like Slony. Core logic behind Londiste or Slony is Remote Triggering. Whereas londiste follows events queuing model which is not their in Slony – I.

Overview on Skytools:
Skytools is a Python-based application, it comes with a bundle of three things PgQ,Londiste & Walmgr and it also requires the Python-Postgres driver ‘psycopg2’.

• PGQ : Its queue mechanism built with pl/pgsql with phython framework on top of it.
• Londiste: A replication tool written in Phyton using PgQ as events transporter.
• Walmgr : Creates a WAL archiving setup.

Am not going to describe much here regarding londiste replication daemon process etc., because you can find the best tutorial regarding Skytools(PgQ/Londiste/WalMgr) in this link http://skytools.projects.postgresql.org/doc/.

Basically, my demo include how to proceed with Londiste replication with PostgreSQL 9.0 along with installation steps. I say, Skytools documentation and PostgreSQL Wiki (http://wiki.postgresql.org/wiki/Londiste_Tutorial) is more then anything to play around with Londiste replication.

Pre-Requisites with Download Links :

• PostgreSQL – PostgreSQL 9.0 http://www.enterprisedb.com/products-services-training/pgdownload
• skytools – skytools-2.1.12.tar.gz http://pgfoundry.org/frs/download.php/2872/skytools-2.1.12.tar.gz
• psycopg2 – psycopg2-2.4.2.tar.gz  http://initd.org/psycopg/tarballs/PSYCOPG-2-4/psycopg2-2.4.2.tar.gz

My Demo includes following :-

OS                     : RHEL 6 32 bit
DB version             : PostgreSQL 9.0
Two Clusters & Database: londiste_provider on 5432,Londiste_subscriber on 5433
Table                  : One Table (ltest)
Location of .ini file  : /opt/skytools-2.1.12/scripts
Location of Skytools   : /opt/skytools-2.1.12
Location of PG 9.0     : /opt/PostgreSQL/9.0/

As its simple demo with one table, I have tried with RHEL 6 32bit/PostgreSQL 9.0 with two clusters in my local box. You would need to tweak it as per the actual requirements.

Note: Before moving forward with setup, I would like to remind that all source installations must be as root user and after installation those directories should own Postgres user permissions.

Step 1.
Install PostgreSQL 9.0 and create two clusters with INITDB command and make sure they run on 5432 & 5433 each. (Remember, its a old fact that with INITDB command pg_log directory will not be created under Data_directory you need to create it explicitly.)

Step 2.
Install skytools by downloading from the above link. Its best practice to keep all sources in one common standard location. I used ‘/usr/local/src’ and skytools under ‘/opt/’. Now configure skytools with PostgreSQL 9.0 ‘pg_config’.

# tar -xvf skytools-2.1.12.tar.gz
# cd /usr/local/src/skytools-2.1.12
# ./configure --prefix=/opt/skytools-2.1.12 --with-pgconfig=/opt/PostgreSQL/9.0/bin/pg_config
# make 
# make install

Note: After the installation you will see two important contrib modules(pgq & londiste) under PostgreSQL contrib location. Basically, these two contrib’s gives you the functionality of londiste replication.

# cd /opt/PostgreSQL/9.0/share/postgresql/contrib
# ll lond*
-rw-r--r--. 1 root root 29771 Jan 11 13:24 londiste.sql
-rw-r--r--. 1 root root 27511 Jan 11 13:24 londiste.upgrade.sql

# ll pgq*
-rw-r--r--. 1 root root  4613 Jan 11 13:24 pgq_ext.sql
-rw-r--r--. 1 root root  1170 Jan 11 13:24 pgq_lowlevel.sql
-rw-r--r--. 1 root root 69798 Jan 11 13:24 pgq.sql
-rw-r--r--. 1 root root  3940 Jan 11 13:24 pgq_triggers.sql
-rw-r--r--. 1 root root 54182 Jan 11 13:24 pgq.upgrade.sql

Step 3.
Install psycopg2, its a phyton-postgres driver which is necessary for skytools. Sometime these driver’s wont come with python, so here are the installation steps.

# tar -xvf psycopg2-2.4.2.tar.gz
# cd psycopg2-2.4.2
# python setup.py install --prefix=/usr/local
# python setup.py build_ext --pg-config /opt/PostgreSQL/9.0/bin/pg_config

Step 4.
Give ownership of Postgres to skytools and postgres installation location. This makes sure that all files/executables are with Postgres User permissions.

# chown -R postgres:postgres /opt/skytools-2.1.12 
# chown -R postgres:postgres /opt/PostgreSQL/9.0/

Step 5.
Set the LD_LIBRARY_PATH & PYTHONPATH and start the two newly created clusters. You can place them in .bash_profile of postgres user as permanent solution.

$export PYTHONPATH=/opt/skytools-2.1.12/lib/python2.6/site-packages/
$export LD_LIBRARY_PATH=/opt/PostgreSQL/9.0/lib:/usr/lib:/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE:
or 
$ vi .bash_profile 
export PYTHONPATH=/opt/skytools-2.1.12/lib/python2.6/site-packages/
export LD_LIBRARY_PATH=/opt/PostgreSQL/9.0/lib:/usr/lib:/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE:
:wq
$ . .bash_profile (execute to take effect of new settings)

Now Start the two cluster

$ pg_ctl -o "-p 5432" -D /opt/PostgreSQL/9.0/data start
$ pg_ctl -o "-p 5433" -D /opt/PostgreSQL/9.0/data_1 start

Step 6.
Create two databases, londiste_provider in 5432 and londiste_subscriber in 5433. Create one table with primary key name ‘ltest’ in two databases and INSERT some data in londiste_provider (ltest) table and later completion of replication setup you should see those INSERT data in londiste_subscriber side.

You may not need CRETAE TABLE on slave side, instead you can use structure dump/restore by using pg_dump/pg_restore, if you have many tables.

On 5432
psql -p 5432 -c "create database londiste_provider;"
psql -p 5432 londiste_provider
londiste_provider=# create table ltest(id int primary key);
londiste_provider=# insert into ltest VALUES (generate_series(1,10));
INSERT 0 10

On 5433
psql -p 5433 -c "create database londiste_subscriber;"
psql -p 5433 londiste_subscriber
londiste_subscriber=# create table ltest(id int primary key);

Step 7.
Create two .ini files one for londiste(londiste.ini) and another for PgQ ticker(pgq_ticker.ini). You can also find the sample .ini files from base installation of skytools. Eg:- “/opt/skytools-2.1.12/share/doc/skytools/conf” location.

Step 8.
Create two directories for log’s and PID’s files and point them in the parameters of londiste.ini and pgq_ticker.ini.

$ cd /opt/PostgreSQL/9.0
$ mkdir log pid

Step 9.
Start the replication with .ini files, firstly install londiste on provider and subscriber and then start the ticker (PgQ) for replicating the tables.

Install londiste on provider and subscriber with below commands one by one:

$ cd /opt/skytools-2.1.12/bin
$ ./londiste.py ../scripts/londiste.ini provider install
2012-01-12 14:56:03,667 11073 INFO plpgsql is installed
2012-01-12 14:56:03,674 11073 INFO txid_current_snapshot is installed
2012-01-12 14:56:03,675 11073 INFO Installing pgq
2012-01-12 14:56:03,676 11073 INFO   Reading from /opt/skytools-2.1.12/share/skytools/pgq.sql
2012-01-12 14:56:03,816 11073 INFO Installing londiste
2012-01-12 14:56:03,816 11073 INFO   Reading from /opt/skytools-2.1.12/share/skytools/londiste.sql

-bash-4.1$ ./londiste.py ../scripts/londiste.ini subscriber install
2012-01-12 14:56:17,871 11081 INFO plpgsql is installed
2012-01-12 14:56:17,872 11081 INFO Installing londiste
2012-01-12 14:56:17,873 11081 INFO   Reading from /opt/skytools-2.1.12/share/skytools/londiste.sql

-->Now, Install PqQ and start ticker with .ini file. 

-bash-4.1$ ./pgqadm.py ../scripts/pgqadm.ini install
2012-01-11 16:45:03,219 6348 INFO plpgsql is installed
2012-01-11 16:45:03,225 6348 INFO txid_current_snapshot is installed
2012-01-11 16:45:03,228 6348 INFO pgq is installed 

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

-->Add the table to provider & subscriber to replicate.

-bash-4.1$ ./londiste.py ../scripts/londiste.ini provider add ltest
2012-01-12 15:03:39,583 11139 INFO Adding public.ltest

-bash-4.1$ ./londiste.py ../scripts/londiste.ini subscriber add ltest
2012-01-12 15:03:47,367 11146 INFO Checking public.ltest
2012-01-12 15:03:47,384 11146 INFO Adding public.ltest

After adding start the replication of the table.

-bash-4.1$ ./londiste.py ../ scripts/londiste.ini replay -d

Note: "-d" option is to run the londiste/PgQ daemons in background. 

Here complete the replication setup. Now you should see the “ltest” table data on Slave Side(i.e. on 5433 port).

Step 10.
Now lets understand what all happend in the background to table/logs/pids/data etc., Lets see one by one.

Logs Information:

Table Structure after replication:

Event Queue Status
Replication status can be checked with pgq utility as below:-

-bash-4.1$ ./pgqadm.py ../scripts/pgqadm.ini status
Postgres version: 9.0.1   PgQ version: 2.1.8

Event queue                                    Rotation        Ticker   TLag
------------------------------------------------------------------------------
londiste.replica                                3/7200s    500/3s/60s     6s
------------------------------------------------------------------------------

Consumer                                                       Lag  LastSeen
------------------------------------------------------------------------------
londiste.replica:
  myfirstlondiste                                               6s        6s
------------------------------------------------------------------------------

Note: There are very good options with Londiste & PGQ utilities to do R & D.
Hoping you all have a successful Londiste replication setup. Please do post your comments those are highly appreciated. See you all soon with some more postings.

–Raghav