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.