umami-software
migrate from mysql to postgresql failed
upgrade from umami 2.19 + mysql to umami 3.0.1 + postgres
Describe the Bug
1. Will this warning cause bugs or affect usage?
WARNING PostgreSQL warning: identifier "idx_16513_website_event_website_id_created_at_referrer_domain_idx" will be truncated to "idx_16513_website_event_website_id_created_at_referrer_domain_i"
Database
PostgreSQL
Relevant log output
pgloader \
> mysql://umami:xxx@ip:3306/umami \
> pgsql://umami:xxx@ip:5432/umami
2025-11-22T03:25:19.054000Z LOG pgloader version "3.6.10~devel"
2025-11-22T03:25:19.303998Z LOG Migrating from #<MYSQL-CONNECTION mysql://umami@ip:3306/umami {1005E11D03}>
2025-11-22T03:25:19.303998Z LOG Migrating into #<PGSQL-CONNECTION pgsql://umami@ip:5432/umami {1005FD70F3}>
2025-11-22T03:25:35.684882Z WARNING PostgreSQL warning: identifier "idx_16513_website_event_website_id_created_at_referrer_domain_idx" will be truncated to "idx_16513_website_event_website_id_created_at_referrer_domain_i"
2025-11-22T03:25:54.097753Z LOG report summary reset
table name errors rows bytes total time
------------------------ --------- --------- --------- --------------
fetch meta data 0 92 0.685s
Create Schemas 0 0 0.003s
Create SQL Types 0 0 0.007s
Create tables 0 26 0.068s
Set Table OIDs 0 13 0.010s
------------------------ --------- --------- --------- --------------
umami.website_event 0 291114 93.1 MB 13.178s
umami.event_data 0 31138 4.6 MB 1.799s
umami.website 0 6 1.1 kB 1.660s
umami."user" 0 2 0.3 kB 2.344s
umami.report 0 1 0.4 kB 2.817s
umami.segment 0 0 3.019s
umami.team 0 1 0.1 kB 4.097s
umami.session 0 137626 20.1 MB 13.675s
umami._prisma_migrations 0 13 2.2 kB 1.060s
umami.team_user 0 2 0.3 kB 0.371s
umami._event_old 0 0 3.196s
umami.revenue 0 0 0.365s
umami.session_data 0 0 0.527s
------------------------ --------- --------- --------- --------------
COPY Threads Completion 0 4 27.221s
Create Indexes 0 79 3m58.143s
Index Build Completion 0 79 4.818s
Reset Sequences 0 1 0.228s
Primary Keys 0 13 1.022s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.001s
Install Comments 0 0 0.001s
------------------------ --------- --------- --------- --------------
Total import time ✓ 459903 117.7 MB 4m31.434s
2. umami start failed
✓ DATABASE_URL is defined.
✓ Database connection successful.
✓ Database version check successful.
✗ Command failed: prisma migrate deploy
Error: P3009
2025-11-22T04:29:55.654208696Z
migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
The `14_add_link_and_pixel` migration started at 2025-11-22 04:22:50.776497 UTC failed
2025-11-22T04:29:55.654227823Z
2025-11-22T04:29:55.654232893Z
2025-11-22T04:29:55.654237946Z
Error: P3009
2025-11-22T04:29:55.654273978Z
migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
The `14_add_link_and_pixel` migration started at 2025-11-22 04:22:50.776497 UTC failed
2025-11-22T04:29:55.654290695Z
2025-11-22T04:29:55.654295702Z
ELIFECYCLE Command failed with exit code 1.
ERROR: "check-db" exited with 1.
ELIFECYCLE Command failed with exit code 1.
3. password error
force fix 14_add_link_and_pixel error by:
UPDATE _prisma_migrations
SET finished_at = now(), applied_steps_count = 1, logs = 'manually applied'
WHERE migration_name = '14_add_link_and_pixel';
can not login any more
Which Umami version are you using? (if relevant)
3.0.1
Which browser are you using? (if relevant)
No response
How are you deploying your application? (if relevant)
docker
Sorry getting to this late. Did you manually run 14_add_link_and_pixel after if failed. The update statement is fine as long as the migration was eventually run. I have no idea why that would affect login however. What error comes back when you try to login into the app.
I guess is that this issue occurred because PostgreSQL uses pgcrypto, which has a different encryption method compared to MySQL, leading to login failures.
After forcibly fixing the error in the 14_add_link_and_pixel migration script using SQL commands, I was completely unable to log into PostgreSQL. Ultimately, I had to use an SQL command to modify the user's password to regain access.
Is the 14_add_link_and_pixel migration script a feature introduced in version 3.0? When upgrading from version 2.19, it seems the migration failed due to missing data checks in the script. This script may need stronger compatibility considerations.