SQLite to Postgres data migration

[speshak]

I'm getting ready to dust off my brewery after a while off and I'm updating my fermentrack install. Is there an existing solution to do a data migration from my SQLite database into Postgres? If not, I may try getting a pgloader configuration put together to do the conversion.

(Full disclosure: I've already tried a low-effort run of pgloader against the data. There appear to be a few schema changes that will require a more thoughtful configuration. I wanted to make sure I'm not reinventing the wheel before I sink more time into it.)

[thorrak]

Have you tried using the custom management command for backup/restore? It leverages Django's backup/restore functionality along with a bit of custom code to also grab the data files, and wraps it all into something that can then be loaded either using the associated restore command, or can be restored via an upload back into the Fermentrack web UI.

Without being near a Fermentrack install to test, I believe you can run it by launching the virtualenv and then running manage.py generate_backup. If you have any trouble, drop a note here and I can see if I can log into a copy of Fermentrack and get more exact instructions.

[speshak]

I hadn't considered that approach. I think that might work. The downside is that I don't currently have an "old style" fermentrack install because the SD card I was using was corrupt. I only have my data directory and the db file from a backup. It probably is less work to get an install stood up that I can use to dump a django backup than to continue the pgloader route.

[thorrak]

If you want to send me your sqlite file, I can probably do it quickly from my side. I think you and I have spoken on HBT in recent history -- probably easiest to do the file transfer there.

Alternatively, yeah -- if you just clone Fermentrack and run it in a virtualenv locally it should by default try to read in a sqlite file. Drop the file there, run the management command using that same virtualenv, and you should (hopefully!) be good to go!