How to use pgloader to migrate sqlite database to postgresql

Docker, cookbook, Postgresql, SQLite, Kotti

I needed to migrate a Kotti database, from its default sqlite file store, to Postgresql. Clued in by StackOverflow, I've tried using pgloader, but the version coming with Ubuntu is old: 2.x instead of the brand new 3.x. But the jump to 3.x meant a switch in programming languages as well: the new one is written in Lisp. I didn't want to install and compile the whole Lisp bundle just to run pgloader and I didn't find a binary distribution either, and after a recent exposure to Docker, I thought I'll give the dockerized version of pgloader a try.

After following the steps to install Docker, took me a bit to figure out the process (note: I'm running all this in a VMWare virtual machine, so I can afford taking a lot of unsecure shortcuts):

First, the local Postgresql database needs to be configured to run on an IP, to allow the dockerized pgloader process to connect to it.

sudo vim  /etc/postgresql/9.3/main/pg_hba.conf

Change the network settings to allow connections from all:

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

sudo vim  /etc/postgresql/9.3/main/postgresql.conf

listen_addresses = '*'		# what IP address(es) to listen on;

docker pull dimitri/pgloader

I've created a file named "convert" with the commands for pgloader, to do the conversion:

load database  
    from 'mydb.db'  
    into postgresql://user:password@192.168.1.20/mydb

with reset sequences, create no tables, include no drop, create no indexes, disable triggers
set work_mem to '200MB', maintenance_work_mem to '512 MB';

For the IP of the postgresql I've used the one attached to eth0.

psql mydb
truncate table nodes cascade
truncate table nodes principals

sudo docker run --rm --name pgloader -v /path/to/data/:/data dimitri/pgloader:latest pgloader /data/convert