Migrate Ghost from SQLite to PostgreSQL

ghost_sqlite2pgsql.sh

#1. destroy and recreate the database (the fastest way)
dropdb blog
createdb blog
 
#2. dump the sqlite, hack the schema, and load
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
psql blog  -f blog.schema.sql
psql blog  -f blog.schema.sql # twice, because the order is not correct
sqlite3 ghost.db .dump | grep -v "^CREATE" | grep -v "PRAGMA" | grep -v "sqlite_sequence" > blog.dump.sql
psql blog  -f blog.dump.sql 
 
#3 - after the import, migrate to the correct data types (check the other gist ghost_sqlite2pgsql.sql)

context: https://andrade.io/ghost-blog-migrate-from-sqlite-to-postgres/