Prisma Migration Issue: “Migration history doesn’t match” Error on Production Database

I’m encountering an issue with Prisma migrations in my NestJS application. My backend setup includes Prisma and PostgreSQL, and I’m using a remote database for my production environment. Every time I try to migrate any database schema changes on the production server, I get the following error:

Error: Migration history doesn't match

It suggests resetting the whole database, which unfortunately clears all the data in my production database. Here is a summary of my current workflow:

  1. Backup Production Database: I take a backup of the production database before making any schema changes.
  2. Migrate Schema Changes: I run the migration commands, which leads to the above error and ends up clearing the data.
  3. Restore Data:I manually import the data back into the production database.

Steps I’ve Taken to Resolve the Issue:

  1. Shadow Database Configuration: I’ve specified a different shadow database in my schema.prisma file:
datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

  1. Synchronize Migration History:
    I’ve tried to ensure that the migration history in my local environment matches the one in production by copying migration files from production to local.

  2. Deploying Migrations:
    I’ve attempted to use the npx prisma migrate deploy command to apply the migrations without resetting the database, but the issue persists.