Docker-gitlab: permission denied to create extension "btree_gist"

[umputun]

psql_gitlab              | 2020-08-24 20:48:06.415 UTC [185] ERROR:  permission denied to create extension "btree_gist"
psql_gitlab              | 2020-08-24 20:48:06.415 UTC [185] HINT:  Must be superuser to create this extension.
psql_gitlab              | 2020-08-24 20:48:06.415 UTC [185] STATEMENT:  CREATE EXTENSION IF NOT EXISTS btree_gist
gitlab                   | rake aborted!
gitlab                   | StandardError: An error has occurred, this and all later migrations canceled:

Tried to update to 13.3

[ManfredTremmel]

I had the same problem, solved it by adding the extension at the postgresql, the line in the docker-compose file changed from

• DB_EXTENSION=pg_trgm

to

• DB_EXTENSION=pg_trgm,btree_gist

This should be changed in the Readme

[f-io]

Ran into the same issue.
Additionally I need to add the extension manually, cause the migration task failed.

PG::InsufficientPrivilege: ERROR:  permission denied to create extension "btree_gist"
HINT:  Must be superuser to create this extension.

by

docker-compose exec -u postgres postgresql psql -d gitlab_production -c "CREATE EXTENSION IF NOT EXISTS btree_gist

https://gitlab.com/gitlab-org/gitlab/-/issues/240884

Further more, on first start the migration task for postgres throw an error.

postgresql    |  UTC [143] ERROR:  new row for relation "merge_request_diffs" violates check constraint "check_93ee616ac9"
postgresql    |  UTC [143] DETAIL:  Failing row contains .................. 
postgresql    |  UTC [143] STATEMENT:  UPDATE "merge_request_diffs" SET files_count = (
postgresql    |    SELECT count(*)
postgresql    |    FROM merge_request_diff_files
postgresql    |    WHERE merge_request_diff_files.merge_request_diff_id = merge_request_diffs.id
postgresql    |  )
postgresql    |  WHERE "merge_request_diffs"."id" BETWEEN $1 AND $2 AND "merge_request_diffs"."id" >= 10097 AND "merge_request_diffs"."id" < 11097

[cwildfoerster]

postgresql    |  UTC [143] ERROR:  new row for relation "merge_request_diffs" violates check constraint "check_93ee616ac9"
postgresql    |  UTC [143] DETAIL:  Failing row contains .................. 
postgresql    |  UTC [143] STATEMENT:  UPDATE "merge_request_diffs" SET files_count = (
postgresql    |    SELECT count(*)
postgresql    |    FROM merge_request_diff_files
postgresql    |    WHERE merge_request_diff_files.merge_request_diff_id = merge_request_diffs.id
postgresql    |  )
postgresql    |  WHERE "merge_request_diffs"."id" BETWEEN $1 AND $2 AND "merge_request_diffs"."id" >= 10097 AND "merge_request_diffs"."id" < 11097

Hm, i didn't get the error, but i also had the btree_gist extension in place before the upgrade or it has to do something with how old the database is? (gitlab versions?)

https://gitlab.com/gitlab-org/gitlab/-/merge_requests/38549 is the only thing google finds for check_93ee616ac9. Maybe there is old data in the database where the constraint fails.

[f-io]

This was a test upgrad from 13.2.6 -> 13.3.0.

The database is a production copy, and yes that is a few years old (2011).
Maybe it was just because the extension wasn't in place at first start up.

[colachg]

Thank you guys, I have met the same issue. @ManfredTremmel 's solution works. :+1:

[pnelsonsr]

Thanks for the heads up people! Adding the btree_gist Extention to the GitLab DB did it!

If you have an external PostgreSQL GitLab database you simply have to log into it and run:

CREATE EXTENSION IF NOT EXISTS btree_gist;

[edbergavera]

The solution works upgrading from 13.0.1 to 13.3.1