Database settings

GitLab supports only PostgreSQL database management system.

Thus you have two options for database servers to use with Omnibus GitLab:

  • Use the packaged PostgreSQL server included with Omnibus GitLab (no configuration required, recommended).
  • Use an external PostgreSQL server.

Using the PostgreSQL Database Service shipped with Omnibus GitLab

Configuring SSL

Omnibus automatically enables SSL on the PostgreSQL server, but it will accept both encrypted and unencrypted connections by default. Enforcing SSL requires using the hostssl configuration in pg_hba.conf. For more details, see the pg_hba.conf documentation.

SSL support depends on the following files:

  • The public SSL certificate for the database (server.crt).
  • The corresponding private key for the SSL certificate (server.key).
  • A root certificate bundle that validates the server’s certificate (root.crt). By default, Omnibus GitLab uses the embedded certificate bundle in /opt/gitlab/embedded/ssl/certs/cacert.pem. This isn’t required for self-signed certificates.

A self-signed certificate and private key will be generated for use. If you’d prefer to use a CA-signed certificate, use the following steps.

Note that the location of these files can be configurable, but the private key must be readable by the gitlab-psql user. Omnibus manages the permissions of the files for you, but if the paths are customized, you must ensure that the gitlab-psql can access the directory the files are placed in.

For more details, see the PostgreSQL documentation.

Note that server.crt and server.key may be different from the default SSL certificates used to access GitLab. For example, suppose the external hostname of your database is database.example.com, and your external GitLab hostname is gitlab.example.com. You will either need a wildcard certificate for *.example.com or two different SSL certificates.

After you have these files, enable SSL:

  1. Edit /etc/gitlab/gitlab.rb:

    postgresql['ssl_cert_file'] = '/custom/path/to/server.crt'
    postgresql['ssl_key_file'] = '/custom/path/to/server.key'
    postgresql['ssl_ca_file'] = '/custom/path/to/bundle.pem'
    postgresql['internal_certificate'] = "-----BEGIN CERTIFICATE-----
    ...base64-encoded certificate...
    -----END CERTIFICATE-----
    "
    postgresql['internal_key'] = "-----BEGIN RSA PRIVATE KEY-----
    ...base64-encoded private key...
    -----END RSA PRIVATE KEY-----
    "
    

    Relative paths will be rooted from the PostgreSQL data directory (/var/opt/gitlab/postgresql/data by default).

  2. Reconfigure GitLab to apply the configuration changes.

  3. Restart PostgreSQL for the changes to take effect:

    gitlab-ctl restart postgresql
    

    If PostgreSQL fails to start, check the logs (for example, /var/log/gitlab/postgresql/current) for more details.

Require SSL

  1. Add the following to /etc/gitlab/gitlab.rb:

     postgresql['db_sslmode'] = 'require'
    
  2. Reconfigure GitLab to apply the configuration changes.

  3. Restart PostgreSQL for the changes to take effect:

    gitlab-ctl restart postgresql
    

    If PostgreSQL fails to start, check the logs (for example, /var/log/gitlab/postgresql/current) for more details.

Disabling SSL

  1. Add the following to /etc/gitlab/gitlab.rb:

    postgresql['ssl'] = 'off'
    
  2. Reconfigure GitLab to apply the configuration changes.

  3. Restart PostgreSQL for the changes to take effect:

    gitlab-ctl restart postgresql
    

    If PostgreSQL fails to start, check the logs (for example, /var/log/gitlab/postgresql/current) for more details.

Verifying that SSL is being used

To determine whether SSL is being used by clients, you can run:

gitlab-rails dbconsole

At startup, you should see a banner as the following:

psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.

To determine if clients are using SSL, issue this SQL query:

SELECT * FROM pg_stat_ssl;

For example:

gitlabhq_production=> select * from pg_stat_ssl;
 pid  | ssl | version |         cipher         | bits | compression |  clientdn
------+-----+---------+------------------------+------+-------------+------------
  384 | f   |         |                        |      |             |
  386 | f   |         |                        |      |             |
  998 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
  933 | f   |         |                        |      |             |
 1003 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
 1016 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
 1022 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
 1211 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
 1214 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
 1213 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
 1215 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
 1252 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           |
 1280 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab
  382 | f   |         |                        |      |             |
  381 | f   |         |                        |      |             |
  383 | f   |         |                        |      |             |
(16 rows)
  1. Rows that have t listed under the ssl column are enabled.
  2. Rows that have a value in the clientdn are using the cert authentication method

Configure SSL client authentication

Client SSL certificates can be used to authenticate to the database server. Creating the certificates is beyond the scope of omnibus-gitlab. But users who have an existing SSL certificate management solution can use this.

