Convert SQLITE SQL dump file to POSTGRESQL

migrate sqlite to postgresql django
convert sqlite to postgresql online
convert sql query to postgresql online
copy data from sql server to postgresql
convert mysql dump to postgresql
sqlite dump
how to migrate sql server database to postgresql
migrate derby to postgresql

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?

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 and 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, 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 awhile but it'll work.

Convert SQLite to Postgres online, This command instructs pgloader to load data from a SQLite file. Automatic A standard data type conversion from SQLite to PostgreSQL is done. create no  The API will return a ZIP archive that contains a .SQL file. This .SQL is a PostgreSQL dump file and can be restored into your PostgreSQL installation. Conversion methods. Replace file with the path to the *.SQLITE, *.SQLITE3, *.SQLITEDB or *.DB file you want to convert.

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.

Migrating a SQLite database to PostgreSQL, We recently created a PostgreSQL / PostGIS database on a server on our to simply create a SQL dump file out of SQLite and then restore it in Postgres. SQLite may be able to take certain types and convert them to what it  Input file. Our API accepts a single *.SQLITE, *.SQLITE3, *.SQLITEDB or *.DB file as input. Files with these extensions are usually SQLite database file. Additionally, it's possible to specify a SQLite-WAL-file, in case you use Write-Ahead logging. Max file size for web uploads: 50 GB Register to upload big files via Amazon S3.

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

Converting from other Databases to PostgreSQL, Click here to learn how to import dump file into PostgreSQL database. Quick Lauch. Once you stored conversion settings into profile you can run  Saving data into a PostgreSQL Dump and PHP script for export to PostgreSQL database; Saving data into a MS SQL Dump for export to MS SQL database MS SQL schemes support Ability to connect to PostgreSQL 8.x./ 9.x/ 10.x located on Linux, Solaris, Mac OS X, Window machines Ability to select TCP/IP, PIPE, IPX/SPX communications protocols

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

Copying Tables from SQLite to PostgreSQL, Option to export SQLite database into MySQL script file. which also offers such tools for MySQL, Oracle, MS SQL Server, PostgreSQL, Firebird and MaxDB. 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 awhile but it'll work.

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/' | sed -e 's/PRAGMA foreign_keys=OFF;//' | sed -e 's/unsigned big int/BIGINT/g' | sed -e 's/UNSIGNED BIG INT/BIGINT/g' | sed -e 's/BIG INT/BIGINT/g' | sed -e 's/UNSIGNED INT(10)/BIGINT/' | sed -e 's/BOOLEAN/SMALLINT/g' | sed -e 's/boolean/SMALLINT/g' | sed -e 's/UNSIGNED BIG INT/INTEGER/g' | sed -e 's/INT(3)/INT2/g' | sed -e 's/DATETIME/TIMESTAMP/g' | psql mypqdb mypguser 

Convert databases from SQLite to PostgreSQL, You'll want to dump your sqlite DB to SQL statements to import them into PostGreSQL. You can use the sqlite CLI tools to do that. Command  export table definitions from the source SQL database. convert them into PostgreSQL format. load the resulting statements to PostgreSQL server. export MS SQL data into intermediate storage. convert it into PostgreSQL format and load into the target database.

sqlite, database - Convert SQLITE SQL dump file to POSTGRESQL. I've been doing development using SQLITE database with production in POSTGRESQL. Bastien Koert has given the 30,000ft solution, so I'll go into the details: The crucial first step is to set up a local PostgreSQL server, so you can test-load your converted data.

What is the best way to copy my SQLite database to PostgreSQL so I , Create an uncompressed database dump file from sqlite3 ( sqlite3 your.db .dump > Import the dump file into Postgres ( psql -dyourdatabasename -U run these SQL statements in your database shell CREATE USER dbuser  MSSQL-to-PostgreSQL is a migration utility to convert SQL Server or SQL Azure databases to PostgreSQL. Option to filter data using SELECT-queries, synchronization mode, command line support. Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including SQL Server).

database - Convert SQLITE SQL dump file to POSTGRESQL, If you have booleans in your SQLite then you could convert 1 and 0 and 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.

Comments
  • Well, that command did not work for me until I changed sqlite to sqlite3
  • No, you want to keep the transaction to avoid some overhead.
  • This works great. I would also note that if you need to migrate sqlite datetime columns, that you have to change them to timestamp for postgres.
  • A few more issues I ran into: changing BLOB into BYTEA (stackoverflow.com/questions/3103242), changing 0/1 for BOOLEAN columns to '0'/'1', and deferring constraints (DEFERRABLE / SET CONSTRAINTS ALL DEFERRED).
  • @NicholasRiley: Thanks for that. I gave this up to a community wiki since it has turned into a group effort, fair is fair.
  • You can use to_timestamp() in the postgreSQL to convert a timestamp to a progreSQL timestamp
  • Beware that (still supported) Ubuntu distributions might have outdated version - v2.x.y are already deprecated and don't actually work. v3.2.x might work but v3.2.3 is recommended. I have fetched v3.2.3 from bleeding edge and installed with sudo dpkg -i <.deb file name>, it had no problem with dependencies.
  • I concur with @silpol - be sure to download the latest stable release and install using your fav package manager; for the "command" file this is just a text file called 'command' with no extension name (i.e. no need for .txt at the end of the file name) you don't need to put the file name in angular brackets; i had to change the search_parth of the psql database in order to see my data; pgloader works well and saved me a great deal of hassle
  • this save my day.
  • Yeah, I was struggling when I encountered this issue, and that tool made it so easy... Sometimes things just work out nicely, don't they?