Database migration
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:
<pre>wget http://pgfoundry.org/frs/download.php/3622/skytools-3.2.tar.gz</pre>
Extract it and go to the directory:
<pre>tar -zxvf skytools-3.2.tar.gz
cd skytools-3.2</pre>
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:
<pre>sudo apt-get install postgresql-9.4 libpq-dev postgresql-server-dev-9.4 python python-dev python-pip</pre>
And then install the python dependency it lists:
<pre>sudo pip install psycopg2</pre>
Next in the INSTALL file it says to build "from source tarball" do the following:
<pre>./configure
make
sudo make install</pre>
Once that's complete, run:
<pre>londiste3</pre>
And it should output:
<pre>need config file, use --help for help.[</pre>
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:
[bash]user=db_username password=db_username host=db_host dbname=db_name[/bash]
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:
<pre>mkdir londiste-config
cd londiste-config
mkdir log pid</pre>
Create a ticker config file called ticker.ini with the contents:
<pre>[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</pre>
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:
<pre>pgqd ticker.ini</pre>
If you see something like this:
<pre>2015-09-24 11:09:22.461 25868 LOG Starting pgqd 3.2</pre>
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:
<pre>pgqd -d ticker.ini</pre>
If you need to stop the ticker:
<pre>pgqd -s ticker.ini</pre>
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:
<pre>[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</pre>
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:
<pre>londiste3 master.ini create-root master 'MASTER_CONN_STR'</pre>
If that worked, at the end of this process it should say:
<pre>2015-09-24 15:57:26,990 804 INFO Done</pre>
To start the master worker, run:
<pre>londiste3 -d master.ini worker</pre>
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:
<pre>[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</pre>
Set up the node:
<pre>londiste3 slave.ini create-branch slave 'SLAVE_CONN_STR' --provider='MASTER_CONN_STR'</pre>
And now run the slave worker:
<pre>londiste3 -d slave.ini worker</pre>
Replicate
Now all you need to do is tell londiste which tables to replicate. In our instance we want to replicate them all:
<pre>londiste3 master.ini add-table --all
londiste3 slave.ini add-table --all</pre>
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.
References:
From Skytools docs:
