Convert SQLITE SQL dump file to POSTGRESQL

Question

I've been doing development using SQLITE database with production in POSTGRESQL. I just updated my local database with a huge amount of data and need to transfer a specific table to the production database.

Based on running sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE outputs a table dump in the following format:

BEGIN TRANSACTION;
CREATE TABLE "courses_school" ("id" integer PRIMARY KEY, "department_count" integer NOT NULL DEFAULT 0, "the_id" integer UNIQUE, "school_name" varchar(150), "slug" varchar(50));
INSERT INTO "courses_school" VALUES(1,168,213,'TEST Name A',NULL);
INSERT INTO "courses_school" VALUES(2,0,656,'TEST Name B',NULL);
....
COMMIT;

How do I convert the above into a POSTGRESQL compatible dump file that I can import into my production server?

Answer 1

pgloader

I came across this post when searching for a way to convert an SQLite dump to PostgreSQL. Even though this post has an accepted answer (and a good one at that +1), I think adding this is important.

I started looking into the solutions here and realized that I was looking for a more automated method. I looked up the wiki docs:

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

and discovered pgloader. Pretty cool application and it's relatively easy to use. You can convert the flat SQLite file into a usable PostgreSQL database. I installed from the *.deb and created a command file like this in a test directory:

load database  
    from 'db.sqlite3'  
    into postgresql:///testdb 
       
with include drop, create tables, create indexes, reset sequences  
         
set work_mem to '16MB', maintenance_work_mem to '512 MB';

like the docs state. I then created a testdb with createdb:

createdb testdb

I ran the pgloader command like this:

pgloader command

and then connected to the new database:

psql testdb

After some queries to check the data, it appears it worked quite well. I know if I had tried to run one of these scripts or do the stepwise conversion mentioned herein, I would have spent much more time.

To prove the concept I dumped this testdb and imported into a development environment on a production server and the data transferred over nicely.

Answer 2

The sequel gem (a Ruby library) offers data copying across different databases: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

First install Ruby, then install the gem by running gem install sequel.

In case of sqlite, it would be like this: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

Answer 3

I wrote a script to do the sqlite3 to postgres migration. It doesn't handle all the schema/data translations mentioned in https://stackoverflow.com/a/4581921/1303625, but it does what I needed it to do. Hopefully it will be a good starting point for others.

https://gist.github.com/2253099

Answer 4

You can use a one liner, here is an example with the help of sed command:

sqlite3 mjsqlite.db .dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/g;s/PRAGMA foreign_keys=OFF;//;s/unsigned big int/BIGINT/g;s/UNSIGNED BIG INT/BIGINT/g;s/BIG INT/BIGINT/g;s/UNSIGNED INT(10)/BIGINT/g;s/BOOLEAN/SMALLINT/g;s/boolean/SMALLINT/g;s/UNSIGNED BIG INT/INTEGER/g;s/INT(3)/INT2/g;s/DATETIME/TIMESTAMP/g' | psql mypqdb mypguser

Answer 5

Try these steps...

Step 01: Dump sqlite db to json

python3 manage.py dumpdata > data.json

Step 02: Create tables without migration

python3 manage.py migrate --run-syncdb

Step 03: Open django shell. Then exclude contentype data

python3 manage.py shell
from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()
quit()

Step 04: Load Data

python3 manage.py loaddata data.json

Answer 6

I have tried editing/regexping the sqlite dump so PostgreSQL accepts it, it is tedious and prone to error.

What I got to work really fast:

First recreate the schema on PostgreSQL without any data, either editing the dump or if you were using an ORM you may be lucky and it talks to both back-ends (sqlalchemy, peewee, ...).

Then migrate the data using pandas. Suppose you have a table with a bool field (which is 0/1 in sqlite, but must be t/f in PostgreSQL)

def int_to_strbool(df, column):
    df = df.replace({column: 0}, 'f')
    df = df.replace({column: 1}, 't')
    return df

#def other_transform(df, column):
#...

conn = sqlite3.connect(db)
df = pd.read_sql(f'select * from {table_name}', conn)

df = int_to_strbool(df, bool_column_name)
#df = other_transform(df, other_column_name)

df.to_csv(table_name + '.csv'), sep=',', header=False, index=False)

This works like a charm, is easy to write, read and debug each function, unlike (for me) the regular expressions.

Now you can try to load the resulting csv with PostgreSQL (even graphically with the admin tool), with the only caveat that you must load the tables with foreign keys after you have loaded the tables with the corresponding source keys. I did not have the case of a circular dependency, I guess you can suspend temporarily the key checking if that is the case.

Answer 7

pgloader work wonders on converting database in sqlite to postgresql.

Here's an example on converting a local sqlitedb to a remote PostgreSQL db:

pgloader sqlite.db postgresql://username:password@hostname/dbname