@humpty Yes, I rolled back twice always with same error restoring app...
I asked to ChatGPT 5.0:
I’m on Cloudron trying to Update Vikunja app to 2.5.0 , but I get this error:
level=ERROR msg="Migration failed: migration 20260720120000 failed: cannot recreate the unique index on tasks (project_id, index) because 2 sets of duplicate values exist — remove the duplicates manually, then restart Vikunja"
what I’ve to do?
Then ChatGPT answered with MySql different queries, then I told:
I’m on postgres
Then gave me this answer:
For PostgreSQL, follow the same principle: keep one task in each duplicate set and assign new task indexes to the others.
1. Back up Vikunja
Create a manual Cloudron backup before modifying the database. Ideally stop Vikunja or ensure nobody is using it during the repair.
2. Connect to PostgreSQL
Open the Vikunja app’s Terminal and run:
psql "$CLOUDRON_POSTGRESQL_URL"
If that variable is unavailable:
PGPASSWORD="$CLOUDRON_POSTGRESQL_PASSWORD" psql \
-h "$CLOUDRON_POSTGRESQL_HOST" \
-p "$CLOUDRON_POSTGRESQL_PORT" \
-U "$CLOUDRON_POSTGRESQL_USERNAME" \
-d "$CLOUDRON_POSTGRESQL_DATABASE"
3. Inspect the duplicates
SELECT
project_id,
"index",
COUNT(*) AS count,
ARRAY_AGG(id ORDER BY id) AS task_ids
FROM tasks
GROUP BY project_id, "index"
HAVING COUNT(*) > 1
ORDER BY project_id, "index";
To also see task titles:
SELECT
t.id,
t.project_id,
t."index",
t.title
FROM tasks AS t
JOIN (
SELECT project_id, "index"
FROM tasks
GROUP BY project_id, "index"
HAVING COUNT(*) > 1
) AS d
ON d.project_id = t.project_id
AND d."index" = t."index"
ORDER BY t.project_id, t."index", t.id;
4. Automatically reassign the extra indexes
The following keeps the lowest task ID in each duplicate set unchanged and assigns unused indexes to the others:
BEGIN;
LOCK TABLE tasks IN ACCESS EXCLUSIVE MODE;
WITH ranked AS (
SELECT
id,
project_id,
"index",
ROW_NUMBER() OVER (
PARTITION BY project_id, "index"
ORDER BY id
) AS duplicate_number
FROM tasks
),
to_fix AS (
SELECT
id,
project_id,
ROW_NUMBER() OVER (
PARTITION BY project_id
ORDER BY "index", id
) AS new_offset
FROM ranked
WHERE duplicate_number > 1
),
project_max AS (
SELECT
project_id,
COALESCE(MAX("index"), 0) AS max_index
FROM tasks
GROUP BY project_id
)
UPDATE tasks AS t
SET "index" = pm.max_index + f.new_offset
FROM to_fix AS f
JOIN project_max AS pm
ON pm.project_id = f.project_id
WHERE t.id = f.id
RETURNING t.id, t.project_id, t."index", t.title;
COMMIT;
This does not delete tasks. It only changes the duplicate per-project task numbers.
5. Confirm the problem is fixed
SELECT project_id, "index", COUNT(*)
FROM tasks
GROUP BY project_id, "index"
HAVING COUNT(*) > 1;
It should return:
(0 rows)
Exit PostgreSQL:
\q
Then restart Vikunja and retry the Cloudron update to 2.5.0.
ChatGPT forgot to remember me to enble recovery mode because app was unreachable at moment, but I solved.
Thanks @humpty for your patience taking look into the problem.
