We recently had to migrate our multiple PostgreSQL databases between cloud providers. We wanted to keep downtime to an absolute minimum. To achieve this we created replicas of the live databases on a new database server. Then to switch to the new database all we had to do was momentarily take a server offline while we pointed it to the new database.
Londiste is a part of Skytools 3. It is an “asynchronous master/slave replication system, built atop PGQ” – source. One of the benefits of Londiste over the streaming replication that’s in the core of PostgreSQL is that Londiste can replicate a single database or a table from a database. Streaming replication will create an exact copy of the database server. Londiste provides more granularity for replication which makes it ideal for our migration. It allows us to move databases from several servers to one unified server.
Fun fact: Skytools was written by people from Skype (Skytools – get it?) which was founded in Estonia. Londiste is named after an Estonian children’s book about an elephant called Londiste.
Let’s do this
Installation
You can run Londiste on any instance you want, it doesn’t need to be the master or slave. I’m using Ubuntu 14.04 where there doesn’t seem to be a package for Skytools3. There appears to be one for 15.04 and 15.10 though, so if you use one of those you could skip all this and try sudo apt-get install python-skytools3. I’ve not tested that but maybe that would save you some time.
Here’s what I did. Download the code:
wget http://pgfoundry.org/frs/download.php/3622/skytools-3.2.tar.gz
Extract it and go to the directory:
tar -zxvf skytools-3.2.tar.gz cd skytools-3.2
Now there is an INSTALL file that explains there are some build dependencies, so we’ll have to install them. First, make sure you have the PostgreSQL Apt Repository so you can install PostgreSQL 9.4 packages. Then run this:
sudo apt-get install postgresql-9.4 libpq-dev postgresql-server-dev-9.4 python python-dev python-pip
And then install the python dependency it lists:
sudo pip install psycopg2
Next in the INSTALL file it says to build “from source tarball” do the following:
./configure make sudo make install
Once that’s complete, run:
londiste3
And it should output:
need config file, use --help for help.
YAY! We have built and installed londiste3.
Preparation
You will need two databases; the master and slave. The master is the ‘live’ database that you want to replicate. The slave is the copy of the master.
Important note: You must be a super user on both master and slave databases for this to work. Otherwise you won’t be able to install the triggers which are written in C. If you want to be able to write from a database that you are a super user of to a database that you don’t have super user access to (e.g. moving to AWS RDS or Heroku) then that’s possible with Skytools 2. This tutorial explains how to do that.
To start off the process you will need to dump the contents of the master in to the slave (or at least the schema). You can do this using pg_dump and psql. I won’t go in to this here because it’s been covered many times before.
Next, it is a good idea to get your head around the connection strings that are required for this process. A connection string is basically something like this:
user=db_username password=db_username host=db_host dbname=db_name
Where you replace all the values starting with db_ with your actual values. Prepare two connection strings for your master and slave databases. I’ll refer to them as MASTER_CONN_STR and SLAVE_CONN_STR from now on in config files and commands.
You are good to go once you have the connection strings, an active master database and a slave database that contains either a snapshot of or the structure of the master.
Londiste
Londiste has a few concepts to get your head around.
The ticker from what I can tell, produces ticks and dictates how often replication events are made.
The root node is the Master database, the source database, the one you are replicating.
A branch node is a slave database. It will receive data from the master node and replicate it. A branch can be used as a reference for other nodes.
A leaf node is the same as a branch node but it cannot be used as a reference for other nodes.
For this guide we’ll just need a ticker, a root node and a leaf node. We don’t need to replicate from a branch node right now.
Ticker
Let’s create the ticker. Create a directory to contain all the londiste specific information:
mkdir londiste-config cd londiste-config mkdir log pid
Create a ticker config file called ticker.ini with the contents:
[pgqd] # libpq connect string without dbname= base_connstr = MASTER_CONN_STR # limit ticker to specific databases database_list = your_master_database # where to log logfile = log/ticker.log # pidfile pidfile = pid/ticker.pid
This tells the ticker which database to connect to and which database to look at. Note: Your MASTER_CONN_STR in this instance should not contain the dbname= part. Let’s start the ticker to make sure it works:
pgqd ticker.ini
If you see something like this:
2015-09-24 11:09:22.461 25868 LOG Starting pgqd 3.2
With no errors below then it’s working! If you see some errors then try to understand what’s going wrong and adjust your config accordingly. I found the error messages slightly cryptic but they do make sense once you understand what they’re saying. Now you can stop the ticker and run it as a daemon:
pgqd -d ticker.ini
If you need to stop the ticker:
pgqd -s ticker.ini
Root
Phew! That’s the ticker done. Now we can create the root node, or the master. Create a master.ini file and add the following:
[londiste3] # target database db = MASTER_CONN_STR # queue name queue_name = your_master_database # how many seconds to sleep between work loops # if missing or 0, then instead sleeping, the script will exit loop_delay = 0.5 # where to log logfile = log/master.log pidfile = pid/master.pid
This time include the full MASTER_CONN_STR. The queue_name can be anything you want, it just has to match with the one specified in the next step.
Now you need to install the required tables and triggers on the master database:
londiste3 master.ini create-root master 'MASTER_CONN_STR'
If that worked, at the end of this process it should say:
2015-09-24 15:57:26,990 804 INFO Done
To start the master worker, run:
londiste3 -d master.ini worker
GREAT. You’ve set up the root database. Now it’s time to set up the leaf node or slave.
Leaf
Similar to before, create slave.ini with the contents:
[londiste3] # target database db = SLAVE_CONN_STR # queue name to read from queue_name = your_master_database # how many seconds to sleep between work loops # if missing or 0, then instead sleeping, the script will exit loop_delay = 0.5 # where to log logfile = log/yourdocs_slave.log pidfile = pid/yourdocs_slave.pid
Set up the node:
londiste3 slave.ini create-branch slave 'SLAVE_CONN_STR' --provider='MASTER_CONN_STR'
And now run the slave worker:
londiste3 -d slave.ini worker
Replicate
Now all you need to do is tell londiste which tables to replicate. In our instance we want to replicate them all:
londiste3 master.ini add-table --all londiste3 slave.ini add-table --all
That’s it. Sit back, relax and the data will be replicated.
Wrap up
It took me a very long time to get that working. Hopefully it should go fine for you because I’ve successfully tested out the above configuration for a few different databases.
Londiste isn’t the most user-friendly piece of software. It took a lot of reading to get this working correctly. Even then I’m not 100% certain I’m doing it correctly. Let me know if you have any suggestions or improvements and I will work them in to this post.
If you’re interested in this kind of thing, we’re hiring.
References:
- PGCon slides on Londiste
- From Skytools docs:
20 thoughts on “PostgreSQL replication with Londiste from Skytools 3”
Comments are closed.
Yay londiste! I love how modular it is. In particular, PgQ is a great transactional queue for Postgres.
Would it be possible to use a streaming replication slave as a master in a londiste replication to another slave. As in I’d like to not fool around with the production master but it’s slave and have data from some tables replicated to a “playground” server
Hey Andreas,
I’m not 100% sure. I know that Londiste and PgQ create several tables in a non-public schema so they might interfere with the streaming replication. Unfortunately the only thing I can think of is to try it out. You could create a dummy database and streaming replication slave and then try to follow this guide on the slave node. I have a feeling that the fact that tables need to be created on the (I’m presuming read-only) slave that an error will be thrown.
Thanks for reading!
Yes, it is possible. In order to do that, slave that you want to use as a master is not “leaf” but “branch”. When you have “branch” it can also takeover from “root”. That is what you need in case that you want to migrate from one provider to another, for example.
Max, great description, makes life so much simpler for a lot of us! I ran through your steps as defined above but hit a snag:
– downloaded the package
– ran the make-related steps (./configure, make and sudo make install)
– gave me no errors.
But when I run londiste3, I get “-bash: londiste3: command not found”. I don’t see a file called londiste3 at all anywhere in the current directory. I see one in /usr/local/local/bin and in build.sk3/scripts-2.6. Running either of those says:
Traceback (most recent call last):
File “/usr/local/local/bin/londiste3”, line 8, in
import pkgloader
ImportError: No module named pkgloader
What am I missing? Sorry this is so detailed a problem!
Which version of Python are you using? It seems to be related to this issue on GitHub here: https://github.com/markokr/… I’m afraid I can’t help you debugging because it seems to be a bug with the installation. I’m sure the people over at the GitHub repository will be able to help you diagnose your issue much better than I could!
I should have checked around before I bothered you – the answer was simple: the installation process puts stuff in usr/local/local, where it should have been in /usr/local. I copied over items in the four subdirectories of usr/local/local to the relevant subdirectories of usr/local and all is well. Thanks!
Friend Max look this error:
root@debian8-db-master:/usr/local/londiste-config# londiste3 slave.ini create-branch slave ‘dbname=name_db_slave host=X.X.X.X port=5432 user=user_db password=passwd_db’ –provider=’dbname=name_db_master host=X.X.X.X port=5432 user=user_db password=passwd_db’
2015-12-16 12:50:49,794 5011 INFO plpgsql is installed
2015-12-16 12:50:49,797 5011 INFO Installing pgq
2015-12-16 12:50:49,798 5011 INFO Reading from /usr/local/share/skytools3/pgq.sql
2015-12-16 12:50:50,610 5011 ERROR Job slave got error on connection ‘db’: could not access file “$libdir/pgq_lowlevel”: No such file or directory. Query: CREATE OR REPLACE FUNCTION pgq.insert_event_raw(
queue_n …
Traceback (most recent call last):
File “/usr/local/lib/python2.7/dist-packages/skytools/scripting.py“, line 579, in run_func_safely
r = func()
File “/usr/local/lib/python2.7/dist-packages/skytools/adminscript.py“, line 62, in work
fn(*cmdargs)
File “/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py“, line 150, in cmd_create_branch
return self.create_node(‘branch’, args)
File “/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py“, line 198, in create_node
self.install_code(db)
File “/usr/local/lib/python2.7/dist-packages/londiste/setup.py“, line 29, in install_code
CascadeAdmin.install_code(self, db)
File “/usr/local/lib/python2.7/dist-packages/pgq/cascade/admin.py“, line 425, in install_code
skytools.db_install(db.cursor(), objs, self.log)
File “/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py“, line 531, in db_install
obj.create(curs, log)
File “/usr/local/lib/python2.7/dist-packages/skytools/sqltools.py“, line 490, in create
curs.execute(stmt)
File “/usr/lib/python2.7/dist-packages/psycopg2/extras.py“, line 120, in execute
return super(DictCursor, self).execute(query, vars)
OperationalError: could not access file “$libdir/pgq_lowlevel”: No such file or directory
/*************/
OS: Debian 8
PostgreSQL: 9.4
Skytools: 3.2
thanks!!!
Hi Yoan, I think I found someone with the same error as you on the Skytools GitHub: https://github.com/markokr/… They say there the solution is to install skytools-modules. On debian 8 you would do (untested): sudo apt-get install postgresql-9.4-pgq3
Dear friend, sorry for my english.
Works by installing Skytools-modules, but I not see replicated in the slave.
Dear friend its works!!! thanks for your help.
Using skytools 3.2 on osx, when I try to run londiste3 master.ini create-root master ‘MASTER_CONN_STR’ I get this error:
Exception: Wrong config file, no section ‘londiste’
It seems like it is expecting my config file to have a line that says `[londiste]`
So I changed [londiste3] to [londiste], but then I got the opposite message:
Exception: Wrong config file, no section ‘londiste3’
So I just duplicated the whole thing twice, ending up with
————————————————————-
[londiste3]
# target database
db = MASTER_CONN_STR
# queue name
queue_name = your_master_database
# how many seconds to sleep between work loops
# if missing or 0, then instead sleeping, the script will exit
loop_delay = 0.5
# where to log
logfile = log/master.log
pidfile = pid/master.pid
[londiste]
# target database
db = MASTER_CONN_STR
# queue name
queue_name = your_master_database
# how many seconds to sleep between work loops
# if missing or 0, then instead sleeping, the script will exit
loop_delay = 0.5
# where to log
logfile = log/master.log
pidfile = pid/master.pid
———————————————————————
After that there were no errors, but I’m sure that can’t be the right solution. Any ideas?
Thanks for this tutorial. Super helpful.
Hi Nathan, unfortunately I have never seen that error before. You could try removing some of the sections under [londiste] or [londiste3] to see which configuration settings are supposed to go where. This sounds like a bug though. You could try installing londiste3 on a new machine to see if the issue is carried across to there.
I’m running now against skytools checked out from github, rather than the downloaded package, which is pretty old, and this problem went away.
Hi, I followed this site and created , root, slave and ticker. I am able to add table in master. I am getting error when I am adding tables to slave.
[postgres@vhedtdspdb01 bin]$ ./londiste3 londiste-config/geodataslave.ini add-table d360t_geodata.region
2016-09-20 10:00:09,098 45672 WARNING d360t_geodata.region not available
2016-09-20 10:00:09,099 45672 ERROR Cannot proceed
Could you please help me.
Londiste expecting schema name to be same in master and slave db. I made it same. Now I am able to add tables for replication.
But replication is not happening. The log says “INFO {standby: 1} “. Please help me to trigger replication.
The replication status is as follows
Queue: masterqueue Local node: repmaster
repmaster (root)
| Tables: 0/5/0
| Lag: 44s, Tick: 1909
+–: slave (branch)
| Tables: 0/5/0
| Lag: 44s, Tick: 1909
+–: slaverep (branch)
Tables: 0/5/0
Lag: 44s, Tick: 1909
How to make it 5/0/0? Please help.
Hi Amala, I’m afraid I don’t know how to fix this. If you followed the blog post and it still doesn’t work then I have little more experience with Londiste than that. You could try the GitHub repo: https://github.com/markokr/…
Hi max,
Can we replicate changes to users and roles from root to leaf using londiste 3 ?
I have one big issue
My database with pgq grown up to 140Gb and i can’t understand how i can clear executed batches from db