DB prisma migration failed while restoring data for latest deployment 2.19.0 from 2.15.0

[kabilesh13]

Describe the Bug

Hi Team,

i been using 2.15.0 version earlier, now when i tried to upgrade to the latest version 2.19.0 i have restore the backup data of 2.15.0 version to the new postgres db and tried to bring up the deployment with latest version 2.19.0, i face below error, can anyone look into this and let me know am i missing out something from my end?

Prisma migration is failing

[email protected] start-docker /app npm-run-all check-db update-tracker set-routes-manifest start-server [email protected] check-db /app node scripts/check-db.js [[email protected]] injecting env (0) from .env -- tip: ⚙️ override existing env vars with { override: true } ✓ DATABASE_URL is defined. ✓ Database connection successful. ✓ Database version check successful. Error: P1002 The database server was reached but timed out. Please try again. Please make sure your database server is running at the configured address. Context: Timed out trying to acquire a postgres advisory lock (SELECT pg_advisory_lock(72707369)). Timeout: 10000ms. See https://pris.ly/d/migrate-advisory-locking for details. ✗ Command failed: prisma migrate deploy Error: P1002 The database server was reached but timed out. Please try again. Please make sure your database server is running at the configured address. Context: Timed out trying to acquire a postgres advisory lock (SELECT pg_advisory_lock(72707369)). Timeout: 10000ms. See https://pris.ly/d/migrate-advisory-locking for details.  ELIFECYCLE  Command failed with exit code 1. ERROR: "check-db" exited with 1.  ELIFECYCLE  Command failed with exit code 1.

Database

PostgreSQL

Relevant log output

> [email protected] start-docker /app
> npm-run-all check-db update-tracker set-routes-manifest start-server
> [email protected] check-db /app
> node scripts/check-db.js
[[email protected]] injecting env (0) from .env -- tip: ⚙️  override existing env vars with { override: true }
✓ DATABASE_URL is defined.
✓ Database connection successful.
✓ Database version check successful.
Error: P1002
The database server was reached but timed out.
Please try again.
Please make sure your database server is running at the configured address.
Context: Timed out trying to acquire a postgres advisory lock (SELECT pg_advisory_lock(72707369)). Timeout: 10000ms. See https://pris.ly/d/migrate-advisory-locking for details.
✗ Command failed: prisma migrate deploy
Error: P1002
The database server was reached but timed out.
Please try again.
Please make sure your database server is running at the configured address.
Context: Timed out trying to acquire a postgres advisory lock (SELECT pg_advisory_lock(72707369)). Timeout: 10000ms. See https://pris.ly/d/migrate-advisory-locking for details.
 ELIFECYCLE  Command failed with exit code 1.
ERROR: "check-db" exited with 1.
 ELIFECYCLE  Command failed with exit code 1.

Which Umami version are you using? (if relevant)

2.19.0

Which browser are you using? (if relevant)

No response

How are you deploying your application? (if relevant)

helm - kubernetes

[franciscao633]

Are you able to execute a query on your db? It looks like your DB its locked out and you just need to terminate the pid.

SELECT pg_terminate_backend(PSA.pid)
FROM pg_locks AS PL
    INNER JOIN pg_stat_activity AS PSA ON PSA.pid = PL.pid
WHERE PSA.state LIKE 'idle'
    AND PL.objid IN (72707369);

[tyler71]

Had this same error coming from v2.17.0. Eventually restarted the database server and get this now:

✓ DATABASE_URL is defined.
✓ Database version check successful.
Error: P3009

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 `09_update_hostname_region` migration started at 2025-07-30 04:41:32.110830 UTC failed


✗ Command failed: prisma migrate deploy

[kabilesh13]

@franciscao633

thanks for highlighting, i have added an init container to the deployment.yaml file and executed the sql queries shared by you, while doing so it stays no advisory locks but still getting same error in the deployment, attaching snips for your reference

image for the output of script shared:

image of the container logs after executing terminate advisory locks via init container

