Error running migration ...postgres_relationships_v2_v3 relation "payload_locked_documents" does not exist.

[max-degterev]

Describe the Bug

Attempting to migrate an existing v2 DB to v3 format is not as smooth as it could be.

1. process is interactive and asks for renames like headingFontFamily › heading_font_family, but this is easy to check programmatically and very error prone otherwise
2. process fails with relation \"payload_locked_documents\" does not exist

Link to the code that reproduces this issue

No link but see a dump attached

Reproduction Steps

1. create database bldry_staging and make user bldry owner/give permissions
2. restore dump
3. attempt to migrate

Zipped tar file, because GitHub: bldry.tar.zip
Schema: 20241214_162106_imprintLinks.json.zip

Which area(s) are affected? (Select all that apply)

db-postgres

Environment Info

> [email protected] payload
> cross-env NODE_OPTIONS=--no-deprecation payload info


Binaries:
  Node: 20.18.1
  npm: 10.9.1
  Yarn: N/A
  pnpm: N/A
Relevant Packages:
  payload: 3.11.0
  next: 15.1.2
  @payloadcms/db-postgres: 3.11.0
  @payloadcms/email-nodemailer: 3.11.0
  @payloadcms/graphql: 3.11.0
  @payloadcms/next/utilities: 3.11.0
  @payloadcms/plugin-cloud-storage: 3.11.0
  @payloadcms/plugin-stripe: 3.11.0
  @payloadcms/richtext-lexical: 3.11.0
  @payloadcms/storage-s3: 3.11.0
  @payloadcms/translations: 3.11.0
  @payloadcms/ui/shared: 3.11.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 24.1.0: Thu Oct 10 21:03:11 PDT 2024; root:xnu-11215.41.3~2/RELEASE_ARM64_T6020
  Available memory (MB): 32768
  Available CPU cores: 12

[github-actions]

Please add a reproduction in order for us to be able to investigate.

Depending on the quality of reproduction steps, this issue may be closed if no reproduction is provided.

Why was this issue marked with the invalid-reproduction label?

To be able to investigate, we need access to a reproduction to identify what triggered the issue. We prefer a link to a public GitHub repository created with create-payload-app@beta -t blank or a forked/branched version of this repository with tests added (more info in the reproduction-guide).

To make sure the issue is resolved as quickly as possible, please make sure that the reproduction is as minimal as possible. This means that you should remove unnecessary code, files, and dependencies that do not contribute to the issue. Ensure your reproduction does not depend on secrets, 3rd party registries, private dependencies, or any other data that cannot be made public. Avoid a reproduction including a whole monorepo (unless relevant to the issue). The easier it is to reproduce the issue, the quicker we can help.

Please test your reproduction against the latest version of Payload to make sure your issue has not already been fixed.

I added a link, why was it still marked?

Ensure the link is pointing to a codebase that is accessible (e.g. not a private repository). "example.com", "n/a", "will add later", etc. are not acceptable links -- we need to see a public codebase. See the above section for accepted links.

Useful Resources

• Reproduction Guide
• Contributing to Payload

[max-degterev]

I am creating migration like this: npm run payload migrate:create --file @payloadcms/db-postgres/relationships-v2-v3
Then running like this: npm run payload migrate

If this is not the way please let me know and I will try to re-run.

[max-degterev]

