Here is some operations made for a manual (but quick) SQLite / PostgreSQL migration.
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.
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 :
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.
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));