Caveat
This is a very simple and straightforward setup. It is a setup of londiste where I only install londiste on one machine, the provider (or master) and not the subscriber (or slave). I have checked that this setup is fine , but it would make less sense once you have more then one subscriber. Most of the information here was obtained from the excellent Londiste tutorial . Please read that tutorial as well. This is purely an elaboration.
Assumptions
1. Master server – with database “db”.
2. Slave server - with database “db_slave”.
3. We will be installing Londiste/Skytools only on the master server.
4. The root londiste folder will be ~/londiste, where all config files will be stored in ~/londiste/etc , log files will be stored in ~/londiste/log and PID files will be stored in ~/londiste/pid. It is assumed that all these directories have already been created.
Overview
Londiste is actually a component of the Skytools package that contains some postgres module, a couple of python modules, and python based admin scripts.
With regards to Londiste there are 2 main components to be setup
1. Ticker – this is to be installed into the master “db”.
2. Londiste – replication engine – one instance run for every slave database.
It is recommended that the ticker be run on the master database and that one replication engine instance be run for every one slave database, on the slave database. But since this setup is a simple one master, one slave setup, I am only going to install everything on the master database.
Installation – dependencies
The following is based on Debian/Ubuntu – sorry Fedora/RedHat/Centos folk.
Apart from the build tools (which you should be able to install easily via a sudo apt-get install build-essential), the following dependencies are required as well:
sudo apt-get install postgresql-server-dev-8.3
sudo apt-get install python-psycopg2
sudo apt-get install python-dev
Download the skytools tar package from http://pgfoundry.org/projects/skytools/
Installation – Installing from a package by creating the deb from the source package
To build the deb install the following dependecies.
sudo apt-get install yada pbuilder devscripts fakeroot
Then, untar the skytools package and from within it do the following
tar -xvzf ./skytools-2.1.9.tar.gz
cd ./skytools-2.1.9
make deb83
There is also a “make deb84″ to build a deb for postgresql8.4
You will end up with 2 deb packages for your architecture in the same directory that your skytools directory resides (The skytools-2.1.9 parent dir). For example since my machine was a 64 bit machine the packages generated by the make process was
skytools_2.1.9_amd64.deb
skytools-modules-8.3_2.1.9_amd64.deb
Install both these packages
sudo dpkg -i skytools-modules-8.3_2.1.9_amd64.deb ytools_2.1.9_amd64.deb
Installation - From source
sudo make install
sudo python setup.py install
Setup of slave database
Dump the master database schema only and get it loaded into the slave. You will need to load the schema (just the table structure without the data itself) of the master into the slave database. You can get this by doing the following. Load the result into an empty slave database
pg_dump -Upostgres db -s –schema=public> db_schema.sql
Configuration and installation of ticker on master
The first component that we will be setting up is the ticker. From what I understand you will setup one ticker for every database that you plan to replicate.
Create a config file ~/londiste/etc/ticker.ini as follows. Edit and replace with your own settings as necessary.
[pgqadm]
job_name = db_ticker
db = dbname=db user=postgres password=dbpass #this is your standard psycopg2 connection string
# how often to run maintenance [seconds]
maint_delay = 600
# how often to check for activity [seconds]
loop_delay = 0.1
logfile = ~/londiste/log/%(job_name)s.log
pidfile = ~/londiste/pid/%(job_name)s.pid
After doing that run
pgqadm.py ~/londiste/etc/ticker.ini install
Caveat for python-2.6 - You will need to change the name of the variable "as" on line 576 of the
file /usr/local/lib/python2.6/dist-packages/pgq/status.py - "as" is a reserved word in
Python2.6. I belive this has been rectified with version 2.1.10 that has just come out.
Configuration and installation of londiste
In the tutorial (http://wiki.postgresql.org/wiki/Londiste_Tutorial) it is recommended that the replication daemon be run on the subscriber. Hence you would normally be setting this bit up on the subscriber machine that would also have londiste installed. You would end up with one config file for every subscriber. However this guide assumes that everything is being run from one machine.
Create a config file ~/londiste/etc/p-to-s.ini as follows. Edit and replace with your own settings as necessary.
[londiste]
job_name = p_to_s # this needs to be globally unique, multiple slaves – would requires different names
provider_db = host=localhost dbname=db user=postgres password=dbpass
subscriber_db = host=slavehost dbname=db_slave user=postgres password=dbpass #this would be different for every slave
# it will be used as sql ident so no dots/spaces
pgq_queue_name = londiste.replica # common queue name for common provider – all subscriber for the same provider hence should use the same value
logfile = ~/londiste/log/%(job_name)s.log
pidfile = ~/londiste/pid/%(job_name)s.pid
Upon doing this we install the londiste component to both the provider and subscriber databases as follows
londiste.py ~/londiste/etc/p-to-s.ini provider install
londiste.py ~/londiste/etc/p-to-s.ini subscriber install
Keep in mind that for our setup we only have one subscriber. If you have more then one you would need to repeat the installation for the rest of the subscribers.
Launch replication
Once the relevant londiste installation is complete we begin the database replication as follows:
londiste.py ~/londiste/etc/p-to-s.ini replay -d
You will need to ensure that the replication has started before proceeding to the next steps. Check the logs. Again, if you had more then one subscriber you will run the replay for each subscriber.
Adding tables for replication
Once the replication is running, you will need to add the tables and sequences that you want replicated in the provider. When it comes to the subscriber you can pick and choose which tables and sequences you would like to replicate as well. For this example I am going to assume that you would want to replicate all the tables and sequences and instead of defining the list of tables individually I am going to do it for all tables and sequences right away.
To add all the tables and sequences to be replicated on the provider, do the following
londiste.py ~/londiste/etc/p-to-s.ini provider add –all
londiste.py ~/londiste/etc/p-to-s.ini provider add-seq –all
To add all the tables and sequences to be replicated on the subscriber, do the following
londiste.py ~/londiste/etc/p-to-s.ini provider tables | xargs londiste.py ~/londiste/etc/p-to-s.ini subscriber add
londiste.py ~/londiste/etc/p-to-s.ini provider seqs | xargs londiste.py ~/londiste/etc/p-to-s.ini subscriber add-seq
By this stage the replication process should start of started. In the londiste log fil, as you add tables you should see entries as follows:
2009-07-28 17:45:37,914 22880 INFO Adding public.table1
You should also see log entries such as
2009-07-28 17:46:15,096 22886 INFO Starting full copy of public.table1
that would indicate the initial data transfer has started.
To check that the replication is really running you can use the compare command that will compare the provider row count and subscriber row count for the replicated tables, as follows:
londiste.py ~/londiste/etc/p-to-s.ini compare
Summary
That’s it! Hope this was useful for someone. Londiste is bloody easy and to get started with and works like a charm. I have been using very much the configuration that I have described above for one of our large database that gets frequent inserts and updates and I have not had a single problem since installing it slightly over 2 months ago. The manual management of column changes in the database can be a bit tedious, but I am looking to Londiste 3.0 to sort that out.
References
Londiste tutorial – http://wiki.postgresql.org/wiki/Londiste_Tutorial
Really helpul and frinedly mailing list – http://pgfoundry.org/mailman/listinfo/skytools-users
Londiste presentation from pgcon – Useful explanation and diagrams of both Londiste 2.0 and 3.0 – http://www.pgcon.org/2009/schedule/attachments/101_Londiste3.pdf
Appendix
How to correctly temporarily disable and resume replication in londiste 2.1
Proper way to rotate logs for replay and ticker daemons?