After manually creating missing tables like so:

  await payload.db.drizzle.execute(sql`
    CREATE TABLE IF NOT EXISTS payload_locked_documents (
        id SERIAL PRIMARY KEY,
        global_slug character varying,
        updated_at timestamp(3) with time zone NOT NULL DEFAULT now(),
        created_at timestamp(3) with time zone NOT NULL DEFAULT now()
    );

    CREATE UNIQUE INDEX IF NOT EXISTS payload_locked_documents_pkey ON payload_locked_documents(id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_global_slug_8_idx ON payload_locked_documents(global_slug text_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_updated_at_8_idx ON payload_locked_documents(updated_at timestamptz_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_created_at_8_idx ON payload_locked_documents(created_at timestamptz_ops);

    CREATE TABLE IF NOT EXISTS payload_locked_documents_rels (
        id SERIAL PRIMARY KEY,
        "order" integer,
        parent_id integer NOT NULL REFERENCES payload_locked_documents(id) ON DELETE CASCADE,
        path character varying NOT NULL,
        admins_id integer REFERENCES admins(id) ON DELETE CASCADE,
        users_id integer REFERENCES users(id) ON DELETE CASCADE,
        billing_id integer REFERENCES billing(id) ON DELETE CASCADE,
        products_id integer REFERENCES products(id) ON DELETE CASCADE,
        prices_id integer REFERENCES prices(id) ON DELETE CASCADE,
        subscriptions_id integer REFERENCES subscriptions(id) ON DELETE CASCADE,
        temporary_plans_id integer REFERENCES temporary_plans(id) ON DELETE CASCADE,
        coupons_id integer REFERENCES coupons(id) ON DELETE CASCADE,
        media_id integer REFERENCES media(id) ON DELETE CASCADE,
        images_id integer REFERENCES images(id) ON DELETE CASCADE,
        favicons_id integer REFERENCES favicons(id) ON DELETE CASCADE,
        files_id integer REFERENCES files(id) ON DELETE CASCADE,
        domains_id integer REFERENCES domains(id) ON DELETE CASCADE,
        sites_id integer REFERENCES sites(id) ON DELETE CASCADE,
        pages_id integer REFERENCES pages(id) ON DELETE CASCADE,
        fields_config_id integer REFERENCES fields_config(id) ON DELETE CASCADE,
        entries_id integer REFERENCES entries(id) ON DELETE CASCADE,
        notifications_id integer REFERENCES notifications(id) ON DELETE CASCADE
    );

    CREATE UNIQUE INDEX IF NOT EXISTS payload_locked_documents_rels_pkey ON payload_locked_documents_rels(id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_order_idx ON payload_locked_documents_rels("order" int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_parent_idx ON payload_locked_documents_rels(parent_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_path_idx ON payload_locked_documents_rels(path text_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_admins_id_8_idx ON payload_locked_documents_rels(admins_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_users_id_8_idx ON payload_locked_documents_rels(users_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_billing_id_8_idx ON payload_locked_documents_rels(billing_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_products_id_8_idx ON payload_locked_documents_rels(products_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_prices_id_8_idx ON payload_locked_documents_rels(prices_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_subscriptions_id_8_idx ON payload_locked_documents_rels(subscriptions_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_temporary_plans_id_8_idx ON payload_locked_documents_rels(temporary_plans_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_coupons_id_8_idx ON payload_locked_documents_rels(coupons_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_media_id_8_idx ON payload_locked_documents_rels(media_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_images_id_8_idx ON payload_locked_documents_rels(images_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_favicons_id_8_idx ON payload_locked_documents_rels(favicons_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_files_id_8_idx ON payload_locked_documents_rels(files_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_domains_id_8_idx ON payload_locked_documents_rels(domains_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_sites_id_8_idx ON payload_locked_documents_rels(sites_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_pages_id_8_idx ON payload_locked_documents_rels(pages_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_fields_config_id_8_idx ON payload_locked_documents_rels(fields_config_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_entries_id_8_idx ON payload_locked_documents_rels(entries_id int4_ops);
    CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_notifications_id_8_idx ON payload_locked_documents_rels(notifications_id int4_ops);`);

Another error:

column sites.font_family does not exist. Perhaps you meant to reference the column "sites.fontFamily".

Looks like migration script attempts to read the new column name instead of the old one

[max-degterev]

This allowed the migration to run. Server runs in production mode fine. Starting in dev mode detects some sort of schema mismatch and crashes. I can recreate dev DB I guess, not that important, still not very clean.

