sqlite2pg.sh

#!/bin/bash

# This script will migrate schema and data from a SQLite3 database to PostgreSQL.
# Schema translation based on http://stackoverflow.com/a/4581921/1303625.
# Some column types are not handled (e.g blobs).
# 
# See also:
# - http://stackoverflow.com/questions/4581727/convert-sqlite-sql-dump-file-to-postgresql
# - https://gist.github.com/bittner/7368128

# cross-OS compatibility (greadlink, gsed, gzcat are GNU implementations for OSX)
readlink=readlink; sed=sed; zcat=zcat
[[ `uname` == 'Darwin' ]] && {
	readlink=greadlink; sed=gsed; zcat=gzcat
		which $readlink $sed $zcat > /dev/null || {
		echo 'ERROR: GNU utils required for Mac. You may use homebrew to install them: brew install coreutils gnu-sed'
		exit 1
	}
}

[[ "$3" == "" || "$4" != "" ]] && {
	echo "Sqlite3 to PostgreSQL database migration: Dump all data from an existing Sqlite database, and create a new PostgreSQL DB from it."
	echo "Usage: ${0##*/} <sqlite_src_db_file> <pg_dest_db_name> <pg_dest_user>"
	exit 1
}

SQLITE_DB_PATH=$1
PG_DB_NAME=$2
PG_USER_NAME=$3

SQLITE_DUMP_FILE="/tmp/sqlite_dump_data.sql"

sqlite3 $SQLITE_DB_PATH .dump > $SQLITE_DUMP_FILE

# PRAGMAs are specific to SQLite3.
$sed -i '/PRAGMA/d' $SQLITE_DUMP_FILE
# Remove unsigned as Postgres doesn't know it.
$sed -i 's/ unsigned[ ]*/ /g' $SQLITE_DUMP_FILE
# Convert sequences.
$sed -i '/sqlite_sequence/d ; s/integer PRIMARY KEY AUTOINCREMENT/serial PRIMARY KEY/ig ; s/"id" integer NOT NULL PRIMARY KEY/"id" serial NOT NULL PRIMARY KEY/g' $SQLITE_DUMP_FILE
# Convert column types.
$sed -i 's/datetime/timestamp with time zone/g ; s/integer[(][^)]*[)]/integer/g ; s/text[(]\([^)]*\)[)]/varchar(\1)/g' $SQLITE_DUMP_FILE
# Convert 0/1 values for boolean types to '0'/'1'.
for bool in 0 0 1 1; do
	# global flag seems to be broken(?) for -i on OSX GNU sed, so we loop twice
	$sed -i "s/,${bool},/,'${bool}',/g" $SQLITE_DUMP_FILE
	$sed -i "s/,${bool})/,'${bool}')/g" $SQLITE_DUMP_FILE
	$sed -i "s/(${bool},/('${bool}',/g" $SQLITE_DUMP_FILE
done

createdb -U $PG_USER_NAME $PG_DB_NAME || exit 2
psql $PG_DB_NAME $PG_USER_NAME < $SQLITE_DUMP_FILE || exit 2
# TODO: grep out error and success messages and redirect them to ${SQLITE_DUMP_FILE}-{error,success}.log
# 2>&1 | sed '/ERROR:  current transaction is aborted, commands ignored until end of transaction block/d' | sed '/^CREATE TABLE$/d' | sed '/^INSERT 0 1$/d' | sed '/^CREATE INDEX$/d'

# Update Postgres sequences.
psql $PG_DB_NAME $PG_USER_NAME -c "\ds" | grep sequence | cut -d'|' -f2 | tr -d '[:blank:]' |
while read sequence_name; do
  table_name=${sequence_name%_id_seq}

  psql $PG_DB_NAME $PG_USER_NAME -c "select setval('$sequence_name', (select max(id) from $table_name))" || exit 2
done

I've tried to enhance Earle Clubb's solution, and make it work for Postgres 9.1 and 9.3, independent of whether executed on a Mac or a recent Linux machine.

I had problems with missing auto_increment id values in most tables, resulting in IntegrityError: null value in column "id" violates not-null constraint. I fixed this with the following SQL for each affected table:

CREATE SEQUENCE "django_xxxx_seq";  -- append START WITH to avoid key already exists
ALTER TABLE "django_xxxx" ALTER "id" SET DEFAULT nextval('django_xxxx_seq');
ALTER SEQUENCE "django_xxxx_seq" OWNED BY "django_xxxx"."id";

This will produce Key ... already exists errors unless you use START WITH (SELECT MAX(id) + 1 FROM django_xxxx) in the CREATE SEQUENCE line.

This is a great script, thanks for writing it. I had to make a modification when I used it -- change line 40 to:

$sed -i '/sqlite_sequence/d ; s/integer PRIMARY KEY AUTOINCREMENT/serial PRIMARY KEY/ig ; s/integer NOT NULL PRIMARY KEY AUTOINCREMENT/serial NOT NULL PRIMARY KEY/ig ; s/"id" integer NOT NULL PRIMARY KEY/"id" serial NOT NULL PRIMARY KEY/g' $SQLITE_DUMP_FILE

Since NOT NULL PRIMARY KEY wasn't accounted for.

I should also note that the sqlite3 dump occurred in the wrong order for me: a couple tables were created before tables in which they had foreign keys, which gave me a "relation does not exist" error. To get around this, I had to manually open the dump in a text editor and move the CREATE and INSERT statements for those tables to just above the INDEX lines. I can't think of a way to do this in the script, unfortunately.

hi, your script is awesome. do the trick with migration. thanks a lot.
Just one thing I noticed during execution of data import type integer differs in sqlite and Postgre SQL and gave me error "ERROR: integer out of range" because integer in Postgre SQL is 4 bytes and in sqlite is 8 bytes i think :)

BLOB type also does not exists in Postgre SQL, need add:
sed -i 's/BLOB/bytea/g' $SQLITE_DUMP_FILE