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'.
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 :
My Demo includes following :-
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'.
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.
Give ownership of Postgres to skytools and postgres installation location. This makes sure that all files/executables are with Postgres User permissions.
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.
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.
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.
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:
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:-
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
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 installNote: 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.sqlStep 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_configStep 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 startStep 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 pidStep 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.ltestAfter 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.
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
