pg_trgm extension can't be enabled during database migrations

Customer was following the instructions from http://docs.gitlab.com/ee/administration/high_availability/database.html to run database and application on separate nodes. Running gitlab-ctl reconfigure would error when it reached the database migrations step. The migration was trying to enable the pg_trgm extension as the gitlab database user, but the extension requires superuser privileges to install.

We should change the installation of the pg_trgm extension to make sure it is always enabled by the gitlab-psql user.

/cc @balameb

This is something that will probably affect #1807 (closed) as well.

@ibaum From the package side, PG is an external database that should have everything already running. So I would go as far as saying that this is intended behaviour. However, we are at a point where we will need to change this behaviour too so I am ok if we need to change it.

@marin This will affect people who are running our PG on a separate node as well. We only notify to enable the extension during database migration, but the command won't execute since pg isn't running on that node.

I think if we ensure that gitlabhq_production is created and the extension are enabled on the db node, that should suffice.

Thanks for working on this @ibaum, I'll be looking back for any updates

mentioned in issue #2570 (closed)

mentioned in issue #2571

mentioned in issue #2329

Workaround:

su - gitlab-psql
/bin/bash
gitlab-psql -d gitlabhq_production
CREATE EXTENSION pg_trgm;

Then run a gitlab-ctl reconfigure.

@ibaum can we take this up as part of the HA work? Otherwise it seems like people will run into this quite often?

Maybe even a 9.4 service pack, now that we have announced it as experimental?