PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint

How to Use GitLab
,
1

After migrating from 11.0.4 source to 11.0.4 omnibus on a new server everything seemed to work fine.
Then we went the long upgrade path in your docs to now 13.0.4 And I still want to continue the upgrade.
My normal tests all went fine and gitlab was working nice.
But then I tested to create a new project:
PG::NotNullViolation: ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, 27, t, null, 50, t).

and the production_log:

Processing by ProjectsController#create as HTML
  Parameters: {"utf8"=>"✓", "authenticity_token"=>"[FILTERED]", "project"=>{"ci_cd_only"=>"false", "name"=>"dfdfd", "namespace_id"=>"13", "path"=>"dfdfd", "description"=>"[FILTERED]", "visibility_level"=>"0"}}
Unable to save project. Error: PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 26, t, null, 50, t).

==> /var/log/gitlab/postgresql/current <==
2021-02-18_10:06:23.45434 ERROR:  null value in column "id" violates not-null constraint
2021-02-18_10:06:23.45437 DETAIL:  Failing row contains (null, 28, t, null, 50, t).
2021-02-18_10:06:23.45437 STATEMENT:  INSERT INTO "project_ci_cd_settings" ("project_id", "default_git_depth", "forward_deployment_enabled") VALUES (28, 50, TRUE) RETURNING "id"

also found other creates failing, like a new pipeline:

2021-02-18_10:04:36.36238 STATEMENT:  INSERT INTO "project_ci_cd_settings" ("project_id", "default_git_depth", "forward_deployment_enabled") VALUES (27, 50, TRUE) RETURNING "id"
2021-02-18_10:06:23.45434 ERROR:  null value in column "id" violates not-null constraint
2021-02-18_10:06:23.45437 DETAIL:  Failing row contains (null, 28, t, null, 50, t).
2021-02-18_10:06:23.45437 STATEMENT:  INSERT INTO "project_ci_cd_settings" ("project_id", "default_git_depth", "forward_deployment_enabled") VALUES (28, 50, TRUE) RETURNING "id"
2021-02-18_10:15:03.16875 ERROR:  null value in column "id" violates not-null constraint
2021-02-18_10:15:03.16878 DETAIL:  Failing row contains (null, 2653, 14, null, 1, null, null, null, f, null, null).
2021-02-18_10:15:03.16878 STATEMENT:  INSERT INTO "ci_builds_metadata" ("build_id", "project_id", "has_exposed_artifacts") VALUES (2653, 14, FALSE) RETURNING "id"

I found similar problems with very specific null fields and hwo to resilve it, but only one with ID.
There they said it could be a missing or faulty sequence. But as it failed there right after restoring a backup, the where able to redo this with a different psql version.
I can’t do this, as the problem occured much later and we would lose a lot of work and time, going back to the initial 11.0.4 server switch.

And also i found that in a special case the gitlab team provied a SQL for teh specific sequence.

how to find the faulty/missing sequence and how should it look like?

2

looks like I have progress:
I’m now able to create pipelines again by restoring the sequence for id:

$ sudo gitlab-psql -d gitlabhq_production
psql (11.7)
Type "help" for help.

gitlabhq_production=# CREATE SEQUENCE ci_builds_metadata_id_seq;
CREATE SEQUENCE
gitlabhq_production=# alter sequence ci_builds_metadata_id_seq owner to gitlab;
ALTER SEQUENCE
gitlabhq_production=# ALTER TABLE ci_builds_metadata ALTER COLUMN id SET DEFAULT nextval('ci_builds_metadata_id_seq');
ALTER TABLE
gitlabhq_production=# alter sequence ci_builds_metadata_id_seq owned by ci_builds_metadata.id;
ALTER SEQUENCE

dann noch schauen was die aktuell höchste id ist um das zählen nicht bei 1 zu beginnen noch die sequence neu starten:

gitlabhq_production=# select id from ci_builds_metadata;
1
[...]
2637
2638
(2638 rows)

gitlabhq_production=# alter sequence ci_builds_metadata_id_seq restart with 2639;
ALTER SEQUENCE

now to the next sequence…