I see pg advisory lock is somehow getting terminated on its own after some time of retrying (container gets restarted continuously if the pod doesn't get to healthy state) post that getting below error with prisma migrations error again sometimes and after few attempts of restart again, app gets healthy, why is this strange behavior seen?

[email protected] start-docker /app npm-run-all check-db update-tracker set-routes-manifest start-server [email protected] check-db /app node scripts/check-db.js [[email protected]] injecting env (0) from .env -- tip: 🔐 encrypt with Dotenvx: https://dotenvx.com ✓ DATABASE_URL is defined. ✓ Database connection successful. ✓ Database version check successful. Error: P3009 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 09_update_hostname_region migration started at 2025-07-30 07:38:38.653115 UTC failed ✗ Command failed: prisma migrate deploy Error: P3009 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 09_update_hostname_region migration started at 2025-07-30 07:38:38.653115 UTC failed  ELIFECYCLE  Command failed with exit code 1. ERROR: "check-db" exited with 1.  ELIFECYCLE  Command failed with exit code 1.

[tyler71]

As far as I can tell, the failure is stemming from 09_update_hostname_region, containing this migration SQL statement:

UPDATE "website_event" w
                         SET hostname = s.hostname
                         FROM "session" s
                         WHERE s.website_id = w.website_id
                             and s.session_id = w.session_id

I let this run overnight (8+ hours) only to receive this error:

An I/O error occurred while sending to the backend.
Unrecoverable error received, the connection is spoiled

[franciscao633]

@kabilesh13 I'm wondering if your DB is timing out for the same reason as @tyler71, since it looks like its failing at 09_update_hostname_region

1. If so, I would manually run the SQL on the DB outside of Prisma. You can also comment out the data migration, if its taking too long to run and back-populate at a later time. The column isn't used much throughout the app.

-- AlterTable
ALTER TABLE "website_event" ADD COLUMN     "hostname" VARCHAR(100);

-- DataMigration
UPDATE "website_event" w
SET hostname = s.hostname
FROM "session" s
WHERE s.website_id = w.website_id
    and s.session_id = w.session_id;

-- DropIndex
DROP INDEX IF EXISTS "session_website_id_created_at_hostname_idx";
DROP INDEX IF EXISTS "session_website_id_created_at_subdivision1_idx";

-- AlterTable
ALTER TABLE "session" RENAME COLUMN "subdivision1" TO "region";
ALTER TABLE "session" DROP COLUMN "subdivision2";
ALTER TABLE "session" DROP COLUMN "hostname";

-- CreateIndex
CREATE INDEX "website_event_website_id_created_at_hostname_idx" ON "website_event"("website_id", "created_at", "hostname");
CREATE INDEX "session_website_id_created_at_region_idx" ON "session"("website_id", "created_at", "region");

2. Mark the migrations as applied

• Run npx prisma migrate resolve --applied 09_update_hostname_region
  OR
• update _prisma_migrations table to applied

UPDATE "_prisma_migrations" 
SET applied_steps_count = 1
WHERE migration_name = '09_update_hostname_region'

3. Restart the app

[hst-Sunday]

@kabilesh13 I'm wondering if your DB is timing out for the same reason as @tyler71, since it looks like its failing at 09_update_hostname_region我想知道您的数据库是否因为相同的原因而超时，因为它看起来像是在 09_update_hostname_region 处失败

1. If so, I would manually run the SQL on the DB outside of Prisma. You can also comment out the data migration, if its taking too long to run and back-populate at a later time. The column isn't used much throughout the app.如果是这样，我会在 Prisma 之外的数据库上手动运行 SQL。如果数据迁移运行时间过长，您也可以注释掉它，稍后再进行回填。该列在整个应用程序中用得不多。

-- AlterTable ALTER TABLE "website_event" ADD COLUMN "hostname" VARCHAR(100);

-- DataMigration UPDATE "website_event" w SET hostname = s.hostname FROM "session" s WHERE s.website_id = w.website_id and s.session_id = w.session_id;

-- DropIndex DROP INDEX IF EXISTS "session_website_id_created_at_hostname_idx"; DROP INDEX IF EXISTS "session_website_id_created_at_subdivision1_idx";

-- AlterTable ALTER TABLE "session" RENAME COLUMN "subdivision1" TO "region"; ALTER TABLE "session" DROP COLUMN "subdivision2"; ALTER TABLE "session" DROP COLUMN "hostname";

-- CreateIndex CREATE INDEX "website_event_website_id_created_at_hostname_idx" ON "website_event"("website_id", "created_at", "hostname"); CREATE INDEX "session_website_id_created_at_region_idx" ON "session"("website_id", "created_at", "region"); 2. Mark the migrations as applied将迁移标记为已应用

• Run npx prisma migrate resolve --applied 09_update_hostname_region  运行 npx prisma migrate resolve --applied 09_update_hostname_region OR  或者
• update _prisma_migrations table to applied将 _prisma_migrations 表更新为已应用

UPDATE "_prisma_migrations" SET applied_steps_count = 1 WHERE migration_name = '09_update_hostname_region' 3. Restart the app  重启应用程序

I've carried out all the steps, but it still doesn't work properly.

Error: P3009

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 `09_update_hostname_region` migration started at 2025-07-31 11:28:16.895805 UTC failed


✗ Command failed: prisma migrate deploy
Error: P3009

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 `09_update_hostname_region` migration started at 2025-07-31 11:28:16.895805 UTC failed

[tyler71]

You can also comment out the data migration

@franciscao633 I skipped the Data Migration step. But this means the data migration will no longer work as is since I'm dropping the session column hostname.

SET hostname = s.hostname
FROM "session" s
[...]
ALTER TABLE "session" DROP COLUMN "hostname";

-- DropIndex
DROP INDEX IF EXISTS "session_website_id_created_at_hostname_idx";
DROP INDEX IF EXISTS "session_website_id_created_at_subdivision1_idx";

-- AlterTable
ALTER TABLE "session" RENAME COLUMN "subdivision1" TO "region";
ALTER TABLE "session" DROP COLUMN "subdivision2";
ALTER TABLE "session" DROP COLUMN "hostname";

-- CreateIndex
CREATE INDEX "website_event_website_id_created_at_hostname_idx" ON "website_event"("website_id", "created_at", "hostname");
CREATE INDEX "session_website_id_created_at_region_idx" ON "session"("website_id", "created_at", "region");

Then in _prisma_migrations I set applied_steps_count = 1 and finished_at to the current date.

UPDATE _prisma_migrations 
SET finished_at = '2025-07-30 16:10:34.148000 +00:00', applied_steps_count = 1
WHERE migration_name = '09_update_hostname_region';

Then it started working again.

[hst-Sunday]

UPDATE _prisma_migrations 
SET finished_at = '2025-07-30 16:10:34.148000 +00:00', applied_steps_count = 1
WHERE migration_name = '09_update_hostname_region';

After running this code and restarting Docker, everything works fine.

[florian2peaches]

it's still failing, we have like 7GB inside website_event, so 5.5 million entries

[github-actions[bot]]

This issue is stale because it has been open for 60 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.