Manage PostgreSQL extensions

GitLab requires specific PostgreSQL extensions in every database. For the list of required extensions and minimum GitLab versions, see PostgreSQL requirements.

To install extensions, PostgreSQL requires superuser privileges. The GitLab database user is typically not a superuser, so you must install extensions manually before upgrading GitLab.

Install required extensions

1. Connect to the GitLab PostgreSQL database using a superuser, for example:

   sudo gitlab-psql -d gitlabhq_production

2. Install the extension (btree_gist in this example) using CREATE EXTENSION:

   CREATE EXTENSION IF NOT EXISTS btree_gist

3. Verify installed extensions:

   gitlabhq_production=# \dx

On some systems you may need to install an additional package (for example, postgresql-contrib) for certain extensions to become available.

Enable pg_stat_statements

pg_stat_statements is recommended for troubleshooting slow database queries. Enabling it requires superuser privileges and a PostgreSQL restart.

1. Add pg_stat_statements to shared_preload_libraries in postgresql.conf. For Linux package installations, add the following to /etc/gitlab/gitlab.rb:

   postgresql['shared_preload_libraries'] = 'pg_stat_statements'

2. Restart PostgreSQL.

3. Create the extension as a superuser:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements

For more information, see Enable optional query statistics data.

Troubleshooting

When working with PostgreSQL extensions, you might encounter the following issue.

Migration fails because an extension is missing

If a database migration fails because an extension is missing, install it manually as a superuser, then re-run migrations:

sudo gitlab-rake db:migrate