Migrate Sqlite to Postgresql

[tuetenk0pp]

A few weeks ago, I did set up Tandoor on unraid using the template provided by @CorneliousJD. It's a really simple setup btw! But now that I used the app for some time, I would like to migrate to postgres (it now uses sqlite). Any advice on this?

[vabene1111]

migrating different database types is a little tricky. You could use the management commands from django dumpdata and loaddata but those sometimes fail.
There is probably also a tool out there to convert sqlite dumps to postgres compatible statements.

If all of that fails you could at least use the exporter to move over your recipes, i havent gotten around to adding an "export all" button but if you dont have tons of recipes selecting them manually should work

[tuetenk0pp]

Just to let you know, I still have this on my list and will solve this once I got it working

[tuetenk0pp]

Sooo, migration from sqlite to postgres is actually quite simple. Here's how I did it:

• set up postgres as described in the tandoor docs
• install pgloader on your local machine (use WSL if you're on Windows)
• grab a copy of the sqlite database, in my case recipes.db
• set up a .load file for pgloader. Here's an example: sqlite.load.txt. Please remember to remove the .txt extention if you're working off that sample file.
• type pgloader sqlite.load and hope for no errors

sqlite.load sample:

load database
	from /PATH/TO/recipes.db
	into postgresql://djangouser:password@PSQLHOSTNAME:5432/djangodb

with no truncate, create tables, include drop, create indexes, reset sequences, downcase identifiers

set work_mem to '128MB', maintenance_work_mem to '512MB';

EDIT:

I ran into an issue (#700) with invite and share links not working properly and it turned out it was caused by how I did the migration. So here is how I fixed it (all the glory goes to @vabene1111 for his awesome support):

I used a pg_dump for this as I did this a while after migrating from sqlite

• start the tandoor container and let it connect to the empty postgres database so that it does all the migration
• stop it again and run pg_restore -U postgresuser --dbname=djangodb --data-only djangodb.dump
• start the container and everything should be fine

Why did I go through this hell you may ask. I just wanted that fuzzy search.

EDIT2:

something with that pg_restore screwd things up around id sequences inside the db. I was unable to do change ingredients on recipes. This Thread saved me: https://stackoverflow.com/a/244265

-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

At this point I don't know anymore if the migration was worth the effort. If it wasn't for the user accounts and other configuration, a simple recipe export to a zip-file (from within tandoor) and import (again inside tandoor) on the new database would have been way easier.

[vabene1111]

that is a very cool and easy soultion 👍 i took the liberty to change the title, ope that is ok

[tuetenk0pp]

that is a very cool and easy soultion

well it sort of deteriorated 😉

[cvele]

You can use this to sort out sequencing errors, working fine for me at the moment.

DO
$$
DECLARE
    rec RECORD;
    seq_name TEXT;
    max_id BIGINT;
BEGIN
    FOR rec IN
        SELECT table_name, column_default
        FROM information_schema.columns
        WHERE table_schema = 'public'
        AND column_name = 'id'
        AND column_default IS NOT NULL
    LOOP
        -- Extract the sequence name from the column default value
        seq_name := regexp_replace(rec.column_default, 'nextval\(''([^'']+)''.*', '\1');

        -- Get the maximum id from the table
        EXECUTE 'SELECT MAX(id) FROM ' || quote_ident(rec.table_name)
        INTO max_id;

        -- Set the sequence value based on the maximum id
        IF max_id IS NOT NULL THEN
            EXECUTE 'SELECT setval(''' || quote_ident(seq_name) || ''', ' || max_id || ')';
        ELSE
            -- If the table is empty, set the sequence to 1
            EXECUTE 'SELECT setval(''' || quote_ident(seq_name) || ''', 1, false)';
        END IF;
    END LOOP;
END
$$;