How to convert SQLite SQL dump file to PostgreSQL?

Question

For development I'm using SQLite database with production in PostgreSQL. I updated my local database with data and need to transfer a specific table to the production database.

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 this into a PostgreSQL compatible dump file I can import into my production server?

Answer 1

You should be able to feed that dump file straight into psql:

/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql

If you want the id column to "auto increment" then change its type from "int" to "serial" in the table creation line. PostgreSQL will then attach a sequence to that column so that INSERTs with NULL ids will be automatically assigned the next available value. PostgreSQL will also not recognize AUTOINCREMENT commands, so these need to be removed.

You'll also want to check for datetime columns in the SQLite schema and change them to timestamp for PostgreSQL. (Thanks to Clay for pointing this out.)

If you have booleans in your SQLite then you could convert 1 and 0 to 1::boolean and 0::boolean (respectively) or you could change the boolean column to an integer in the schema section of the dump and then fix them up by hand inside PostgreSQL after the import.

If you have BLOBs in your SQLite then you'll want to adjust the schema to use bytea. You'll probably need to mix in some decode calls as well. Writing a quick'n'dirty copier in your favorite language might be easier than mangling the SQL if you a lot of BLOBs to deal with though.

As usual, if you have foreign keys then you'll probably want to look into set constraints all deferred to avoid insert ordering problems, placing the command inside the BEGIN/COMMIT pair.

Thanks to Nicolas Riley for the boolean, blob, and constraints notes.

If you have ` on your code, as generated by some SQLite3 clients, you need to remove them.

PostGRESQL also doesn't recognize unsigned columns, so you might want to drop that or add a custom-made constraint such as this:

CREATE TABLE tablename (
    ...
    unsigned_column_name integer CHECK (unsigned_column_name > 0)
);

While SQLite defaults null values to '', PostgreSQL requires them to be set as NULL.

The syntax in the SQLite dump file appears to be mostly compatible with PostgreSQL so you can patch a few things and feed it to psql. Importing a big pile of data through SQL INSERTs might take a while but it'll work.

Answer 2

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 3

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 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

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 6

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 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

Answer 8

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.