await payload.db.drizzle.execute(sql`
ALTER TABLE sites RENAME COLUMN "fontFamily" TO font_family;
ALTER TABLE sites RENAME COLUMN "headingFontFamily" TO heading_font_family;
ALTER TABLE pages_blocks_block_hero RENAME COLUMN "imageMode" TO image_mode;
ALTER TABLE pages_blocks_block_cards RENAME COLUMN "imageProportions" TO image_proportions;

CREATE TABLE IF NOT EXISTS payload_locked_documents (
    id SERIAL PRIMARY KEY,
    global_slug character varying,
    updated_at timestamp(3) with time zone NOT NULL DEFAULT now(),
    created_at timestamp(3) with time zone NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS payload_locked_documents_rels (
    id SERIAL PRIMARY KEY,
    "order" integer,
    parent_id integer NOT NULL REFERENCES payload_locked_documents(id) ON DELETE CASCADE,
    path character varying NOT NULL,
    admins_id integer REFERENCES admins(id) ON DELETE CASCADE,
    users_id integer REFERENCES users(id) ON DELETE CASCADE,
    billing_id integer REFERENCES billing(id) ON DELETE CASCADE,
    products_id integer REFERENCES products(id) ON DELETE CASCADE,
    prices_id integer REFERENCES prices(id) ON DELETE CASCADE,
    subscriptions_id integer REFERENCES subscriptions(id) ON DELETE CASCADE,
    temporary_plans_id integer REFERENCES temporary_plans(id) ON DELETE CASCADE,
    coupons_id integer REFERENCES coupons(id) ON DELETE CASCADE,
    media_id integer REFERENCES media(id) ON DELETE CASCADE,
    images_id integer REFERENCES images(id) ON DELETE CASCADE,
    favicons_id integer REFERENCES favicons(id) ON DELETE CASCADE,
    files_id integer REFERENCES files(id) ON DELETE CASCADE,
    domains_id integer REFERENCES domains(id) ON DELETE CASCADE,
    sites_id integer REFERENCES sites(id) ON DELETE CASCADE,
    pages_id integer REFERENCES pages(id) ON DELETE CASCADE,
    fields_config_id integer REFERENCES fields_config(id) ON DELETE CASCADE,
    entries_id integer REFERENCES entries(id) ON DELETE CASCADE,
    notifications_id integer REFERENCES notifications(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX IF NOT EXISTS payload_locked_documents_pkey ON payload_locked_documents(id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_global_slug_8_idx ON payload_locked_documents(global_slug text_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_updated_at_8_idx ON payload_locked_documents(updated_at timestamptz_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_created_at_8_idx ON payload_locked_documents(created_at timestamptz_ops);

CREATE UNIQUE INDEX IF NOT EXISTS payload_locked_documents_rels_pkey ON payload_locked_documents_rels(id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_order_idx ON payload_locked_documents_rels("order" int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_parent_idx ON payload_locked_documents_rels(parent_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_path_idx ON payload_locked_documents_rels(path text_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_admins_id_8_idx ON payload_locked_documents_rels(admins_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_users_id_8_idx ON payload_locked_documents_rels(users_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_billing_id_8_idx ON payload_locked_documents_rels(billing_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_products_id_8_idx ON payload_locked_documents_rels(products_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_prices_id_8_idx ON payload_locked_documents_rels(prices_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_subscriptions_id_8_idx ON payload_locked_documents_rels(subscriptions_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_temporary_plans_id_8_idx ON payload_locked_documents_rels(temporary_plans_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_coupons_id_8_idx ON payload_locked_documents_rels(coupons_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_media_id_8_idx ON payload_locked_documents_rels(media_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_images_id_8_idx ON payload_locked_documents_rels(images_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_favicons_id_8_idx ON payload_locked_documents_rels(favicons_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_files_id_8_idx ON payload_locked_documents_rels(files_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_domains_id_8_idx ON payload_locked_documents_rels(domains_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_sites_id_8_idx ON payload_locked_documents_rels(sites_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_pages_id_8_idx ON payload_locked_documents_rels(pages_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_fields_config_id_8_idx ON payload_locked_documents_rels(fields_config_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_entries_id_8_idx ON payload_locked_documents_rels(entries_id int4_ops);
CREATE INDEX IF NOT EXISTS payload_locked_documents_rels_notifications_id_8_idx ON payload_locked_documents_rels(notifications_id int4_ops);`);

[max-degterev]

Disabled locking on all collections removed the need for hacky payload_locked_documents tables, the bug in case lookups still there though

[r1tsuu]

That error indeed existed, but in 3.10.0 it was resolved #10080

I just tested again, here's the repo - https://github.com/r1tsuu/payload-2x-3x-migration-test which has migrations from v2 and seeds some relations in migrations, the migration on 3.12.0 passes successfully, while on 3.9.0 I have the same error with payload_locked_documnets. I see you have 3.11.0, but maybe something was cached and you needed to delete node_modules / lockfile?

