Create pg_trgm extension in the NixOS module
#657
Merged
Conversation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
nlewo
force-pushed
the
create-pg-trgm-in-module
branch
from
ca61f57
to
40ca74a
Compare
|
@rbvermaa @basvandijk does this PR fix your issue (#660) ? |
|
I haven't tried this on our company hydra but I can confirm this works with |
edolstra
reviewed
Jul 24, 2019
hydra-module.nix
Outdated
| @@ -273,6 +273,10 @@ in | |||
| runuser -u ${config.services.postgresql.superUser} -- ${config.services.postgresql.package}/bin/createdb -O hydra hydra | |||
| touch ${baseDir}/.db-created | |||
| fi | |||
| # The extension pg_trgm needs to be created by a superuser. | |||
| echo "alter user hydra with superuser" | runuser -u ${config.services.postgresql.superUser} -- ${config.services.postgresql.package}/bin/psql | |||
| echo "create extension pg_trgm" | ${config.services.postgresql.package}/bin/psql -U hydra | |||
There was a problem hiding this comment.
Could this be done via the postgres superuser? Temporarily making hydra a superuser seems a bit ugly.
There was a problem hiding this comment.
Yep, this is ugly:/
I added a mapping for the postgres user in the pg_ident.conf. The postgres superuser is now able to create the index for the hydra database, so we no longer need to provide superuser capabilities to the hydra user.
The creation of the `pg_trgm` extension needs superuser power. So,
this patch makes the extension creation in the Hydra NixOS module when
a local database is used.
If it is not possible to create this extension (remote database for
instance with nosuperuser), the creation of the `pg_trgm` index is
skipped (this index speedup queries on builds.drvpath) and warnings
are emitted:
initialising the Hydra database schema...
WARNING: Can not create extension pg_trgm: permission denied to create extension "pg_trgm"
WARNING: HINT: Temporary provide superuser role to your Hydra Postgresql user and run the script src/sql/upgrade-57.sql
WARNING: The pg_trgm index on builds.drvpath has been skipped (slower complex queries on builds.drvpath)
This allows to keep smooth migrations: the migration process doesn't
require a manual step (but this manual step is recommended on big
remote databases).
nlewo
force-pushed
the
create-pg-trgm-in-module
branch
from
40ca74a
to
8a0a5ec
Compare
|
Thanks! |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
The creation of the
pg_trgmextension needs superuser power. So,this patch makes the extension creation in the Hydra NixOS module when
a local database is used.
If it is not possible to create this extension (remote database for
instance with nosuperuser), the creation of the
pg_trgmindex isskipped (this index speedup queries on builds.drvpath) and warnings
are emitted:
This allows to keep smooth migrations: the migration process doesn't
require a manual step (but this manual step is recommended on big
remote databases).
@rbvermaa does it work for you?