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

[mayankmike86]

We are also facing same issue when installing gitlab charts (this works fine if we install again without deleting the pv data folder)

Checking database migrations are up-to-date
Performing migrations (this will initialized if needed)
psql:/srv/gitlab/db/structure.sql:9: ERROR:  permission denied to create extension "btree_gist"
HINT:  Must be superuser to create this extension.
rake aborted!
failed to execute:
psql --set ON_ERROR_STOP=1 --quiet --no-psqlrc --file /srv/gitlab/db/structure.sql --single-transaction gitlabhq_production

Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.

Is there a way we can pass DB_EXTENSION=pg_trgm,btree_gist when installing the gitlab charts

[kkimurak]

@mayankmike86

Lacks info. And you may be in the wrong place to get help. What image are you using?

If you are using configuration files in kubernetes/ of this repository, you are likely using sameersbn/postgresql. You can see the config in postgresql-rc.yml.
Well, bree_gist is missing. I'll submit a correction PR later.

If you are using the official gitlab charts (if so, which is outside the scope of this repository), you are most likely using bitnami/postgresql. It does not support installation with the DB_EXTENSION environment variable.
Looking at the README and issue comment, it seems that if the shell script is placed in a predetermined directory, it will be executed when the container starts. Try using an installation shell script like the one described above. For more support, please ask at bitnami/container.