[max-degterev]

Nope migration was starting and failing mid-run until I've put lockDocuments: false in every collection config. After that it ran but still failed at camelcased column lookups.

I managed to run my migration in 2 steps.

Step 1: alter column names from camel to snake case myself

export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
  console.log('> Patching tables before programmatic migration');
  await payload.db.drizzle.execute(sql`
  ALTER TABLE "sites" RENAME COLUMN "fontFamily" TO "font_family";
  ALTER TABLE "sites" RENAME COLUMN "headingFontFamily" TO "heading_font_family";
  ALTER TABLE "pages_blocks_block_hero" RENAME COLUMN "imageMode" TO "image_mode";
  ALTER TABLE "pages_blocks_block_cards" RENAME COLUMN "imageProportions" TO "image_proportions";`);

  console.log('> Running programmatic migration');
  await migratePostgresV2toV3({
    // enables logging of changes that will be made to the database
    debug: true,
    payload,
    req,
  });
}

Step 2: migrate data missed by automatic migration (obfuscated so you can make sense of it):

export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
  console.log('> Creating payload_locked_documents table');
  await payload.db.drizzle.execute(sql`
  CREATE TABLE IF NOT EXISTS "payload_locked_documents" (
  	"id" serial PRIMARY KEY NOT NULL,
  	"global_slug" varchar,
  	"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
  	"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
  );`);

  console.log('> Creating payload_locked_documents_rels table');
  await payload.db.drizzle.execute(sql`
  CREATE TABLE IF NOT EXISTS "payload_locked_documents_rels" (
  	"id" serial PRIMARY KEY NOT NULL,
  	"order" integer,
  	"parent_id" integer NOT NULL,
  	"path" varchar NOT NULL,
  	"admins_id" integer,
  	"users_id" integer,
  	"billing_id" integer,
  	//... all tables ids
  );`);

  console.log('> Adding constraints to payload_locked_documents_rels');
  await payload.db.drizzle.execute(sql`
  DO $$ BEGIN
   ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_parent_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."payload_locked_documents"("id") ON DELETE cascade ON UPDATE no action;
  EXCEPTION
   WHEN duplicate_object THEN null;
  END $$;

  DO $$ BEGIN
   ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_admins_fk" FOREIGN KEY ("admins_id") REFERENCES "public"."admins"("id") ON DELETE cascade ON UPDATE no action;
  EXCEPTION
   WHEN duplicate_object THEN null;
  END $$;

  DO $$ BEGIN
   ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_users_fk" FOREIGN KEY ("users_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
  EXCEPTION
   WHEN duplicate_object THEN null;
  END $$;

   //... all lock constraints ids

  console.log('> Altering defaults');
  await payload.db.drizzle.execute(sql`
  ALTER TABLE "admins" ALTER COLUMN "login_attempts" SET DEFAULT 0;
  ALTER TABLE "users" ALTER COLUMN "locale" SET DEFAULT 'en';
  ALTER TABLE "users" ALTER COLUMN "using_autocomplete" SET DEFAULT false;
  //... add missing defaults

  console.log('> Dropping indexes');
  await payload.db.drizzle.execute(sql`
  DROP INDEX IF EXISTS "sites_rels_users_id_idx";
  DROP INDEX IF EXISTS "sites_rels_favicons_id_idx";
  DROP INDEX IF EXISTS "sites_rels_media_id_idx";`);

  console.log('> Creating payload_locked_documents indexes');
  await payload.db.drizzle.execute(sql`
  CREATE INDEX IF NOT EXISTS "payload_locked_documents_global_slug_idx" ON "payload_locked_documents" USING btree ("global_slug");
  CREATE INDEX IF NOT EXISTS "payload_locked_documents_updated_at_idx" ON "payload_locked_documents" USING btree ("updated_at");
  //... rest of lock indexes

  console.log('> Creating relation indexes');
  await payload.db.drizzle.execute(sql`
  CREATE INDEX IF NOT EXISTS "admins_updated_at_idx" ON "admins" USING btree ("updated_at");
  CREATE UNIQUE INDEX IF NOT EXISTS "users_billing_idx" ON "users" USING btree ("billing_id");
  //... rest if relation indexes

  console.log('> Done!');
}