Skip to content
Snippets Groups Projects
database.md 51.84 KiB
stage: Systems
group: Distribution
info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://handbook.gitlab.com/handbook/product/ux/technical-writing/#assignments

Database settings (FREE SELF)

GitLab supports only the PostgreSQL database management system.

Thus you have two options for database servers to use with a Linux package installation:

  • Use the packaged PostgreSQL server included with the Linux package installation (no configuration required, recommended).
  • Use an external PostgreSQL server.

Using the PostgreSQL database service shipped with the Linux package

Reconfigure and PostgreSQL restarts

Linux package installations normally restart any service on reconfigure if configuration settings for that service were changed in the gitlab.rb file. PostgreSQL is unique in that some of its settings take effect with a reload (HUP), while others require PostgreSQL to be restarted. Because administrators frequently want more control over exactly when PostgreSQL is restarted, Linux package installations are configured to do a reload of PostgreSQL on reconfigure, and not a restart. This means that if you modify any PostgreSQL setting that requires a restart, you will need to restart PostgreSQL manually after you reconfigure.

The GitLab config template identifies which PostgreSQL settings require a restart and which require only a reload. You can also run a query against your database to determine if any individual setting requires a restart. Start a database console with sudo gitlab-psql, then replace <setting name> in the following query with the setting you are changing:

SELECT name,setting FROM pg_settings WHERE context = 'postmaster' AND name = '<setting name>';

If changing the setting will require a restart, the query will return the name of the setting and the current value of that setting in the running PostgreSQL instance.

Automatic restart when the PostgreSQL version changes

By default, Linux package installations automatically restart PostgreSQL when the underlying version changes, as suggested by the upstream documentation. This behavior can be controlled using the auto_restart_on_version_change setting available for postgresql and geo-postgresql.

To disable automatic restarts when the PostgreSQL version changes:

  1. Edit /etc/gitlab/gitlab.rb and add the following line:

    # For PostgreSQL/Patroni
    postgresql['auto_restart_on_version_change'] = false
    
    # For Geo PostgreSQL
    geo_postgresql['auto_restart_on_version_change'] = false
  2. Reconfigure GitLab:

    sudo gitlab-ctl reconfigure

NOTE: It is highly recommended to restart PostgreSQL when the underlying version changes, to avoid errors like the one related to loading necessary libraries.

Configuring SSL

Linux package installations automatically enable 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, Linux package installations use the embedded certificate bundle in /opt/gitlab/embedded/ssl/certs/cacert.pem. This isn't required for self-signed certificates.

A 10-year self-signed certificate and private key are generated by a Linux package installation for use. If you'd prefer to use a CA-signed certificate or replace this with your own self-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. Linux package installations manage the permissions of the files for you, but if the paths are customized, you must ensure that the gitlab-psql can access the directory in 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.

The ssl_cert_file, ssl_key_file, and ssl_ca_file files direct PostgreSQL to where on the filesystem to find the certificate, key, and bundle. These changes are applied to postgresql.conf. The directives internal_certificate and internal_key are used to populate the contents of these files. The contents can be added directly or loaded from file as shown in the following example.

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'] = File.read('/custom/path/to/server.crt')
    postgresql['internal_key'] = File.read('/custom/path/to/server.key')

    Relative paths will be rooted in 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:

    gitlab_rails['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:

In GitLab 14.2 and later:

sudo gitlab-rails dbconsole --database main

In GitLab 14.1 and earlier:

sudo 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

For the rails client to connect to the server, you will need a certificate and key with the commonName set to gitlab, which 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 listens to. The default is 5432.
  • md5_auth_cidr_addresses: A list of CIDR address blocks that are allowed to connect to the server, after authentication with a password.
  • trust_auth_cidr_addresses: A list of CIDR address blocks that 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.
  1. Edit /etc/gitlab/gitlab.rb:

    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' (single-quoted to avoid shell interpolation) 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
  2. Reconfigure GitLab and restart PostrgreSQL:

    sudo gitlab-ctl reconfigure
    sudo gitlab-ctl restart postgresql

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 server. 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.
  1. Edit /etc/gitlab/gitlab.rb:

    gitlab_rails['db_host'] = '127.0.0.1'
    gitlab_rails['db_port'] = 5432
    gitlab_rails['db_username'] = "gitlab"
    gitlab_rails['db_password'] = "securesqlpassword"
  2. Reconfigure GitLab and restart PostrgreSQL:

    sudo gitlab-ctl reconfigure
    sudo gitlab-ctl restart postgresql

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 Linux 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

WARNING: If you have an existing database, you need to move the data to the new location first.

WARNING: 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

The Linux package 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 GitLab to a newer version, refer to the version-specific changes of the Linux package to see either:

  • When a database version has changed.
  • When an upgrade is warranted.

WARNING: 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.

NOTE: If you encounter any problems during the 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 that supports your current version of PostgreSQL.

  • 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 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 and above include 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

To upgrade to a specific PostgreSQL version, use the -V flag to append the version. For example, to upgrade to PostgreSQL 14:

sudo gitlab-ctl pg-upgrade -V 14

NOTE: 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 abort otherwise. You can skip this by appending the --skip-disk-check flag.
  3. Shuts down the existing database and any unnecessary services, and enables GitLab to 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. Runs ANALYZE to generate database statistics.
  11. Starts the remaining services and removes the deploy page.
  12. 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.

If there was an error in the output while running the ANALYZE step, your upgrade will still be working but will have poor database performance until the database statistics are generated. Use gitlab-psql to determine whether ANALYZE should be run manually:

sudo gitlab-psql -c "SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE last_analyze IS NULL AND last_autoanalyze IS NULL;"

You can run ANALYZE manually if the query above returned any rows:

sudo gitlab-psql -c 'SET statement_timeout = 0; ANALYZE VERBOSE;'

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 the Linux package.

Opt out of automatic PostgreSQL upgrades

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

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

Revert packaged PostgreSQL server to the 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).

To specify a target PostgreSQL version of 12:

gitlab-ctl revert-pg-upgrade -V 12

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 that ship only one PostgreSQL version, you can't downgrade your PostgreSQL version. You must downgrade GitLab to an older version for this.

Configuring multiple database connections

The gitlab:db:decomposition:connection_status Rake task was introduced in GitLab 15.11.

In GitLab 16.0, GitLab defaults to using two database connections that point to the same PostgreSQL database.

Before upgrading to GitLab 16.0, check that the PostgreSQL max_connections setting is high enough so that more than 50% of available connections show as being unused. For example, if max_connections is set to 100 and you see 75 connections in use, you must increase max_connections to at least 150 before upgrading because after upgrading, the in-use connections will double to 150.

You can verify this by running the following Rake task:

sudo gitlab-rake gitlab:db:decomposition:connection_status

If the task indicates that max_connections is high enough, then you can proceed with the upgrade.

If, for any reason, you wish to remain on single connection, and you are upgrading from GitLab 15.11 or earlier to GitLab 16.0, or switch back to single database connection update this setting in /etc/gitlab/gitlab.rb:

gitlab_rails['databases']['ci']['enable'] = false

In a multi-node environment, this setting should be updated on all Rails and Sidekiq nodes.

Using a non-packaged PostgreSQL database management server

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

WARNING: If you are using a 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 listening 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 the 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