Configure the database server
  1. Create a certificate and key for the server, the common name should equal the DNS name of the server
  2. Copy the server certificate, key, and CA file to the PostgreSQL server, and ensure the permissions are correct
    1. The certificate should be owned by the database user (default: gitlab-psql)
    2. The key file should be owned by the database user, and its permissions should be 0400
    3. The CA file should be owned by the database user, and its permissions should be 0400
    Note: Don’t use the filenames server.crt or server.key for these files. These filenames are reserved for the internal use of omnibus-gitlab.
  3. Ensure the following is set in gitlab.rb:

    postgresql['ssl_cert_file'] = 'PATH_TO_CERTIFICATE'
    postgresql['ssl_key_file'] = 'PATH_TO_KEY_FILE'
    postgresql['ssl_ca_file'] = 'PATH_TO_CA_FILE'
    postgresql['listen_address'] = 'IP_ADDRESS'
    postgresql['cert_auth_addresses'] = {
    'IP_ADDRESS' => {
      'database' => 'gitlabhq_production',
      'user' => 'gitlab'
    }
    

    Set listen_address as the IP address of the server that the clients will use to connect to the database. Ensure cert_auth_addresses contains a list of IP addresses, and the databases and users that are allowed to connect to the database. You can use CIDR notation when specifying the key for cert_auth_addresses to incorporate an IP address range.

  4. Run gitlab-ctl reconfigure, and then gitlab-ctl restart postgresql for the new settings to take effect.

Configure the Rails client

In order for the rails client to connect to the server, you will need a certficate and key with the commonName set to gitlab, that is signed by a certificate authority trusted in the CA file specified in ssl_ca_file on the database server.

  1. Configure gitlab.rb

    gitlab_rails['db_host'] = 'IP_ADDRESS_OR_HOSTNAME_OF_DATABASE_SERVER'
    gitlab_rails['db_sslcert'] = 'PATH_TO_CERTIFICATE_FILE'
    gitlab_rails['db_sslkey'] = 'PATH_TO_KEY_FILE'
    gitlab_rails['db_rootcert'] = 'PATH_TO_CA_FILE'
    
  2. Run gitlab-ctl reconfigure for the rails client to use the new settings
  3. Follow the steps in Verifying that SSL is being used to ensure the authentication is working.

Configure packaged PostgreSQL server to listen on TCP/IP

The packaged PostgreSQL server can be configured to listen for TCP/IP connections, with the caveat that some non-critical scripts expect UNIX sockets and may misbehave.

To configure the use of TCP/IP for the database service, make changes to both the postgresql and gitlab_rails sections of gitlab.rb.

Configure PostgreSQL block

The following settings are affected in the postgresql block:

  • listen_address: Controls the address on which PostgreSQL will listen.
  • port: Controls the port on which PostgreSQL will listen, and must be set if listen_address is.
  • md5_auth_cidr_addresses: A list of CIDR address blocks which are allowed to connect to the server, after authentication with a password.
  • trust_auth_cidr_addresses: A list of CIDR address blocks which are allowed to connect to the server, without authentication of any kind. Be very careful with this setting. It’s suggested that this be limited to the loopback address of 127.0.0.1/24 or even 127.0.0.1/32.
  • sql_user: Controls the expected username for MD5 authentication. This defaults to gitlab, and isn’t a required setting.
  • sql_user_password: Sets the password that PostgreSQL will accept for MD5 authentication. Replace securesqlpassword in the following example with an acceptable password.
postgresql['listen_address'] = '0.0.0.0'
postgresql['port'] = 5432
postgresql['md5_auth_cidr_addresses'] = %w()
postgresql['trust_auth_cidr_addresses'] = %w(127.0.0.1/24)
postgresql['sql_user'] = "gitlab"

##! SQL_USER_PASSWORD_HASH can be generated using the command `gitlab-ctl pg-password-md5 gitlab`,
##! where `gitlab` is the name of the SQL user that connects to GitLab.
postgresql['sql_user_password'] = "SQL_USER_PASSWORD_HASH"

# force ssl on all connections defined in trust_auth_cidr_addresses and md5_auth_cidr_addresses
postgresql['hostssl'] = true

Any client or GitLab service which will connect over the network will need to provide the values of sql_user for the username, and password provided to the configuration when connecting to the PostgreSQL server. They must also be in the network block provided to md5_auth_cidr_addresses

Configure GitLab Rails block

To configure the gitlab-rails application to connect to the PostgreSQL database over the network, several settings must be configured:

  • db_host: Needs to be set to the IP address of the database sever. If this is on the same instance as the PostgreSQL service, this can be 127.0.0.1 and will not require password authentication.
  • db_port: Sets the port on the PostgreSQL server to connect to, and must be set if db_host is set.
  • db_username: Configures the username with which to connect to PostgreSQL. This defaults to gitlab.
  • db_password: Must be provided if connecting to PostgreSQL over TCP/IP, and from an instance in the postgresql['md5_auth_cidr_addresses'] block from settings above. This is not required if you are connecting to 127.0.0.1 and have configured postgresql['trust_auth_cidr_addresses'] to include it.
gitlab_rails['db_host'] = '127.0.0.1'
gitlab_rails['db_port'] = 5432
gitlab_rails['db_username'] = "gitlab"
gitlab_rails['db_password'] = "securesqlpassword"

Apply and restart services

After making the previous changes, an administrator should run gitlab-ctl reconfigure. If you experience any issues in regards to the service not listening on TCP, try directly restarting the service with gitlab-ctl restart postgresql.

Some included scripts of the Omnibus package (such as gitlab-psql) expect the connections to PostgreSQL to be handled over the UNIX socket, and may not function properly. You can enable TCP/IP without disabling UNIX sockets.

Enabling PostgreSQL WAL (Write Ahead Log) Archiving

By default, WAL archiving of the packaged PostgreSQL isn’t enabled. Consider the following when seeking to enable WAL archiving:

  • The WAL level needs to be ‘replica’ or higher (9.6+ options are minimal, replica, or logical)
  • Increasing the WAL level will increase the amount of storage consumed in regular operations

To enable WAL Archiving:

  1. Edit /etc/gitlab/gitlab.rb:

    # Replication settings
    postgresql['sql_replication_user'] = "gitlab_replicator"
    postgresql['wal_level'] = "replica"
        ...
        ...
    # Backup/Archive settings
    postgresql['archive_mode'] = "on"
    postgresql['archive_command'] = "/your/wal/archiver/here"
    postgresql['archive_timeout'] = "60"
    
  2. Reconfigure GitLab for the changes to take effect. This will result in a database restart.

Store PostgreSQL data in a different directory

By default, everything is stored under /var/opt/gitlab/postgresql, controlled by the postgresql['dir'] attribute.

This consists of:

  • The database socket will be /var/opt/gitlab/postgresql/.s.PGSQL.5432. This is controlled by postgresql['unix_socket_directory'].
  • The gitlab-psql system user will have its HOME directory set to this. This is controlled by postgresql['home'].
  • The actual data will be stored in /var/opt/gitlab/postgresql/data.

To change the location of the PostgreSQL data

Caution: If you have an existing database, you need to move the data to the new location first.
Caution: This is an intrusive operation. It cannot be done without downtime on an existing installation
  1. If this is an existing installation, stop GitLab: gitlab-ctl stop.
  2. Update postgresql['dir'] to the desired location.
  3. Run gitlab-ctl reconfigure.
  4. Start GitLab gitlab-ctl start.

Upgrade packaged PostgreSQL server

Omnibus GitLab provides the gitlab-ctl pg-upgrade command to update the packaged PostgreSQL server to a later version (if one is included in the package). This updates PostgreSQL to the default shipped version during package upgrades, unless specifically opted out.

Before upgrading: It’s important that you fully read this section before running any commands. For single node installations, this upgrade needs downtime, as the database must be down while the upgrade is being performed. The length of time depends on the size of your database. To avoid downtime, it’s possible to upgrade to a new database using Slony.
Tip: If you encounter any problems during upgrade, raise an issue with a full description at the Omnibus GitLab issue tracker.

To upgrade the PostgreSQL version, be sure that:

  • You’re running the latest version of GitLab, and it’s working.
  • If you recently upgraded, you ran sudo gitlab-ctl reconfigure successfully before you proceed.
  • You have sufficient disk space for two copies of your database. Do not attempt to upgrade unless you have enough free space available.

    • Check your database size using sudo du -sh /var/opt/gitlab/postgresql/data (or update to your database path).
    • Check the space available using sudo df -h. If the partition where the database resides doesn’t have enough space, pass the argument --tmp-dir $DIR to the command. GitLab 13.3 includes an available disk space check and aborts the upgrade if the requirements aren’t met.

After you confirm that the above checklist is satisfied, you can proceed with the upgrade:

sudo gitlab-ctl pg-upgrade
Tip: pg-upgrade can take arguments; for example, you can set the timeout for the execution of the underlying commands (--timeout=1d2h3m4s5ms). Run gitlab-ctl pg-upgrade -h to see the full list.

gitlab-ctl pg-upgrade performs the following steps:

  1. Checks to ensure the database is in a known good state.
  2. Checks if there’s enough free disk space and aborts otherwise. You can skip this by appending the --skip-disk-check flag.
  3. Shuts down the existing database, any unnecessary services, and enables the GitLab deploy page.
  4. Changes the symlinks in /opt/gitlab/embedded/bin/ for PostgreSQL to point to the newer version of the database.
  5. Creates a new directory containing a new, empty database with a locale matching the existing database.
  6. Uses the pg_upgrade tool to copy the data from the old database to the new database.
  7. Moves the old database out of the way.
  8. Moves the new database to the expected location.
  9. Calls sudo gitlab-ctl reconfigure to do the required configuration changes and starts the new database server.
  10. Starts the remaining services and removes the deploy page.
  11. If any errors are detected during this process, it reverts to the old version of the database.

After the upgrade is complete, verify that everything is working as expected.

After you have verified that your GitLab instance is running correctly, you can clean up the old database files:

sudo rm -rf /var/opt/gitlab/postgresql/data.<old_version>
sudo rm -f /var/opt/gitlab/postgresql-version.old

You can find details of PostgreSQL versions shipped with various GitLab versions in PostgreSQL versions shipped with Omnibus GitLab.

Opt out of automatic PostgreSQL upgrades

To opt out of automatic PostgreSQL upgrade during GitLab package upgrades, run:

sudo touch /etc/gitlab/disable-postgresql-upgrade

GitLab 13.3 and later

As of GitLab 13.3, PostgreSQL 11.7 and 12.3 are both shipped with Omnibus. During a package upgrade, the database isn’t upgraded to PostgreSQL 12. If you want to upgrade to PostgreSQL 12, you must do it manually:

sudo gitlab-ctl pg-upgrade -V 12

PostgreSQL 12 isn’t supported on Geo deployments and is planned for the future releases. The fault-tolerant PostgreSQL 12 deployment is possible only by using Patroni. Repmgr is no longer supported for PostgreSQL 12.

GitLab 12.10 and later

The default PostgreSQL version is set to 11.x, and an upgrade of the database is done on package upgrades for installs that are not using repmgr or Geo.

The upgrade is skipped in any of the following cases:

  • You’re running the database in high availability using repmgr.
  • Your database nodes are part of GitLab Geo configuration.
  • You have specifically opted out.

To upgrade PostgreSQL on installs with HA or Geo, see Packaged PostgreSQL deployed in an HA/Geo Cluster.

GitLab 12.8 and later

As of GitLab 12.8, PostgreSQL 9.6.17, 10.12, and 11.7 are shipped with Omnibus GitLab.

During package upgrades (unless opted out) and when user manually runs gitlab-ctl pg-upgrade, omnibus-gitlab will still be attempting to upgrade the database only to 10.x, while 11.x will be available for users to manually upgrade to. To manually update PostgreSQL to version 11.x , the pg-upgrade command has to be passed with a version argument (-V or --target-version):

sudo gitlab-ctl pg-upgrade -V 11

GitLab 12.0 and later

As of GitLab 12.0, PostgreSQL 9.6.11 and 10.7 are shipped with Omnibus GitLab.

On upgrades, we will be upgrading the database to 10.7 unless specifically opted out, as previously described.

GitLab 11.11 and later

As of GitLab 11.11, PostgreSQL 9.6.X and 10.7 are shipped with Omnibus GitLab.

Fresh installs will be getting PostgreSQL 10.7 while GitLab package upgrades will retain the existing version of PostgreSQL. Users can manually upgrade to the 10.7 using the pg-upgrade command as previously mentioned.

Revert packaged PostgreSQL server to previous version

Warning: This operation will revert your current database, including its data, to its state before your last upgrade. Be sure to create a backup before attempting to downgrade your packaged PostgreSQL database.

On GitLab versions which ship multiple PostgreSQL versions, users can downgrade an already upgraded PostgreSQL version to the earlier version using the gitlab-ctl revert-pg-upgrade command. This command also supports the -V flag to specify a target version for scenarios where more than two PostgreSQL versions are shipped in the package (for example: GitLab 12.8 where PostgreSQL 9.6.x, 10.x, and 11.x are shipped).

If the target version is not specified, it will use the version in /var/opt/gitlab/postgresql-version.old if available. Otherwise, it falls back to the default version shipped with GitLab.

On other GitLab versions which ship only one PostgreSQL version, you can’t downgrade your PostgreSQL version. You must downgrade GitLab to an older version for this.

Connecting to the bundled PostgreSQL database

If you need to connect to the bundled PostgreSQL database and are using the default Omnibus GitLab database configuration, you can connect as the application user:

sudo gitlab-rails dbconsole

or as a PostgreSQL superuser:

sudo gitlab-psql -d gitlabhq_production

Using a non-packaged PostgreSQL database management server

By default, GitLab is configured to use the PostgreSQL server that’s included in Omnibus GitLab. You can also reconfigure it to use an external instance of PostgreSQL.

Caution: If you are using non-packaged PostgreSQL server, you need to make sure that PostgreSQL is set up according to the database requirements document.
  1. Edit /etc/gitlab/gitlab.rb:

    # Disable the built-in Postgres
    postgresql['enable'] = false
    
    # Fill in the connection details for database.yml
    gitlab_rails['db_adapter'] = 'postgresql'
    gitlab_rails['db_encoding'] = 'utf8'
    gitlab_rails['db_host'] = '127.0.0.1'
    gitlab_rails['db_port'] = 5432
    gitlab_rails['db_username'] = 'USERNAME'
    gitlab_rails['db_password'] = 'PASSWORD'
    

    Don’t forget to remove the # comment characters at the beginning of these lines.

    Note that:

    • /etc/gitlab/gitlab.rb should have file permissions 0600 because it contains plain-text passwords.
    • PostgreSQL allows to listen on multiple addresses

      If you use multiple addresses in gitlab_rails['db_host'], comma-separated, the first address in the list will be used for connection.

  2. Reconfigure GitLab for the changes to take effect.

  3. Seed the database.

UNIX socket configuration for non-packaged PostgreSQL

If you want to use your system’s PostgreSQL server (installed on the same system as GitLab) instead of the one bundled with GitLab, you can do so by using a UNIX socket:

  1. Edit /etc/gitlab/gitlab.rb:

    # Disable the built-in Postgres
    postgresql['enable'] = false
    
    # Fill in the connection details for database.yml
    gitlab_rails['db_adapter'] = 'postgresql'
    gitlab_rails['db_encoding'] = 'utf8'
    # The path where the socket lives
    gitlab_rails['db_host'] = '/var/run/postgresql/'
    
  2. Reconfigure GitLab for the changes to take effect:

    sudo gitlab-ctl-reconfigure
    

Configuring SSL

Require SSL

  1. Add the following to /etc/gitlab/gitlab.rb:

    postgresql['db_sslmode'] = 'require'
    
  2. Reconfigure GitLab to apply the configuration changes.

  3. Restart PostgreSQL for the changes to take effect:

    gitlab-ctl restart postgresql
    

    If PostgreSQL fails to start, check the logs (for example, /var/log/gitlab/postgresql/current) for more details.

Require SSL and verify server certificate against CA bundle

PostgreSQL can be configured to require SSL and verify the server certificate against a CA bundle to prevent spoofing. The CA bundle that’s specified in gitlab_rails['db_sslrootcert'] must contain both the root and intermediate certificates.

  1. Add the following to /etc/gitlab/gitlab.rb:

    gitlab_rails['db_sslmode'] = "verify-full"
    gitlab_rails['db_sslrootcert'] = "your-full-ca-bundle.pem"
    

    If you are using Amazon RDS for your PostgreSQL server, ensure you download and use the combined CA bundle for gitlab_rails['db_sslrootcert']. More information on this can be found in the using SSL/TLS to Encrypt a Connection to a DB Instance article on AWS.

  2. Reconfigure GitLab to apply the configuration changes.

  3. Restart PostgreSQL for the changes to take effect:

    gitlab-ctl restart postgresql
    

    If PostgreSQL fails to start, check the logs (for example, /var/log/gitlab/postgresql/current) for more details.

Backup and restore a non-packaged PostgreSQL database

When using the Rake backup create and restore task, GitLab will attempt to use the packaged pg_dump command to create a database backup file and the packaged psql command to restore a backup. This will only work if they are the correct versions. Check the versions of the packaged pg_dump and psql:

/opt/gitlab/embedded/bin/pg_dump --version
/opt/gitlab/embedded/bin/psql --version

If these versions are different from your non-packaged external PostgreSQL, you will need to install tools that match your database version and then follow the steps below. There are multiple ways to install PostgreSQL client tools. See https://www.postgresql.org/download/ for options.

Once the correct psql and pg_dump tools are available on your system, follow these steps, using the correct path to the location you installed the new tools:

  1. Add symbolic links to the non-packaged versions:

    ln -s /path/to/new/pg_dump /path/to/new/psql /opt/gitlab/bin/
    
  2. Check the versions:

    /opt/gitlab/bin/pg_dump --version
    /opt/gitlab/bin/psql --version
    

    They should now be the same as your non-packaged external PostgreSQL.

After this is done, ensure that the backup and restore tasks are using the correct executables by running both the backup and restore tasks.

Upgrade a non-packaged PostgreSQL database

Before proceeding with the upgrade, note the following:

  • Before upgrading, review the GitLab and PostgreSQL version compatibility table to determine your upgrade path. When using GitLab backup or restore, you must keep the same version of GitLab; first upgrade PostgreSQL, and then GitLab.
  • The backup and restore Rake task can be used to back up and restore the database to a later version of PostgreSQL.
  • If configuring a version number whose binaries are unavailable on the file system, GitLab/Rails uses the default database’s version binaries (default as per GitLab and PostgreSQL version compatibility table).
  • If you’re using Amazon RDS and are seeing extremely high (near 100%) CPU utilization following a major version upgrade (for example, from 10.x to 11.x), running an ANALYZE VERBOSE; query may be necessary to recreate query plans and reduce CPU utilization on the database server(s). Amazon recommends this as part of a major version upgrade.

The following example demonstrates upgrading from a database host running PostgreSQL 10 to another database host running PostgreSQL 11 and incurs downtime:

  1. Spin up a new PostgreSQL 11 database server that’s set up according to the database requirements.

  2. Ensure that the compatible versions of pg_dump and pg_restore are being used on the GitLab Rails instance. To amend GitLab configuration, edit /etc/gitlab/gitlab.rb and specify the value of postgresql['version']:

     postgresql['version'] = 11
    
Note: Connecting to PostgreSQL 12 (alongside with amending postgresql['version'] = 12) breaks the GitLab Backup/Restore functionality unless the v12 client binaries are available on the file system. More on this topic can be found under backup and restore a non-packaged database. This problem with missing 12 client binaries is partially resolved in GitLab 13.3, where PostgreSQL 12 is shipped with Omnibus GitLab, and it’s being tackled in the support for PostgreSQL 12 epic.
  1. Reconfigure GitLab:

    sudo gitlab-ctl reconfigure
    
  2. Stop GitLab (note that this step will cause downtime):

    sudo gitlab-ctl stop
    
  3. Run the backup Rake task using the SKIP options to back up only the database. Make a note of the backup file name; you’ll use it later to restore.

    sudo gitlab-backup create SKIP=repositories,uploads,builds,artifacts,lfs,pages,registry
    
  4. Shutdown the PostgreSQL 10 database host.

  5. Edit /etc/gitlab/gitlab.rb and update the gitlab_rails['db_host'] setting to point to the PostgreSQL database 11 host.

  6. Reconfigure GitLab:

    sudo gitlab-ctl reconfigure
    
  7. Restore the database using the database backup file created earlier, and be sure to answer no when asked “This task will now rebuild the authorized_keys file”:

    sudo gitlab-backup restore BACKUP=<database-backup-filename>
    
  8. Start GitLab:

    sudo gitlab-ctl start
    

Seed the database (fresh installs only)

Caution: This is a destructive command; do not run it on an existing database.

Omnibus GitLab will not seed your external database. Run the following command to import the schema and create the first administration user:

# Remove 'sudo' if you are the 'git' user
sudo gitlab-rake gitlab:setup

If you want to specify a password for the default root user, specify the initial_root_password setting in /etc/gitlab/gitlab.rb before running the gitlab:setup command above:

gitlab_rails['initial_root_password'] = 'nonstandardpassword'

If you want to specify the initial registration token for shared GitLab Runners, specify the initial_shared_runners_registration_token setting in /etc/gitlab/gitlab.rb before running the gitlab:setup command:

gitlab_rails['initial_shared_runners_registration_token'] = 'token'

Pin the packaged PostgreSQL version (fresh installs only)

Note: GitLab 13.0 only ships with PostgreSQL 11. PostgreSQL 12 is only available from GitLab 13.3 onward. PostgreSQL 12 is not supported on Geo deployments and is planned for the future releases. The fault-tolerant PostgreSQL 12 deployment is only possible using Patroni. Repmgr is no longer supported for PostgreSQL 12.

Omnibus GitLab will initialize PostgreSQL with the default version.

To initialize PostgreSQL with a non-default version, you can set postgresql['version'] to the major version one of the packaged PostgreSQL versions prior to the initial reconfigure. For example, starting GitLab 13.3 you can use postgresql['version'] = 12 to opt in to use PostgreSQL 12.

Caution: Setting postgresql['version'] while using the Omnibus packaged PostgreSQL after the initial reconfigure will throw errors about the data directory being initialized on a different version of PostgreSQL. If this is encountered, see Revert packaged PostgreSQL server to previous version.

Troubleshooting

Set default_transaction_isolation into read committed

If you see errors similar to the following in your production/sidekiq log:

ActiveRecord::StatementInvalid PG::TRSerializationFailure: ERROR:  could not serialize access due to concurrent update

Chances are your database’s default_transaction_isolation configuration is not in line with GitLab application requirement. You can check this configuration by connecting to your PostgreSQL database and run SHOW default_transaction_isolation;. GitLab application expects read committed to be configured.

This default_transaction_isolation configuration is set in your postgresql.conf file. You will need to restart/reload the database once you changed the configuration. This configuration comes by default in the packaged PostgreSQL server included with Omnibus GitLab.

Application Settings for the Database

Disabling automatic database migration

If you have multiple GitLab servers sharing a database, you will want to limit the number of nodes that are performing the migration steps during reconfiguration.

Edit /etc/gitlab/gitlab.rb:

# Enable or disable automatic database migrations
gitlab_rails['auto_migrate'] = false

Don’t forget to remove the # comment characters at the beginning of this line.

/etc/gitlab/gitlab.rb should have file permissions 0600 because it contains plain-text passwords.

The next time a reconfigure is triggered, the migration steps will not be performed.

Setting client statement_timeout

The amount of time that Rails will wait for a database transaction to complete before timing out can now be adjusted with the gitlab_rails['db_statement_timeout'] setting. By default, this setting is not used.

Edit /etc/gitlab/gitlab.rb:

gitlab_rails['db_statement_timeout'] = 45000

In this case the client statement_timeout is set to 45 seconds. The value is specified in milliseconds.

Setting connection timeout

The amount of time that Rails will wait for a PostgreSQL connection attempt to succeed before timing out can be adjusted with the gitlab_rails['db_connect_timeout'] setting. By default, this setting is not used:

  1. Edit /etc/gitlab/gitlab.rb:

    gitlab_rails['db_connect_timeout'] = 5
    
  2. Reconfigure GitLab:

    sudo gitlab-ctl reconfigure
    

In this case the client connect_timeout is set to 5 seconds. The value is specified in seconds. A minimum value of 2 seconds applies. Setting this to <= 0 or not specifying the setting at all disables the timeout.

Setting tcp controls

The Rails PostgreSQL adapter provides a series of TCP connection controls that may be tuned to improve performance. Consult the PostgreSQL upstream documentation for more information about each parameter.

Omnibus sets no defaults for these values and instead uses the defaults provided by PostgreSQL adapter. Override them in gitlab.rb using the parameters noted in the table below and then run gitlab-ctl reconfigure.

PostgreSQL parameter gitlab.rb parameter
keepalives gitlab_rails[‘db_keepalives’]
keepalives_idle gitlab_rails[‘db_keepalives_idle’]
keepalives_interval gitlab_rails[‘db_keepalives_interval’]
keepalives_count gitlab_rails[‘db_keepalives_count’]
tcp_user_timeout gitlab_rails[‘db_tcp_user_timeout’]

Automatic database reindexing

Introduced in GitLab 13.5.

Warning: This is an experimental feature that isn’t enabled by default.

Recreates database indexes in the background (called “reindexing”). This can be used to remove bloated space that has accumulated in indexes and helps to maintain healthy and efficient indexes.

The reindexing task can be started regularly through a cronjob. To configure the cronjob, gitlab_rails['database_reindexing']['enable'] should be set to true.

In a multi-node environment, this feature should only be enabled on an application host. The reindexing process cannot go through PgBouncer, it has to have a direct database connection.

By default, this starts the cronjob every hour during weekends (likely a low-traffic time) only.

You can change the schedule by refining the following settings:

  1. Edit /etc/gitlab/gitlab.rb:

    gitlab_rails['database_reindexing']['hour'] = '*'
    gitlab_rails['database_reindexing']['minute'] = 0
    gitlab_rails['database_reindexing']['month'] = '*'
    gitlab_rails['database_reindexing']['day_of_month'] = '*'
    gitlab_rails['database_reindexing']['day_of_week'] = '0,6'
    
  2. Reconfigure GitLab:

    sudo gitlab-ctl reconfigure
    

Packaged PostgreSQL deployed in an HA/Geo Cluster

Upgrading a GitLab HA cluster

Note: As of GitLab 13.3, PostgreSQL 12 is shipped with Omnibus GitLab. However, the current support for is limited to single database node installation. Fault-tolerant PostgreSQL deployments, and Geo installations are not supported, but planned for the future releases.

The following instructions are valid for a fault tolerant setup with repmgr. To upgrade PostgreSQL version in a Patroni cluster see Upgrading PostgreSQL major version in a Patroni cluster.

If PostgreSQL is configured for high availability, pg-upgrade should be run on all the nodes running PostgreSQL. Other nodes can be skipped, but must be running the same GitLab version as the database nodes.

Follow the steps below to upgrade the database nodes:

  1. Secondary nodes must be upgraded before the primary node.
    1. On the secondary nodes, edit /etc/gitlab/gitlab.rb to include the following:
    # Replace X with value of number of db nodes + 1
    postgresql['max_replication_slots'] = X
    
    1. Run gitlab-ctl reconfigure to update the configuration.
    2. Run sudo gitlab-ctl restart postgresql to get PostgreSQL restarted with the new configuration.
    3. On running pg-upgrade on a PostgreSQL secondary node, the node will be removed from the cluster.
    4. Once all the secondary nodes are upgraded using pg-upgrade, the user will be left with a single-node cluster that has only the primary node.
    5. pg-upgrade, on secondary nodes will not update the existing data to match the new version, as that data will be replaced by the data from primary node. It will, however move the existing data to a backup location.
  2. Once all secondary nodes are upgraded, run pg-upgrade on primary node.
    1. On the primary node, edit /etc/gitlab/gitlab.rb to include the following:
    # Replace X with value of number of db nodes + 1
    postgresql['max_replication_slots'] = X
    
    1. Run gitlab-ctl reconfigure to update the configuration.
    2. Run sudo gitlab-ctl restart postgresql to get PostgreSQL restarted with the new configuration.
    3. On a primary node, pg-upgrade will update the existing data to match the new PostgreSQL version.
  3. Recreate the secondary nodes by running the following command on each of them

    gitlab-ctl repmgr standby setup MASTER_NODE_NAME
    
  4. Check if the repmgr cluster is back to the original state

    gitlab-ctl repmgr cluster show
    

Troubleshooting upgrades in an HA cluster

If at some point, the bundled PostgreSQL had been running on a node before upgrading to an HA setup, the old data directory may remain. This will cause gitlab-ctl reconfigure to downgrade the version of the PostgreSQL utilities it uses on that node. Move (or remove) the directory to prevent this:

  • mv /var/opt/gitlab/postgresql/data/ /var/opt/gitlab/postgresql/data.$(date +%s)

If you encounter the following error when recreating the secondary nodes with gitlab-ctl repmgr standby setup MASTER_NODE_NAME, ensure that you have postgresql['max_replication_slots'] = X, replacing X with value of number of db nodes + 1, is included in /etc/gitlab/gitlab.rb:

pg_basebackup: could not create temporary replication slot "pg_basebackup_12345": ERROR:  all replication slots are in use
HINT:  Free one or increase max_replication_slots.

Upgrading a Geo instance

Since Geo depends on PostgreSQL streaming replication by default, there are additional considerations when upgrading GitLab and/or when upgrading PostgreSQL described below.

Caution: If you are running a Geo installation using PostgreSQL 9.6.x, upgrade to GitLab 12.4 or newer. Older versions were affected by an issue that could cause upgrades of the PostgreSQL database to fail on the secondary. See Disabling automatic PostgreSQL upgrades to workaround the issue. This issue is now fixed.

Caveats when upgrading PostgreSQL with Geo

Caution: When using Geo, upgrading PostgreSQL requires downtime on all secondaries.

When using Geo, upgrading PostgreSQL requires downtime on all secondaries because it requires re-initializing PostgreSQL replication to Geo secondaries. This is due to the way PostgreSQL streaming replication works. Re-initializing replication copies all data from the primary again, so it can take a long time depending mostly on the size of the database and available bandwidth. For example, at a transfer speed of 30 Mbps, and a database size of 100 GB, resynchronization could take approximately 8 hours. See PostgreSQL documentation for more.

Disabling automatic PostgreSQL upgrades

From GitLab 12.1 through GitLab 12.9, GitLab package upgrades try to upgrade PostgreSQL to version 10.x. In GitLab 12.10 and later, upgrades of PostgreSQL don’t happen in an unattended manner when using Geo.

Before upgrading to GitLab 12.1 through GitLab 12.9, we strongly recommend disabling unattended upgrades of PostgreSQL and manually upgrading PostgreSQL separately from upgrading the GitLab package to avoid any unintended downtime.

You can disable unattended upgrades of PostgreSQL by running the following on all nodes running postgresql or geo-postgresql:

sudo touch /etc/gitlab/disable-postgresql-upgrade

How to upgrade PostgreSQL when using Geo

To upgrade PostgreSQL, you will need the name of the replication slot, and the replication user’s password.

  1. Find the name of the existing replication slot on the Geo primary’s database node, run:

    sudo gitlab-psql -qt -c 'select slot_name from pg_replication_slots'
    

    If you can’t find your slot_name here, or there is no output returned, your Geo secondaries may not be healthy. In that case, make sure the secondaries are healthy and replication is working.

  2. Gather the replication user’s password. It was set while setting up Geo in Step 1. Configure the primary server.

  3. Manually upgrade PostgreSQL on the Geo primary. Run on the Geo primary’s database node:

    sudo gitlab-ctl pg-upgrade
    

    Wait for the primary database to finish upgrading before beginning the following step, so the secondary can remain ready as a backup. Afterward, you can upgrade the tracking database in parallel with the secondary database.

  4. Manually upgrade PostgreSQL on the Geo secondaries. Run on the Geo secondary database and also on the tracking database:

    sudo gitlab-ctl pg-upgrade
    
  5. Restart the database replication on the Geo secondary database using the command:

    sudo gitlab-ctl replicate-geo-database --slot-name=SECONDARY_SLOT_NAME --host=PRIMARY_HOST_NAME
    

    You will be prompted for the replication user’s password of the primary. Replace SECONDARY_SLOT_NAME with the slot name retrieved from the first step above.

  6. Reconfigure GitLab on the Geo secondary database to update the pg_hba.conf file. This is needed because replicate-geo-database replicates the primary’s file to the secondary.

  7. Restart puma (or unicorn), sidekiq, and geo-logcursor.

    sudo gitlab-ctl hup puma
    sudo gitlab-ctl restart sidekiq
    sudo gitlab-ctl restart geo-logcursor
    
  8. Navigate to https://your_primary_server/admin/geo/nodes and ensure that all nodes are healthy.