GitLab EE 8.8.2 - cannot execute CREATE EXTENSION in a read-only transaction [Geo]

New customer using a trial of GitLab EE with Geo has encountered a problem when running a gitlab-ctl reconfigure - This could be an issue with miss configuration or a bug, we have asked for additional information.

Recipe: gitlab::postgresql 
* execute[enable pg_trgm extension] action run 
[execute] ERROR: cannot execute CREATE EXTENSION in a read-only 
transaction

This indicates the action is being run on a read-only postgres DB.

@brodock I've escalated the ticket to you in ZD. - https://gitlab.zendesk.com/agent/tickets/25870

Added ~113221 label

/cc @marin

This will be tricky... by initial decision, we opted for not having any additional file configuration required to enable Geo, so we don't have the information in the context of the omnibus setup to tell if it's a secondary node, etc.

If we don't find any workaround, we should rethink that decision to prevent this type of problem. A possible workaround here is to detect, by SQL, if we are in a read-only replica, and not create the extension.

Milestone changed to %8.8

The root of the problem is that the extension isn't getting replicated from the primary. I don't know why... but I've seen this with other customers as well.

Changed title: GitLab EE 8.8.2 - cannot execute CREATE EXTENSION in a read-only [Geo] → GitLab EE 8.8.2 - cannot execute CREATE EXTENSION in a read-only transaction [Geo]

I think better solution is mentioned here by @brodock, detect if we are in read-only and not trigger the extension. This is then omnibus-gitlab issue.

mentioned in issue #76

@brodock Can you fix this in Omnibus?

@marin Can you formulate a workaround the customer could use before the new omnibus package is out?

If enabling extension is the only problem (I am not sure how migration will behave in RO) you can:

1. Download the package
2. Install the package (dpkg -i or rpm -i)
3. Before running reconfigure, in /opt/gitlab/embedded/cookbooks/recipes/postgresql.rb line 170 change the block to:

execute "enable pg_trgm extension" do
  command "exit 0"
  user postgresql_user
  retries 20
  action :nothing
  not_if { !pg_helper.is_running? }
end

1. Configure gitlab.rb and run reconfigure

Can we add a not_if|only_if and run the query select extname from pg_extension where extname = 'pg_trgm';. If that returns a result, then the extension is already enabled. This is a safe(r) way so that we ensure that the extension has actually been enabled or replicated. If it hasn't been, then there will be bigger problems later.

@marin ^

@dblessing How is that different to CREATE EXTENSION IF NOT EXISTS pg_trgm;? This will connect to the database and it will check for the existence of the extension but it will also try to enable it if it doesn't exist.

not_if would connect and check for the plugin and our task would need to create the extension if it doesn't exist.

mentioned in merge request omnibus-gitlab!829 (merged)

Status changed to closed by merge request omnibus-gitlab!829 (merged)

mentioned in commit omnibus-gitlab@64e7864e

mentioned in commit omnibus-gitlab@7a844742

mentioned in commit tnir/omnibus-gitlab@7821bbaa

Mentioned in issue #76