Ghost blog: migrate from SQLite to Postgres

After using Wordpress to host this blog, I stumbled upon Ghost. This new blogging platform has clean design and simple editor with Markdown support.
Built in Node instead of the usual PHP, is also more sexy these days.

After migrating and configuring static pages, I wanted to migrate from SQLite to PostgreSQL. Not a straight task, but is working. Leave here the instructions for who may need it.

0 create a database

createdb blog

1 export SQLiteDB to SQL script, and adapt to Postgres synthax

sqlite3 ghost.db .sch > blog.schema.sql
sed s/"integer not null primary key autoincrement"/"serial primary key"/g -i blog.schema.sql
sed s/"datetime"/"bigint"/g -i blog.schema.sql
sed s/"tinyint"/"integer"/g -i blog.schema.sql

2 load to postgresql

psql blog -f blog.schema.sql

3 dump the data, strip the create table statement, and load it

sqlite3 ghost.db .dump | grep -v "^CREATE" | grep -v "PRAGMA" | grep -v "sqlite_sequence" > blog.dump.sql psql blog -f blog.dump.sql

(script for the steps above is here)

4 Migrate data types and update the sequence tables.

The script to change the sql files can be found here

So far, Ghost is working over Postgres like a charm.

Giscup: matching GPS log
For the 2012 GisCup, me and Sérgio Freitas submitted some code to test some Geospatial methods. The challenge goal was to match a set of points (P) from a GPS log to a set of Edges (E). In this contest, both the accuracy (points matched correctly) and the efficiency (time... Read More
the mackerel
The fish in the blog header is not a Mackerel. Is a random fish from the web with free use. Some people like to have it grilled, some in tomato sauce. The mackerel was a source of inspiration for artists like Vincent van Gogh Nevertheless, this is not a blog... Read More