Can I move an existing site from SQLite to postgres without losing data?

It took some manual effort but I did it.

• Dump the SQLite database.

  • sqlite.exe mydatabase.sqlite3 .dump > mydatabase.dump

• Instantiate a new PostGreSQL database. Edit the settings to point at it. Populate it.

  • python manage.py syncdb --all

  • python manage.py migrate forum --fake

• Go through the SQLite dumpfile, copying the INSERT statements into a PostGreSQL console:

  • but change all boolean values (1 or 0 in sqlite) to a PostGreSQL-compatible value instead - namely '1' and '0' (strings instead of bare numbers).

  • take care of strings in the wrong encoding before PostGres can barf on them.

  • Check the table in the new DB before inserting. Delete existing "boilerplate" rows if new rows will overwrite them. Use ALTER TABLE xxx DISABLE TRIGGER ALL to work around circular references.

  • After inserting a bunch of rows for a single table, go fix up the corresponding PostGreSQL sequence so the current value is beyond the range of IDs inserted.