Encountering 500 errors (unknown primary key) after restore with correct secrets file

Issue

When performing a GitLab Restore, there are scenarios where there is possible corruption or incomplete upgrade/restoration of the database. The symptoms might appear similar to missing or corrupt secret files where some pages like the CI/CD page, Feature Flags page, etc. raise 500 errors with the following message.

"exception.message": "Unknown primary key for table plans in model Plan.",

Environment

• Impacted offerings:
  • GitLab Self-Managed

Cause

The plans table in the database is expected to have plans_pkey and index_plans_on_name indexes. If the backup data restored is corrupted or might have been manipulated in some way (e.g. has duplicate entries), it can raise errors similar to the one below during the restore and cause pages to raise 500 errors.

—---- BEGIN ERRORS -----
ERROR: could not create unique index "plans_pkey"
DETAIL: Key (id)=(1) is duplicated.
ERROR: could not create unique index "index_plans_on_name"
DETAIL: Key (name)=(default) is duplicated.

Resolution

1. Open gitlab-psql and check the contents of the plans table and verify the indexes are missing.

   \d Plans;
   select * from Plans;

   • A healthy plans table, will include Indexes, Referenced by and Triggers at the end.

                                           Table "public.plans"
   Column   |            Type             | Collation | Nullable |              Default              
   ------------+-----------------------------+-----------+----------+-----------------------------------
   id         | integer                     |           | not null | nextval('plans_id_seq'::regclass)
   created_at | timestamp without time zone |           | not null | 
   updated_at | timestamp without time zone |           | not null | 
   name       | character varying           |           |          | 
   title      | character varying           |           |          | 
   Indexes:
       "plans_pkey" PRIMARY KEY, btree (id)
       "index_plans_on_name" UNIQUE, btree (name)
   Referenced by:
       TABLE "gitlab_subscriptions" CONSTRAINT "fk_bd0c4019c3" FOREIGN KEY (hosted_plan_id) REFERENCES plans(id) ON DELETE CASCADE
       TABLE "plan_limits" CONSTRAINT "fk_rails_69f8b6184f" FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
   Triggers:
       plans_loose_fk_trigger AFTER DELETE ON plans REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records()

   • A broken plans table, will not include the Indexes, Referenced by or Triggers at the end.

                                           Table "public.plans"
   Column   |            Type             | Collation | Nullable |              Default              
   ------------+-----------------------------+-----------+----------+-----------------------------------
   id         | integer                     |           | not null | nextval('plans_id_seq'::regclass)
   created_at | timestamp without time zone |           | not null | 
   updated_at | timestamp without time zone |           | not null | 
   name       | character varying           |           |          | 
   title      | character varying           |           |          | 

2. If the plans table doesn't have the indexes, run the following queries.

   --- add the `plans_pkey` constraint in the `plans` table where the primary key is based on the `id` column
   SET statement_timeout = 0;
   CREATE UNIQUE INDEX CONCURRENTLY plans_pkey on plans(id);
   ALTER TABLE plans ADD CONSTRAINT plans_pkey PRIMARY KEY USING INDEX plans_pkey;
   --- create the `index_plans_on_name` index based on the `name` column using a `btree` data structure
   CREATE UNIQUE INDEX CONCURRENTLY index_plans_on_name ON plans USING btree (name);

3. Reconfigure the instance by running gitlab-ctl reconfigure.

4. Check that the faulty pages are now accessible.