Migrate to PostgreSQL

[Revisor01]

Hello,
I love the app very much, thank you so much for it. I wanted to switch to PostgreSQL in Docker, but I'm currently struggling with migrating the old database into PostgreSQL. Does anyone have any ideas on how I can do this?
It would be really helpful.

[TomBursch]

Hi, I haven't done that myself but you should be able to import the SQLite database dump directly into PostgreSQL
See this for example https://stackoverflow.com/questions/4581727/how-to-convert-sqlite-sql-dump-file-to-postgresql

This should be enough for KitchenOwl to work. If you encounter any errors in KitchenOwl, please let me know :)

You could also generate the DB schema first with flask db upgrade in the KitchenOwl backend and then somehow only add your data to Postgres without changing the schema

[Kernald]

Having tried something like this just now, there seems to be issues around the autoincrement columns - I'll update here if I manage to get something working. pgloader is overriding the schema without any autoincrement either, which is a bit surprising, but it's not something I've done before so not sure what to expect...

[Kernald]

I got it to work pretty cleanly with pgloader, as far as I can tell. The process I followed was:

1. Stop KitchenOwl, move both files from the upload directory and the SQLite database away
2. Configure KitchenOwl to use Postgres
3. Start KitchenOwl, let it do its migrations
4. Stop KitchenOwl
5. Import the SQLite database to Postgres with the pgloader.conf file below: pgloader pgloader.conf
6. Move the uploaded files back to the upload directory
7. Start KitchenOwl, it should just work!

Configuration file:

LOAD DATABASE
  FROM sqlite:///tmp/kitchenowl.db
  INTO postgresql://kitchenowl:password@host5432/kitchenowl

WITH data only, reset sequences

EXCLUDING TABLE NAMES like 'alembic_version'

SET work_mem to '16MB', maintenance_work_mem to '512 MB';

Excluding alembic_version is probably not strictly required, but given that Alembic had re-created its schema already, it already added a row there when KitchenOwl started to create said schema - letting Alembic keep track of its own schema version makes sense. The reason for moving the files out of the upload directories is that KitchenOwl seems to scan that directory on start-up and add missing entries to the file table if they're missing (with no created_by). Moving them out lets pgloader import the records with created_by.

Playing around a bit, everything seems fine, the sequences are there and working properly (which was an issue when initially importing the data without any configuration file - pgloader didn't set sequences properly, so while the data was imported, creating a new user, item, or even session token was failing).