Anyone managed a successful Sqlite -> Postgres migration?

[danielrosehill]

I see that the projet now support Postgres which would definitely be my strong choice of DB.

Anyone here happen to have done a successful Sqlite -> PG migration and lived to tell the tale?

I know that it's not a "plug and play" process given that they are fundamentally different databases but .. I'm hopeful that with some effort it can be done (and certainly worth doing so).

I'm probably in the same case as most in that the only thing that I need to preserve are the item UUIDs given that these construct the item URLs (and I have a bunch of NFC and barcodes printed pointing to them).

Anyway - if anyone has done it, let me know how. If I manage, I will report back with the cliff notes!

[tankerkiller125]

If you have a cross compatible DB management software (like DataGrip for example), you can export each table into CSV files, and then import those CSV files into a fresh postgres database generated by Homebox. (Before you sign in) starting with the group CSV, then users, etc. if you get an error about relationships then you need to import a different table first before you can import the one you originally tried.

This is currently the most straight forward process of doing it that I have unfortunately. In theory it could be turned into an external utility/script if someone wanted to work that out.

As a note, if you have older data from prior to 0.18.0 (or maybe 0.17.0?) it uses a Golang timestamp, which is incompatible with Postgres datetime storage. Those will have to be converted into ISO datetime first before importing the CSVs. At some point I'll write a migration to convert all those in SQLite as well.