Here is some operations made for a manual (but quick) SQLite / PostgreSQL migration.

Data export

First, export the data from SQLite :

$ sqlite3 data.db
sqlite> .mode csv
sqlite> .separator ','
sqlite> .output mydb.csv
sqlite> select field1, field2, field3 from mytable where param='value';

The rows are now saved in the selected order in a CSV file.

Data migration

In my case, the size of the dump was small enough to make the following changes using a spreadsheet :

• Insert the ‘\’ escape character before carriage returns, commas and existings ‘\’ in text fiels.
• Replace ‘0’ or ‘1’ by ‘true’ or ‘false’ values for the boolean fields.

The database schema can be exported :

sqlite> .schema

PostgreSQL import

The data can now be imported in PostgreSQL : first recreate the previously exported schema with the statement CREATE <table>. The rows import can be started with :

psql # COPY <table> FROM '/path/to/my/file' DELIMITER ',';

All data is now imported in the new database. It could be required to adjust the sequence of the generated primary key.
If SELECT MAX(<id>) FROM <table>; is greater than SELECT nextval('<table>_<id>_seq'); the sequence must be adjusted with the statement :

SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));