<NME> database.md
<BEF> ---
stage: Systems
group: Distribution
info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/product/ux/technical-writing/#assignments
---

# Database settings **(FREE SELF)**

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-a-non-packaged-postgresql-database-management-server).

## Using the PostgreSQL Database Service shipped with Omnibus GitLab

### Reconfigure and PostgreSQL restarts

Omnibus normally restarts any service on reconfigure if config settings for that service were
changed in the `gitlab.rb` file. PostgreSQL is unique in that some of its settings will 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, Omnibus has been 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](https://gitlab.com/gitlab-org/omnibus-gitlab/blob/master/files/gitlab-config-template/gitlab.rb.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:

```sql
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, Omnibus automatically restarts PostgreSQL when the underlying
version changes, as suggested by the [upstream documentation](https://www.postgresql.org/docs/13/upgrading.html).
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:

   ```ruby
   # For PostgreSQL/Patroni
   postgresql['auto_restart_on_version_change'] = false

   # For Geo PostgreSQL
   geo_postgresql['auto_restart_on_version_change'] = false
   ```

1. Reconfigure GitLab:

   ```shell
   sudo gitlab-ctl reconfigure
   ```

NOTE:
It is highly recommended to restart PostgreSQL when underlying version changes,
to avoid errors like the [one related to loading necessary libraries](#could-not-load-library-plpgsqlso).

### 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](https://www.postgresql.org/docs/13/auth-pg-hba-conf.html).

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 10-year self-signed certificate and private key will be generated by Omnibus GitLab 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. 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](https://www.postgresql.org/docs/11/ssl-tcp.html).

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

   ```ruby
   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).

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.

1. Restart PostgreSQL for the changes to take effect:

   ```shell
   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`:

    ```ruby
    gitlab_rails['db_sslmode'] = 'require'
    ```

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.

1. Restart PostgreSQL for the changes to take effect:

   ```shell
   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`:

   ```ruby
   postgresql['ssl'] = 'off'
   ```

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.

1. Restart PostgreSQL for the changes to take effect:

   ```shell
   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](https://gitlab.com/gitlab-org/gitlab/-/issues/341210):

```shell
sudo gitlab-rails dbconsole --database main
```

In GitLab 14.1 and earlier:

```shell
sudo gitlab-rails dbconsole
```

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

```plaintext
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:

```sql
SELECT * FROM pg_stat_ssl;
```

For example:

```plaintext
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.
1. 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
1. 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`)
   1. The key file should be owned by the database user, and its permissions should be `0400`
   1. 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`.

1. Ensure the following is set in `gitlab.rb`:

   ```ruby
   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.

1. 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`

   ```ruby
   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'
   ```

1. Run `gitlab-ctl reconfigure` for the rails client to use the new settings
1. Follow the steps in [Verifying that SSL is being used](#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.

```ruby
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.

```ruby
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`:

   ```ruby
   # 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"
   ```

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) 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`.
1. Update `postgresql['dir']` to the desired location.
1. Run `gitlab-ctl reconfigure`.
1. 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](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)
during package upgrades, unless specifically [opted out](#opt-out-of-automatic-postgresql-upgrades).

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. To avoid downtime, it's possible to upgrade to a new
database [using Slony](https://docs.gitlab.com/ee/update/upgrading_postgresql_using_slony.html).

NOTE:
If you encounter any problems during upgrade, raise an issue with a full
description at the [Omnibus GitLab issue tracker](https://gitlab.com/gitlab-org/omnibus-gitlab).

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 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 and above 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:

```shell
sudo gitlab-ctl pg-upgrade
```

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.
1. Checks if there's enough free disk space and aborts otherwise. You can skip this by appending the `--skip-disk-check` flag.
1. Shuts down the existing database, any unnecessary services, and enables the GitLab deploy page.
1. Changes the symlinks in `/opt/gitlab/embedded/bin/` for PostgreSQL to point to the newer version of the database.
1. Creates a new directory containing a new, empty database with a locale matching the existing database.
1. Uses the `pg_upgrade` tool to copy the data from the old database to the new database.
1. Moves the old database out of the way.
1. Moves the new database to the expected location.
1. Calls `sudo gitlab-ctl reconfigure` to do the required configuration changes and starts the new database server.
1. Runs `ANALYZE` to generate database statistics.
1. Starts the remaining services and removes the deploy page.
1. 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:

```shell
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:

```shell
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:

```shell
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](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html).

#### Opt out of automatic PostgreSQL upgrades

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

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

#### GitLab 15.0 and later

As of GitLab 15.0, new installations will default to PostgreSQL 13.

Existing single database node instances can update manually via:

```shell
sudo gitlab-ctl pg-upgrade -V 13
```

Until PostgreSQL 12 is removed, administrators may
[pin the PostgreSQL version](#pin-the-packaged-postgresql-version-fresh-installs-only)
if needed for compatibility or test environment reasons.

[Fault tolerant and Geo installations require additional steps and planning](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#upgrading-postgresql-major-version-in-a-patroni-cluster).

## Troubleshooting

### Connecting to the bundled PostgreSQL database

If you need to connect to the bundled PostgreSQL database and are
As of GitLab 13.7, new installations will default to PostgreSQL 12.

Existing instances can update manually via:

```shell
sudo gitlab-ctl pg-upgrade -V 12
```

Instances with fault-tolerant PostgreSQL 11 deployments will need to [upgrade to Patroni](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#switching-from-repmgr-to-patroni) first, before upgrading to PostgreSQL 12.

Geo installations support manual upgrades to PostgreSQL 12, see [Packaged PostgreSQL deployed in an Geo Cluster](#upgrading-a-geo-instance).

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

```shell
sudo gitlab-ctl pg-upgrade -V 12
```

PostgreSQL 12 isn't supported on Geo deployments and is [planned](https://gitlab.com/groups/gitlab-org/-/epics/2374)
for the future releases. The [fault-tolerant PostgreSQL 12 deployment](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html)
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](#opt-out-of-automatic-postgresql-upgrades).

To upgrade PostgreSQL on installs with HA or Geo, see [Packaged PostgreSQL deployed in an HA/Geo Cluster](#packaged-postgresql-deployed-in-an-hageo-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`):

```shell
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).

To specify a target PostgreSQL version of 12:

```shell
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 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:

In [GitLab 14.2 and later](https://gitlab.com/gitlab-org/gitlab/-/issues/341210):

```shell
sudo gitlab-rails dbconsole --database main
```

In GitLab 14.1 and earlier:

```shell
sudo gitlab-rails dbconsole
```

or as a PostgreSQL superuser:

```shell
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.

WARNING:
If you are using non-packaged PostgreSQL server, you need to make
sure that PostgreSQL is set up according to the [database requirements document](https://docs.gitlab.com/ee/install/requirements.html#database).

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

   ```ruby
   # 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](https://www.postgresql.org/docs/11/runtime-config-connection.html)

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

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) for the changes to take effect.

1. [Seed the database](#seed-the-database-fresh-installs-only).

### 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`:

   ```ruby
   # 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/'
   ```

1. Reconfigure GitLab for the changes to take effect:

   ```ruby
   sudo gitlab-ctl-reconfigure
   ```

### Configuring SSL

#### Require SSL

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

   ```ruby
   gitlab_rails['db_sslmode'] = 'require'
   ```

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.

1. Restart PostgreSQL for the changes to take effect:

   ```shell
   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`:

   ```ruby
   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](https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem)
   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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html)
   article on AWS.

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.

1. Restart PostgreSQL for the changes to take effect:

   ```shell
   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](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system), 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`:

```shell
/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 may encounter the following error output when attempting to run a [Rake backup create task](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system):

```plaintext
Dumping PostgreSQL database gitlabhq_production ... pg_dump: error: server version: 13.3; pg_dump version: 12.6
pg_dump: error: aborting because of server version mismatch
```

In this example, the error occurs on GitLab 14.1 when using PostgreSQL version 13.3, instead of the [default shipped PostgreSQL version](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html) of 12.6.

In this case, 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:

   ```shell
   ln -s /path/to/new/pg_dump /path/to/new/psql /opt/gitlab/bin/
   ```

1. Check the versions:

   ```shell
   /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](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system) and
[restore](https://docs.gitlab.com/ee/raketasks/backup_restore.html#restore-a-previously-created-backup) tasks.

### Upgrade a non-packaged PostgreSQL database

You can upgrade the external database as suggested by the provider after stopping all the processes that are connected to the database (Puma, Sidekiq):

```shell
sudo gitlab-ctl stop puma
sudo gitlab-ctl stop sidekiq
```

Before proceeding with the upgrade, note the following:

- Before upgrading, review the [GitLab and PostgreSQL version compatibility table](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)
  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](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system)
  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](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)).
- 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html)
  as part of a major version upgrade.

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

1. Spin up a new PostgreSQL 12 database server that's set up according to the [database requirements](https://docs.gitlab.com/ee/install/requirements.html#database).

1. 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']`:

    ```ruby
    postgresql['version'] = 12
    ```

1. Reconfigure GitLab:

   ```shell
   sudo gitlab-ctl reconfigure
   ```

1. Stop GitLab (note that this step causes downtime):

   ```shell
   sudo gitlab-ctl stop
   ```

WARNING:
The backup command requires [additional parameters](https://docs.gitlab.com/ee/raketasks/backup_restore.html#backup-and-restore-for-installations-using-pgbouncer)
when your installation is using PgBouncer.

1. 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.

   ```shell
   sudo gitlab-backup create SKIP=repositories,uploads,builds,artifacts,lfs,pages,registry
   ```

1. Shutdown the PostgreSQL 11 database host.

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

1. Reconfigure GitLab:

   ```shell
   sudo gitlab-ctl reconfigure
   ```

   WARNING:
   The backup command requires [additional parameters](https://docs.gitlab.com/ee/raketasks/backup_restore.html#backup-and-restore-for-installations-using-pgbouncer)
   when your installation is using PgBouncer.

1. 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":

   ```shell
   # Use the backup timestamp https://docs.gitlab.com/ee/raketasks/backup_restore.html#backup-timestamp
   sudo gitlab-backup restore BACKUP=<backup-timestamp>
   ```

1. Start GitLab:

   ```shell
   sudo gitlab-ctl start
   ```

### Seed the database (fresh installs only)

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

```shell
# 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:

```ruby
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:

```ruby
gitlab_rails['initial_shared_runners_registration_token'] = 'token'
```

### Pin the packaged PostgreSQL version (fresh installs only)

NOTE:
GitLab 14.1 and onward shipped with both Postgres 12 and Postgres 13.
GitLab 14.0 only ships with PostgreSQL 12. GitLab 13.3 and onward shipped both Postgres 11 and Postgres 12.
GitLab 13.0 through 13.2 only shipped with PostgreSQL 11.

Omnibus GitLab will initialize PostgreSQL with the [default version](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html).

To initialize PostgreSQL with a non-default version, you can set `postgresql['version']` to the major version one of
the [packaged PostgreSQL versions](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html) prior to the initial reconfigure.
For example, in GitLab 15.0 you can use `postgresql['version'] = 12` to use PostgreSQL 12 instead of the default of PostgreSQL 13.

WARNING:
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](#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:

```plaintext
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.

### Could not load library `plpgsql.so`

You might see errors similar to the following while running Database migrations
or in the PostgreSQL/Patroni logs:

```plaintext
ERROR:  could not load library "/opt/gitlab/embedded/postgresql/12/lib/plpgsql.so": /opt/gitlab/embedded/postgresql/12/lib/plpgsql.so: undefined symbol: EnsurePortalSnapshotExists
```

This error is caused due to not restarting PostgreSQL after the underlying
version changed. To fix this error:

1. Run one of the following commands:

   ```shell
   # For PostgreSQL
   sudo gitlab-ctl restart postgresql

   # For Patroni
   sudo gitlab-ctl restart patroni

   # For Geo PostgreSQL
   sudo gitlab-ctl restart geo-postgresql
   ```

1. Reconfigure GitLab:

   ```shell
   sudo gitlab-ctl reconfigure
   ```

## 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` to append:

```ruby
# Enable or disable automatic database migrations
# on all hosts except the designated deploy node
gitlab_rails['auto_migrate'] = false
```

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

The next time hosts carrying the above configuration are reconfigured, the migration steps are not performed.

To avoid schema related post-upgrade errors, the host marked as [the deploy node](https://docs.gitlab.com/ee/update/zero_downtime.html#multi-node--ha-deployment) must have `gitlab_rails['auto_migrate'] = true` during upgrades.

### 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`:

```ruby
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`:

   ```ruby
   gitlab_rails['db_connect_timeout'] = 5
   ```

1. Reconfigure GitLab:

   ```shell
   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](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-KEEPALIVES).

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](https://gitlab.com/gitlab-org/gitlab/-/issues/249662) 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`:

   ```shell
   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'
   ```

1. Reconfigure GitLab:

   ```shell
   sudo gitlab-ctl reconfigure
   ```

## Packaged PostgreSQL deployed in an HA/Geo Cluster

### Upgrading a GitLab HA cluster

To upgrade the PostgreSQL version in a Patroni cluster see [Upgrading PostgreSQL major version in a Patroni cluster](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#upgrading-postgresql-major-version-in-a-patroni-cluster).

### Upgrading a GitLab HA Repmgr cluster

NOTE:
If you are upgrading to PostgreSQL 12, you need to switch from Repmgr to Patroni first see [Switching from Repmgr to Patroni](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#switching-from-repmgr-to-patroni).

These instructions are provided for upgrading a older GitLab cluster to PostgreSQL 11, when using Repmgr.

If [PostgreSQL is configured for high availability](https://docs.gitlab.com/ee/administration/postgresql/index.html),
`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:

   ```shell
   # Replace X with value of number of db nodes + 1
   postgresql['max_replication_slots'] = X
    ```

   1. Run `gitlab-ctl reconfigure` to update the configuration.
   1. Run `sudo gitlab-ctl restart postgresql` to get PostgreSQL restarted with the new configuration.
   1. On running `pg-upgrade` on a PostgreSQL secondary node, the node will be removed
      from the cluster.
   1. 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.
   1. `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.
1. 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:

   ```shell
   # Replace X with value of number of db nodes + 1
   postgresql['max_replication_slots'] = X
    ```

   1. Run `gitlab-ctl reconfigure` to update the configuration.
   1. Run `sudo gitlab-ctl restart postgresql` to get PostgreSQL restarted with the new configuration.
   1. On a primary node, `pg-upgrade` will update the existing data to match
      the new PostgreSQL version.
1. Recreate the secondary nodes by running the following command on each of them

   ```shell
   gitlab-ctl repmgr standby setup MASTER_NODE_NAME
   ```

1. Check if the repmgr cluster is back to the original state

   ```shell
   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`:

```shell
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.

WARNING:
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](https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/4692) that could cause upgrades of the PostgreSQL database to fail on the secondary. See [Disabling automatic PostgreSQL upgrades](#disabling-automatic-postgresql-upgrades) to workaround the issue. This issue is now fixed.

#### Caveats when upgrading PostgreSQL with Geo

WARNING:
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](https://www.postgresql.org/docs/11/pgupgrade.html)
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`:

```shell
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:

   ```shell
   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](https://docs.gitlab.com/ee/administration/geo/replication/troubleshooting.html#check-the-health-of-the-secondary-node).

1. Gather the replication user's password. It was set while setting up Geo in
   [Step 1. Configure the primary server](https://docs.gitlab.com/ee/administration/geo/setup/database.html#step-1-configure-the-primary-server).

1. Manually upgrade PostgreSQL on the Geo primary. Run on the Geo primary's
   database node:

   ```shell
   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**.

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

   ```shell
   sudo gitlab-ctl pg-upgrade
   ```

1. Restart the database replication on the Geo **secondary database** using the
   command:

   ```shell
   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.

1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) 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.

1. Restart `puma`, `sidekiq`, and `geo-logcursor`.

   ```shell
   sudo gitlab-ctl hup puma
   sudo gitlab-ctl restart sidekiq
   sudo gitlab-ctl restart geo-logcursor
   ```

1. Navigate to `https://your_primary_server/admin/geo/nodes` and ensure that all nodes are healthy.

<MSG> Merge branch 'docs-add-db-troubleshooting-transaction_isolation' into 'master'

default_transaction_isolation info for db settings

See merge request gitlab-org/omnibus-gitlab!3359
<DFF> @@ -536,6 +536,24 @@ If you need to run an older version of PostgreSQL, you must downgrade GitLab to
 
 ## 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 GitLab Omnibus.
+
 ### Connecting to the bundled PostgreSQL database
 
 If you need to connect to the bundled PostgreSQL database and are

{'commit': "diff --git a/doc/settings/database.md b/doc/settings/database.md\nindex aac4ac09c..358e28410 100644\n--- a/doc/settings/database.md\n+++ b/doc/settings/database.md\n@@ -536,6 +536,24 @@ If you need to run an older version of PostgreSQL, you must downgrade GitLab to\n \n ## Troubleshooting\n \n+### Set `default_transaction_isolation` into `read committed`\n+\n+If you see errors similar to the following in your production/sidekiq log: \n+\n+```\n+ActiveRecord::StatementInvalid PG::TRSerializationFailure: ERROR:  could not serialize access due to concurrent update\n+```\n+\n+Chances are your database's `default_transaction_isolation` configuration is not\n+in line with GitLab application requirement. You can check this configuration by\n+connecting to your PostgreSQL database and run `SHOW default_transaction_isolation;`.\n+GitLab application expects `read committed` to be configured.\n+\n+This `default_transaction_isolation` configuration is set in your \n+`postgresql.conf` file. You will need to restart/reload the database once you \n+changed the configuration. This configuration comes by default in the packaged \n+PostgreSQL server included with GitLab Omnibus.\n+\n ### Connecting to the bundled PostgreSQL database\n \n If you need to connect to the bundled PostgreSQL database and are\n", 'commit_subject': "Merge branch 'docs-add-db-troubleshooting-transaction_isolation' into 'master'", 'repo_name': 'gitlabhq/omnibus-gitlab', 'committer': {'name': 'Evan Read', 'email': '7ea72eb9c05adae46f82bdbf290256c688486a4e@gitlab.com', 'time_sec': '1563515896', 'tz_offset': '0', 'date': {'seconds': '1563515896'}}, 'commit_tree': '1c46951753e4523586119eb2f090299c49b74fac', 'commit_message': "Merge branch 'docs-add-db-troubleshooting-transaction_isolation' into 'master'\n\ndefault_transaction_isolation info for db settings\n\nSee merge request gitlab-org/omnibus-gitlab!3359", 'language_name': 'Shell', 'license': 'apache-2.0', 'hunks': "@@ -536,6 +536,24 @@ If you need to run an older version of PostgreSQL, you must downgrade GitLab to\n \n ## Troubleshooting\n \n+### Set `default_transaction_isolation` into `read committed`\n+\n+If you see errors similar to the following in your production/sidekiq log: \n+\n+```\n+ActiveRecord::StatementInvalid PG::TRSerializationFailure: ERROR:  could not serialize access due to concurrent update\n+```\n+\n+Chances are your database's `default_transaction_isolation` configuration is not\n+in line with GitLab application requirement. You can check this configuration by\n+connecting to your PostgreSQL database and run `SHOW default_transaction_isolation;`.\n+GitLab application expects `read committed` to be configured.\n+\n+This `default_transaction_isolation` configuration is set in your \n+`postgresql.conf` file. You will need to restart/reload the database once you \n+changed the configuration. This configuration comes by default in the packaged \n+PostgreSQL server included with GitLab Omnibus.\n+\n ### Connecting to the bundled PostgreSQL database\n \n If you need to connect to the bundled PostgreSQL database and are\n", 'addition_count': 18, 'deletion_count': 0, 'src_file': 'a/doc/settings/database.md', 'tgt_file': 'b/doc/settings/database.md', 'file_extension': '.md', 'before_file': '---\nstage: Systems\ngroup: Distribution\ninfo: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/product/ux/technical-writing/#assignments\n---\n\n# Database settings **(FREE SELF)**\n\nGitLab supports only PostgreSQL database management system.\n\nThus you have two options for database servers to use with Omnibus GitLab:\n\n- Use the packaged PostgreSQL server included with Omnibus GitLab (no\n  configuration required, recommended).\n- Use an [external PostgreSQL server](#using-a-non-packaged-postgresql-database-management-server).\n\n## Using the PostgreSQL Database Service shipped with Omnibus GitLab\n\n### Reconfigure and PostgreSQL restarts\n\nOmnibus normally restarts any service on reconfigure if config settings for that service were\nchanged in the `gitlab.rb` file. PostgreSQL is unique in that some of its settings will take effect\nwith a reload (HUP), while others require PostgreSQL to be restarted. Because administrators\nfrequently want more control over exactly when PostgreSQL is restarted, Omnibus has been configured\nto do a reload of PostgreSQL on reconfigure, and not a restart. This means that if you modify any\nPostgreSQL setting that requires a restart, you will need to restart PostgreSQL manually after you\nreconfigure.\n\nThe [GitLab config template](https://gitlab.com/gitlab-org/omnibus-gitlab/blob/master/files/gitlab-config-template/gitlab.rb.template)\nidentifies which PostgreSQL settings require a restart and which require only a reload. You can also\nrun a query against your database to determine if any individual setting requires a restart. Start a\ndatabase console with `sudo gitlab-psql`, then replace `<setting name>` in the following query\nwith the setting you are changing:\n\n```sql\nSELECT name,setting FROM pg_settings WHERE context = \'postmaster\' AND name = \'<setting name>\';\n```\n\nIf changing the setting will require a restart, the query will return the name of the setting and the current value\nof that setting in the running PostgreSQL instance.\n\n#### Automatic restart when the PostgreSQL version changes\n\nBy default, Omnibus automatically restarts PostgreSQL when the underlying\nversion changes, as suggested by the [upstream documentation](https://www.postgresql.org/docs/13/upgrading.html).\nThis behavior can be controlled using the `auto_restart_on_version_change` setting\navailable for `postgresql` and `geo-postgresql`.\n\nTo disable automatic restarts when the PostgreSQL version changes:\n\n1. Edit `/etc/gitlab/gitlab.rb` and add the following line:\n\n   ```ruby\n   # For PostgreSQL/Patroni\n   postgresql[\'auto_restart_on_version_change\'] = false\n\n   # For Geo PostgreSQL\n   geo_postgresql[\'auto_restart_on_version_change\'] = false\n   ```\n\n1. Reconfigure GitLab:\n\n   ```shell\n   sudo gitlab-ctl reconfigure\n   ```\n\nNOTE:\nIt is highly recommended to restart PostgreSQL when underlying version changes,\nto avoid errors like the [one related to loading necessary libraries](#could-not-load-library-plpgsqlso).\n\n### Configuring SSL\n\nOmnibus automatically enables SSL on the PostgreSQL server, but it will accept\nboth encrypted and unencrypted connections by default. Enforcing SSL requires\nusing the `hostssl` configuration in `pg_hba.conf`. For more details, see the\n[`pg_hba.conf` documentation](https://www.postgresql.org/docs/13/auth-pg-hba-conf.html).\n\nSSL support depends on the following files:\n\n- The public SSL certificate for the database (`server.crt`).\n- The corresponding private key for the SSL certificate (`server.key`).\n- A root certificate bundle that validates the server\'s certificate (`root.crt`).\n  By default, Omnibus GitLab uses the embedded certificate bundle in\n  `/opt/gitlab/embedded/ssl/certs/cacert.pem`. This isn\'t required for\n  self-signed certificates.\n\nA 10-year self-signed certificate and private key will be generated by Omnibus GitLab for use. If you\'d\nprefer to use a CA-signed certificate or replace this with your own self-signed certificate, use the following steps.\n\nNote that the location of these files can be configurable, but the private key\n_must_ be readable by the `gitlab-psql` user. Omnibus manages the permissions of\nthe files for you, but if the paths are customized, you *must* ensure that the\n`gitlab-psql` can access the directory the files are placed in.\n\nFor more details, see the [PostgreSQL documentation](https://www.postgresql.org/docs/11/ssl-tcp.html).\n\nNote that `server.crt` and `server.key` may be different from the default SSL\ncertificates used to access GitLab. For example, suppose the external hostname\nof your database is `database.example.com`, and your external GitLab hostname\nis `gitlab.example.com`. You will either need a wildcard certificate for\n`*.example.com` or two different SSL certificates.\n\nAfter you have these files, enable SSL:\n\n1. Edit `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   postgresql[\'ssl_cert_file\'] = \'/custom/path/to/server.crt\'\n   postgresql[\'ssl_key_file\'] = \'/custom/path/to/server.key\'\n   postgresql[\'ssl_ca_file\'] = \'/custom/path/to/bundle.pem\'\n   postgresql[\'internal_certificate\'] = "-----BEGIN CERTIFICATE-----\n   ...base64-encoded certificate...\n   -----END CERTIFICATE-----\n   "\n   postgresql[\'internal_key\'] = "-----BEGIN RSA PRIVATE KEY-----\n   ...base64-encoded private key...\n   -----END RSA PRIVATE KEY-----\n   "\n   ```\n\n   Relative paths will be rooted from the PostgreSQL data directory\n   (`/var/opt/gitlab/postgresql/data` by default).\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.\n\n1. Restart PostgreSQL for the changes to take effect:\n\n   ```shell\n   gitlab-ctl restart postgresql\n   ```\n\n   If PostgreSQL fails to start, check the logs (for example,\n   `/var/log/gitlab/postgresql/current`) for more details.\n\n#### Require SSL\n\n1. Add the following to `/etc/gitlab/gitlab.rb`:\n\n    ```ruby\n    gitlab_rails[\'db_sslmode\'] = \'require\'\n    ```\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.\n\n1. Restart PostgreSQL for the changes to take effect:\n\n   ```shell\n   gitlab-ctl restart postgresql\n   ```\n\n   If PostgreSQL fails to start, check the logs (for example,\n   `/var/log/gitlab/postgresql/current`) for more details.\n\n#### Disabling SSL\n\n1. Add the following to `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   postgresql[\'ssl\'] = \'off\'\n   ```\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.\n\n1. Restart PostgreSQL for the changes to take effect:\n\n   ```shell\n   gitlab-ctl restart postgresql\n   ```\n\n   If PostgreSQL fails to start, check the logs (for example,\n   `/var/log/gitlab/postgresql/current`) for more details.\n\n#### Verifying that SSL is being used\n\nTo determine whether SSL is being used by clients, you can run:\n\nIn [GitLab 14.2 and later](https://gitlab.com/gitlab-org/gitlab/-/issues/341210):\n\n```shell\nsudo gitlab-rails dbconsole --database main\n```\n\nIn GitLab 14.1 and earlier:\n\n```shell\nsudo gitlab-rails dbconsole\n```\n\nAt startup, you should see a banner as the following:\n\n```plaintext\npsql (9.6.5)\nSSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)\nType "help" for help.\n```\n\nTo determine if clients are using SSL, issue this SQL query:\n\n```sql\nSELECT * FROM pg_stat_ssl;\n```\n\nFor example:\n\n```plaintext\ngitlabhq_production=> select * from pg_stat_ssl;\n pid  | ssl | version |         cipher         | bits | compression |  clientdn\n------+-----+---------+------------------------+------+-------------+------------\n  384 | f   |         |                        |      |             |\n  386 | f   |         |                        |      |             |\n  998 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n  933 | f   |         |                        |      |             |\n 1003 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n 1016 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n 1022 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n 1211 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n 1214 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n 1213 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n 1215 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n 1252 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           |\n 1280 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | f           | /CN=gitlab\n  382 | f   |         |                        |      |             |\n  381 | f   |         |                        |      |             |\n  383 | f   |         |                        |      |             |\n(16 rows)\n```\n\n1. Rows that have `t` listed under the `ssl` column are enabled.\n1. Rows that have a value in the `clientdn` are using the `cert` authentication method\n\n#### Configure SSL client authentication\n\nClient SSL certificates can be used to authenticate to the database server. Creating the certificates\nis beyond the scope of `omnibus-gitlab`. But users who have an existing SSL certificate management solution\ncan use this.\n\n##### Configure the database server\n\n1. Create a certificate and key for the server, the common name should equal the DNS name of the server\n1. Copy the server certificate, key, and CA file to the PostgreSQL server, and ensure the permissions are correct\n   1. The certificate should be owned by the database user (default: `gitlab-psql`)\n   1. The key file should be owned by the database user, and its permissions should be `0400`\n   1. The CA file should be owned by the database user, and its permissions should be `0400`\n\n   NOTE:\n   Don\'t use the filenames `server.crt` or `server.key` for these files. These\n   filenames are reserved for the internal use of `omnibus-gitlab`.\n\n1. Ensure the following is set in `gitlab.rb`:\n\n   ```ruby\n   postgresql[\'ssl_cert_file\'] = \'PATH_TO_CERTIFICATE\'\n   postgresql[\'ssl_key_file\'] = \'PATH_TO_KEY_FILE\'\n   postgresql[\'ssl_ca_file\'] = \'PATH_TO_CA_FILE\'\n   postgresql[\'listen_address\'] = \'IP_ADDRESS\'\n   postgresql[\'cert_auth_addresses\'] = {\n   \'IP_ADDRESS\' => {\n     \'database\' => \'gitlabhq_production\',\n     \'user\' => \'gitlab\'\n   }\n   ```\n\n   Set `listen_address` as the IP address of the server that the clients will use\n   to connect to the database.\n   Ensure `cert_auth_addresses` contains a list of IP addresses, and the\n   databases and users that are allowed to connect to the database. You can use\n   CIDR notation when specifying the key for `cert_auth_addresses` to\n   incorporate an IP address range.\n\n1. Run `gitlab-ctl reconfigure`, and then `gitlab-ctl restart postgresql` for\n   the new settings to take effect.\n\n#### Configure the Rails client\n\nIn 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.\n\n1. Configure `gitlab.rb`\n\n   ```ruby\n   gitlab_rails[\'db_host\'] = \'IP_ADDRESS_OR_HOSTNAME_OF_DATABASE_SERVER\'\n   gitlab_rails[\'db_sslcert\'] = \'PATH_TO_CERTIFICATE_FILE\'\n   gitlab_rails[\'db_sslkey\'] = \'PATH_TO_KEY_FILE\'\n   gitlab_rails[\'db_rootcert\'] = \'PATH_TO_CA_FILE\'\n   ```\n\n1. Run `gitlab-ctl reconfigure` for the rails client to use the new settings\n1. Follow the steps in [Verifying that SSL is being used](#verifying-that-ssl-is-being-used) to ensure the authentication is working.\n\n### Configure packaged PostgreSQL server to listen on TCP/IP\n\nThe packaged PostgreSQL server can be configured to listen for TCP/IP connections,\nwith the caveat that some non-critical scripts expect UNIX sockets and may misbehave.\n\nTo configure the use of TCP/IP for the database service, make changes to both\nthe `postgresql` and `gitlab_rails` sections of `gitlab.rb`.\n\n#### Configure PostgreSQL block\n\nThe following settings are affected in the `postgresql` block:\n\n- `listen_address`: Controls the address on which PostgreSQL will listen.\n- `port`: Controls the port on which PostgreSQL will listen, and _must be set_\n  if `listen_address` is.\n- `md5_auth_cidr_addresses`: A list of CIDR address blocks which are allowed to\n  connect to the server, after authentication with a password.\n- `trust_auth_cidr_addresses`: A list of CIDR address blocks which are allowed\n  to connect to the server, without authentication of any kind. _Be very careful\n  with this setting._ It\'s suggested that this be limited to the loopback\n  address of `127.0.0.1/24` or even `127.0.0.1/32`.\n- `sql_user`: Controls the expected username for MD5 authentication. This\n  defaults to `gitlab`, and isn\'t a required setting.\n- `sql_user_password`: Sets the password that PostgreSQL will accept for MD5\n  authentication. Replace `securesqlpassword` in the following example with an\n  acceptable password.\n\n```ruby\npostgresql[\'listen_address\'] = \'0.0.0.0\'\npostgresql[\'port\'] = 5432\npostgresql[\'md5_auth_cidr_addresses\'] = %w()\npostgresql[\'trust_auth_cidr_addresses\'] = %w(127.0.0.1/24)\npostgresql[\'sql_user\'] = "gitlab"\n\n##! SQL_USER_PASSWORD_HASH can be generated using the command `gitlab-ctl pg-password-md5 gitlab`,\n##! where `gitlab` is the name of the SQL user that connects to GitLab.\npostgresql[\'sql_user_password\'] = "SQL_USER_PASSWORD_HASH"\n\n# force ssl on all connections defined in trust_auth_cidr_addresses and md5_auth_cidr_addresses\npostgresql[\'hostssl\'] = true\n```\n\nAny client or GitLab service which will connect over the network will need to\nprovide the values of `sql_user` for the username, and password provided to the\nconfiguration when connecting to the PostgreSQL server. They must also be in the\nnetwork block provided to `md5_auth_cidr_addresses`\n\n#### Configure GitLab Rails block\n\nTo configure the `gitlab-rails` application to connect to the PostgreSQL database\nover the network, several settings must be configured:\n\n- `db_host`: Needs to be set to the IP address of the database sever. If this is\n  on the same instance as the PostgreSQL service, this can be `127.0.0.1` and _will\n  not require_ password authentication.\n- `db_port`: Sets the port on the PostgreSQL server to connect to, and _must be set_\n  if `db_host` is set.\n- `db_username`: Configures the username with which to connect to PostgreSQL. This\n  defaults to `gitlab`.\n- `db_password`: Must be provided if connecting to PostgreSQL over TCP/IP, and from\n  an instance in the `postgresql[\'md5_auth_cidr_addresses\']` block from settings\n  above. This is not required if you are connecting to `127.0.0.1` and have configured\n  `postgresql[\'trust_auth_cidr_addresses\']` to include it.\n\n```ruby\ngitlab_rails[\'db_host\'] = \'127.0.0.1\'\ngitlab_rails[\'db_port\'] = 5432\ngitlab_rails[\'db_username\'] = "gitlab"\ngitlab_rails[\'db_password\'] = "securesqlpassword"\n```\n\n#### Apply and restart services\n\nAfter making the previous changes, an administrator should run `gitlab-ctl reconfigure`.\nIf you experience any issues in regards to the service not listening on TCP, try\ndirectly restarting the service with `gitlab-ctl restart postgresql`.\n\nSome included scripts of the Omnibus package (such as `gitlab-psql`) expect the\nconnections to PostgreSQL to be handled over the UNIX socket, and may not function\nproperly. You can enable TCP/IP without disabling UNIX sockets.\n\n### Enabling PostgreSQL WAL (Write Ahead Log) Archiving\n\nBy default, WAL archiving of the packaged PostgreSQL isn\'t enabled. Consider the\nfollowing when seeking to enable WAL archiving:\n\n- The WAL level needs to be \'replica\' or higher (9.6+ options are `minimal`,\n  `replica`, or `logical`)\n- Increasing the WAL level will increase the amount of storage consumed in\n  regular operations\n\nTo enable WAL Archiving:\n\n1. Edit `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   # Replication settings\n   postgresql[\'sql_replication_user\'] = "gitlab_replicator"\n   postgresql[\'wal_level\'] = "replica"\n       ...\n       ...\n   # Backup/Archive settings\n   postgresql[\'archive_mode\'] = "on"\n   postgresql[\'archive_command\'] = "/your/wal/archiver/here"\n   postgresql[\'archive_timeout\'] = "60"\n   ```\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) for the changes to take effect. This will result in a database restart.\n\n### Store PostgreSQL data in a different directory\n\nBy default, everything is stored under `/var/opt/gitlab/postgresql`, controlled\nby the `postgresql[\'dir\']` attribute.\n\nThis consists of:\n\n- The database socket will be `/var/opt/gitlab/postgresql/.s.PGSQL.5432`. This\n  is controlled by `postgresql[\'unix_socket_directory\']`.\n- The `gitlab-psql` system user will have its `HOME` directory set to this. This\n  is controlled by `postgresql[\'home\']`.\n- The actual data will be stored in `/var/opt/gitlab/postgresql/data`.\n\nTo change the location of the PostgreSQL data\n\nWARNING:\nIf you have an existing database, you need to move the data to the new location\nfirst.\n\nWARNING:\nThis is an intrusive operation. It cannot be done without downtime on an\nexisting installation\n\n1. If this is an existing installation, stop GitLab: `gitlab-ctl stop`.\n1. Update `postgresql[\'dir\']` to the desired location.\n1. Run `gitlab-ctl reconfigure`.\n1. Start GitLab `gitlab-ctl start`.\n\n### Upgrade packaged PostgreSQL server\n\nOmnibus GitLab provides the `gitlab-ctl pg-upgrade` command to update the\npackaged PostgreSQL server to a later version (if one is included in the\npackage). This updates PostgreSQL to the [default shipped version](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)\nduring package upgrades, unless specifically [opted out](#opt-out-of-automatic-postgresql-upgrades).\n\nWARNING:\nBefore upgrading, it\'s important that you fully read this section before running any commands. For\nsingle node installations, this upgrade needs downtime, as the database must be\ndown while the upgrade is being performed. The length of time depends on the\nsize of your database. To avoid downtime, it\'s possible to upgrade to a new\ndatabase [using Slony](https://docs.gitlab.com/ee/update/upgrading_postgresql_using_slony.html).\n\nNOTE:\nIf you encounter any problems during upgrade, raise an issue with a full\ndescription at the [Omnibus GitLab issue tracker](https://gitlab.com/gitlab-org/omnibus-gitlab).\n\nTo upgrade the PostgreSQL version, be sure that:\n\n- You\'re running the latest version of GitLab that supports your current version of PostgreSQL.\n- If you recently upgraded, you ran `sudo gitlab-ctl reconfigure` successfully\n  before you proceed.\n- You have sufficient disk space for two copies of your database. _Do not attempt\n  to upgrade unless you have enough free space available._\n\n  - Check your database size using `sudo du -sh /var/opt/gitlab/postgresql/data`\n    (or update to your database path).\n  - Check the space available using `sudo df -h`. If the partition where the\n    database resides doesn\'t have enough space, pass the argument `--tmp-dir $DIR`\n    to the command. GitLab 13.3 and above includes an available disk space check and aborts\n    the upgrade if the requirements aren\'t met.\n\nAfter you confirm that the above checklist is satisfied, you can proceed with\nthe upgrade:\n\n```shell\nsudo gitlab-ctl pg-upgrade\n```\n\nNOTE:\n`pg-upgrade` can take arguments; for example, you can set the timeout for the\nexecution of the underlying commands (`--timeout=1d2h3m4s5ms`). Run `gitlab-ctl pg-upgrade -h`\nto see the full list.\n\n`gitlab-ctl pg-upgrade` performs the following steps:\n\n1. Checks to ensure the database is in a known good state.\n1. Checks if there\'s enough free disk space and aborts otherwise. You can skip this by appending the `--skip-disk-check` flag.\n1. Shuts down the existing database, any unnecessary services, and enables the GitLab deploy page.\n1. Changes the symlinks in `/opt/gitlab/embedded/bin/` for PostgreSQL to point to the newer version of the database.\n1. Creates a new directory containing a new, empty database with a locale matching the existing database.\n1. Uses the `pg_upgrade` tool to copy the data from the old database to the new database.\n1. Moves the old database out of the way.\n1. Moves the new database to the expected location.\n1. Calls `sudo gitlab-ctl reconfigure` to do the required configuration changes and starts the new database server.\n1. Runs `ANALYZE` to generate database statistics.\n1. Starts the remaining services and removes the deploy page.\n1. If any errors are detected during this process, it reverts to the old version of the database.\n\nAfter the upgrade is complete, verify that everything is working as expected.\n\nIf there was an error in the output while running the `ANALYZE` step, your upgrade\nwill still be working, but will have poor database performance until the\ndatabase statistics are generated. Use `gitlab-psql` to determine whether `ANALYZE` should be run manually:\n\n```shell\nsudo gitlab-psql -c "SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE last_analyze IS NULL AND last_autoanalyze IS NULL;"\n```\n\nYou can run `ANALYZE` manually if the query above returned any rows:\n\n```shell\nsudo gitlab-psql -c \'SET statement_timeout = 0; ANALYZE VERBOSE;\'\n```\n\n_After you have verified that your GitLab instance is running correctly_, you\ncan clean up the old database files:\n\n```shell\nsudo rm -rf /var/opt/gitlab/postgresql/data.<old_version>\nsudo rm -f /var/opt/gitlab/postgresql-version.old\n```\n\nYou can find details of PostgreSQL versions shipped with various GitLab versions\nin [PostgreSQL versions shipped with Omnibus GitLab](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html).\n\n#### Opt out of automatic PostgreSQL upgrades\n\nTo opt out of automatic PostgreSQL upgrade during GitLab package upgrades, run:\n\n```shell\nsudo touch /etc/gitlab/disable-postgresql-upgrade\n```\n\n#### GitLab 15.0 and later\n\nAs of GitLab 15.0, new installations will default to PostgreSQL 13.\n\nExisting single database node instances can update manually via:\n\n```shell\nsudo gitlab-ctl pg-upgrade -V 13\n```\n\nUntil PostgreSQL 12 is removed, administrators may\n[pin the PostgreSQL version](#pin-the-packaged-postgresql-version-fresh-installs-only)\nif needed for compatibility or test environment reasons.\n\n[Fault tolerant and Geo installations require additional steps and planning](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#upgrading-postgresql-major-version-in-a-patroni-cluster).\n\n## Troubleshooting\n\n### Connecting to the bundled PostgreSQL database\n\nIf you need to connect to the bundled PostgreSQL database and are\nAs of GitLab 13.7, new installations will default to PostgreSQL 12.\n\nExisting instances can update manually via:\n\n```shell\nsudo gitlab-ctl pg-upgrade -V 12\n```\n\nInstances with fault-tolerant PostgreSQL 11 deployments will need to [upgrade to Patroni](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#switching-from-repmgr-to-patroni) first, before upgrading to PostgreSQL 12.\n\nGeo installations support manual upgrades to PostgreSQL 12, see [Packaged PostgreSQL deployed in an Geo Cluster](#upgrading-a-geo-instance).\n\n#### GitLab 13.3 and later\n\nAs of GitLab 13.3, PostgreSQL 11.7 and 12.3 are both shipped with Omnibus.\nDuring a package upgrade, the database isn\'t upgraded to PostgreSQL 12. If you\nwant to upgrade to PostgreSQL 12, you must do it manually:\n\n```shell\nsudo gitlab-ctl pg-upgrade -V 12\n```\n\nPostgreSQL 12 isn\'t supported on Geo deployments and is [planned](https://gitlab.com/groups/gitlab-org/-/epics/2374)\nfor the future releases. The [fault-tolerant PostgreSQL 12 deployment](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html)\nis possible only by using Patroni. Repmgr is no longer supported for\nPostgreSQL 12.\n\n#### GitLab 12.10 and later\n\nThe default PostgreSQL version is set to 11.x, and an upgrade of the database is\ndone on package upgrades for installs that are not using repmgr or Geo.\n\nThe upgrade is skipped in any of the following cases:\n\n- You\'re running the database in high availability using repmgr.\n- Your database nodes are part of GitLab Geo configuration.\n- You have specifically [opted out](#opt-out-of-automatic-postgresql-upgrades).\n\nTo upgrade PostgreSQL on installs with HA or Geo, see [Packaged PostgreSQL deployed in an HA/Geo Cluster](#packaged-postgresql-deployed-in-an-hageo-cluster).\n\n#### GitLab 12.8 and later\n\n**As of GitLab 12.8, PostgreSQL 9.6.17, 10.12, and 11.7 are shipped with\nOmnibus GitLab.**\n\nDuring package upgrades (unless opted out) and when user manually runs\n`gitlab-ctl pg-upgrade`, `omnibus-gitlab` will still be attempting to upgrade\nthe database only to 10.x, while 11.x will be available for users to manually\nupgrade to. To manually update PostgreSQL to version 11.x , the `pg-upgrade`\ncommand has to be passed with a version argument (`-V` or `--target-version`):\n\n```shell\nsudo gitlab-ctl pg-upgrade -V 11\n```\n\n#### GitLab 12.0 and later\n\n**As of GitLab 12.0, PostgreSQL 9.6.11 and 10.7 are shipped with Omnibus\nGitLab.**\n\nOn upgrades, we will be upgrading the database to 10.7 unless specifically opted\nout, as previously described.\n\n#### GitLab 11.11 and later\n\n**As of GitLab 11.11, PostgreSQL 9.6.X and 10.7 are shipped with Omnibus\nGitLab.**\n\nFresh installs will be getting PostgreSQL 10.7 while GitLab package upgrades\nwill retain the existing version of PostgreSQL. Users can manually upgrade to\nthe 10.7 using the `pg-upgrade` command as previously mentioned.\n\n### Revert packaged PostgreSQL server to previous version\n\nWARNING:\nThis operation will revert your current database, _including its data_, to its state\nbefore your last upgrade. Be sure to create a backup before attempting to downgrade\nyour packaged PostgreSQL database.\n\nOn GitLab versions which ship multiple PostgreSQL versions, users can downgrade\nan already upgraded PostgreSQL version to the earlier version using the `gitlab-ctl\nrevert-pg-upgrade` command. This command also supports the `-V` flag to specify\na target version for scenarios where more than two PostgreSQL versions are shipped in\nthe package (for example: GitLab 12.8 where PostgreSQL 9.6.x, 10.x, and 11.x are\nshipped).\n\nTo specify a target PostgreSQL version of 12:\n\n```shell\ngitlab-ctl revert-pg-upgrade -V 12\n```\n\nIf the target version is not specified, it will use the version in `/var/opt/gitlab/postgresql-version.old`\nif available. Otherwise, it falls back to the default version shipped with GitLab.\n\nOn other GitLab versions which ship only one PostgreSQL version, you can\'t\ndowngrade your PostgreSQL version. You must downgrade GitLab to an older version for\nthis.\n\n### Connecting to the bundled PostgreSQL database\n\nIf you need to connect to the bundled PostgreSQL database and are using the\ndefault Omnibus GitLab database configuration, you can connect as the\napplication user:\n\nIn [GitLab 14.2 and later](https://gitlab.com/gitlab-org/gitlab/-/issues/341210):\n\n```shell\nsudo gitlab-rails dbconsole --database main\n```\n\nIn GitLab 14.1 and earlier:\n\n```shell\nsudo gitlab-rails dbconsole\n```\n\nor as a PostgreSQL superuser:\n\n```shell\nsudo gitlab-psql -d gitlabhq_production\n```\n\n## Using a non-packaged PostgreSQL database management server\n\nBy default, GitLab is configured to use the PostgreSQL server that\'s included\nin Omnibus GitLab. You can also reconfigure it to use an external instance of\nPostgreSQL.\n\nWARNING:\nIf you are using non-packaged PostgreSQL server, you need to make\nsure that PostgreSQL is set up according to the [database requirements document](https://docs.gitlab.com/ee/install/requirements.html#database).\n\n1. Edit `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   # Disable the built-in Postgres\n   postgresql[\'enable\'] = false\n\n   # Fill in the connection details for database.yml\n   gitlab_rails[\'db_adapter\'] = \'postgresql\'\n   gitlab_rails[\'db_encoding\'] = \'utf8\'\n   gitlab_rails[\'db_host\'] = \'127.0.0.1\'\n   gitlab_rails[\'db_port\'] = 5432\n   gitlab_rails[\'db_username\'] = \'USERNAME\'\n   gitlab_rails[\'db_password\'] = \'PASSWORD\'\n   ```\n\n   Don\'t forget to remove the `#` comment characters at the beginning of these\n   lines.\n\n   Note that:\n\n   - `/etc/gitlab/gitlab.rb` should have file permissions `0600` because it contains\n     plain-text passwords.\n   - PostgreSQL allows to listen on [multiple addresses](https://www.postgresql.org/docs/11/runtime-config-connection.html)\n\n     If you use multiple addresses in `gitlab_rails[\'db_host\']`, comma-separated, the first address in the list will be used for connection.\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) for the changes to take effect.\n\n1. [Seed the database](#seed-the-database-fresh-installs-only).\n\n### UNIX socket configuration for non-packaged PostgreSQL\n\nIf you want to use your system\'s PostgreSQL server (installed on the same system\nas GitLab) instead of the one bundled with GitLab, you can do so by using a UNIX\nsocket:\n\n1. Edit `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   # Disable the built-in Postgres\n   postgresql[\'enable\'] = false\n\n   # Fill in the connection details for database.yml\n   gitlab_rails[\'db_adapter\'] = \'postgresql\'\n   gitlab_rails[\'db_encoding\'] = \'utf8\'\n   # The path where the socket lives\n   gitlab_rails[\'db_host\'] = \'/var/run/postgresql/\'\n   ```\n\n1. Reconfigure GitLab for the changes to take effect:\n\n   ```ruby\n   sudo gitlab-ctl-reconfigure\n   ```\n\n### Configuring SSL\n\n#### Require SSL\n\n1. Add the following to `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   gitlab_rails[\'db_sslmode\'] = \'require\'\n   ```\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.\n\n1. Restart PostgreSQL for the changes to take effect:\n\n   ```shell\n   gitlab-ctl restart postgresql\n   ```\n\n   If PostgreSQL fails to start, check the logs (for example,\n   `/var/log/gitlab/postgresql/current`) for more details.\n\n#### Require SSL and verify server certificate against CA bundle\n\nPostgreSQL can be configured to require SSL and verify the server certificate\nagainst a CA bundle to prevent spoofing.\nThe CA bundle that\'s specified in `gitlab_rails[\'db_sslrootcert\']` must contain\nboth the root and intermediate certificates.\n\n1. Add the following to `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   gitlab_rails[\'db_sslmode\'] = "verify-full"\n   gitlab_rails[\'db_sslrootcert\'] = "your-full-ca-bundle.pem"\n   ```\n\n   If you are using Amazon RDS for your PostgreSQL server, ensure you\n   download and use the [combined CA bundle](https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem)\n   for `gitlab_rails[\'db_sslrootcert\']`. More information on this can be found\n   in the [using SSL/TLS to Encrypt a Connection to a DB Instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html)\n   article on AWS.\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) to apply the configuration changes.\n\n1. Restart PostgreSQL for the changes to take effect:\n\n   ```shell\n   gitlab-ctl restart postgresql\n   ```\n\n   If PostgreSQL fails to start, check the logs (for example,\n   `/var/log/gitlab/postgresql/current`) for more details.\n\n### Backup and restore a non-packaged PostgreSQL database\n\nWhen using the [Rake backup create and restore task](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system), GitLab will\nattempt to use the packaged `pg_dump` command to create a database backup file\nand the packaged `psql` command to restore a backup. This will only work if\nthey are the correct versions. Check the versions of the packaged `pg_dump` and\n`psql`:\n\n```shell\n/opt/gitlab/embedded/bin/pg_dump --version\n/opt/gitlab/embedded/bin/psql --version\n```\n\nIf these versions are different from your non-packaged external PostgreSQL, you may encounter the following error output when attempting to run a [Rake backup create task](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system):\n\n```plaintext\nDumping PostgreSQL database gitlabhq_production ... pg_dump: error: server version: 13.3; pg_dump version: 12.6\npg_dump: error: aborting because of server version mismatch\n```\n\nIn this example, the error occurs on GitLab 14.1 when using PostgreSQL version 13.3, instead of the [default shipped PostgreSQL version](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html) of 12.6.\n\nIn this case, you will need to install tools that match your database version and then follow the\nsteps below. There are multiple ways to install PostgreSQL client tools. See\n<https://www.postgresql.org/download/> for options.\n\nOnce the correct `psql` and `pg_dump` tools are available on your system, follow\nthese steps, using the correct path to the location you installed the new tools:\n\n1. Add symbolic links to the non-packaged versions:\n\n   ```shell\n   ln -s /path/to/new/pg_dump /path/to/new/psql /opt/gitlab/bin/\n   ```\n\n1. Check the versions:\n\n   ```shell\n   /opt/gitlab/bin/pg_dump --version\n   /opt/gitlab/bin/psql --version\n   ```\n\n   They should now be the same as your non-packaged external PostgreSQL.\n\nAfter this is done, ensure that the backup and restore tasks are using the\ncorrect executables by running both the [backup](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system) and\n[restore](https://docs.gitlab.com/ee/raketasks/backup_restore.html#restore-a-previously-created-backup) tasks.\n\n### Upgrade a non-packaged PostgreSQL database\n\nYou can upgrade the external database as suggested by the provider after stopping all the processes that are connected to the database (Puma, Sidekiq):\n\n```shell\nsudo gitlab-ctl stop puma\nsudo gitlab-ctl stop sidekiq\n```\n\nBefore proceeding with the upgrade, note the following:\n\n- Before upgrading, review the [GitLab and PostgreSQL version compatibility table](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)\n  to determine your upgrade path. When using GitLab backup or restore, you\n  _must_ keep the same version of GitLab; first upgrade PostgreSQL, and then GitLab.\n- The [backup and restore Rake task](https://docs.gitlab.com/ee/raketasks/backup_restore.html#create-a-backup-of-the-gitlab-system)\n  can be used to back up and restore the database to a later version of PostgreSQL.\n- If configuring a version number whose binaries are unavailable on the file\n  system, GitLab/Rails uses the default database\'s version binaries (default as\n  per [GitLab and PostgreSQL version compatibility table](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)).\n- If you\'re using Amazon RDS and are seeing extremely high (near 100%) CPU\n  utilization following a major version upgrade (for example, from `10.x` to\n  `11.x`), running an `ANALYZE VERBOSE;` query may be necessary to recreate query\n  plans and reduce CPU utilization on the database server(s).\n  [Amazon recommends this](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html)\n  as part of a major version upgrade.\n\nThe following example demonstrates upgrading from a database host running PostgreSQL 11 to another database host running PostgreSQL 12 and incurs downtime:\n\n1. Spin up a new PostgreSQL 12 database server that\'s set up according to the [database requirements](https://docs.gitlab.com/ee/install/requirements.html#database).\n\n1. Ensure that the compatible versions of `pg_dump` and `pg_restore` are being\n   used on the GitLab Rails instance. To amend GitLab configuration, edit\n   `/etc/gitlab/gitlab.rb` and specify the value of `postgresql[\'version\']`:\n\n    ```ruby\n    postgresql[\'version\'] = 12\n    ```\n\n1. Reconfigure GitLab:\n\n   ```shell\n   sudo gitlab-ctl reconfigure\n   ```\n\n1. Stop GitLab (note that this step causes downtime):\n\n   ```shell\n   sudo gitlab-ctl stop\n   ```\n\nWARNING:\nThe backup command requires [additional parameters](https://docs.gitlab.com/ee/raketasks/backup_restore.html#backup-and-restore-for-installations-using-pgbouncer)\nwhen your installation is using PgBouncer.\n\n1. Run the backup Rake task using the SKIP options to back up only the database.\n   Make a note of the backup file name; you\'ll use it later to restore.\n\n   ```shell\n   sudo gitlab-backup create SKIP=repositories,uploads,builds,artifacts,lfs,pages,registry\n   ```\n\n1. Shutdown the PostgreSQL 11 database host.\n\n1. Edit `/etc/gitlab/gitlab.rb` and update the `gitlab_rails[\'db_host\']` setting\n   to point to the PostgreSQL database 12 host.\n\n1. Reconfigure GitLab:\n\n   ```shell\n   sudo gitlab-ctl reconfigure\n   ```\n\n   WARNING:\n   The backup command requires [additional parameters](https://docs.gitlab.com/ee/raketasks/backup_restore.html#backup-and-restore-for-installations-using-pgbouncer)\n   when your installation is using PgBouncer.\n\n1. Restore the database using the database backup file created earlier, and be\n   sure to answer **no** when asked "This task will now rebuild the authorized_keys file":\n\n   ```shell\n   # Use the backup timestamp https://docs.gitlab.com/ee/raketasks/backup_restore.html#backup-timestamp\n   sudo gitlab-backup restore BACKUP=<backup-timestamp>\n   ```\n\n1. Start GitLab:\n\n   ```shell\n   sudo gitlab-ctl start\n   ```\n\n### Seed the database (fresh installs only)\n\nWARNING:\nThis is a destructive command; do not run it on an existing database.\n\nOmnibus GitLab will not seed your external database. Run the following command\nto import the schema and create the first administration user:\n\n```shell\n# Remove \'sudo\' if you are the \'git\' user\nsudo gitlab-rake gitlab:setup\n```\n\nIf you want to specify a password for the default `root` user, specify the\n`initial_root_password` setting in `/etc/gitlab/gitlab.rb` before running the\n`gitlab:setup` command above:\n\n```ruby\ngitlab_rails[\'initial_root_password\'] = \'nonstandardpassword\'\n```\n\nIf you want to specify the initial registration token for shared GitLab Runners,\nspecify the `initial_shared_runners_registration_token` setting in `/etc/gitlab/gitlab.rb`\nbefore running the `gitlab:setup` command:\n\n```ruby\ngitlab_rails[\'initial_shared_runners_registration_token\'] = \'token\'\n```\n\n### Pin the packaged PostgreSQL version (fresh installs only)\n\nNOTE:\nGitLab 14.1 and onward shipped with both Postgres 12 and Postgres 13.\nGitLab 14.0 only ships with PostgreSQL 12. GitLab 13.3 and onward shipped both Postgres 11 and Postgres 12.\nGitLab 13.0 through 13.2 only shipped with PostgreSQL 11.\n\nOmnibus GitLab will initialize PostgreSQL with the [default version](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html).\n\nTo initialize PostgreSQL with a non-default version, you can set `postgresql[\'version\']` to the major version one of\nthe [packaged PostgreSQL versions](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html) prior to the initial reconfigure.\nFor example, in GitLab 15.0 you can use `postgresql[\'version\'] = 12` to use PostgreSQL 12 instead of the default of PostgreSQL 13.\n\nWARNING:\nSetting `postgresql[\'version\']` while using the Omnibus packaged PostgreSQL after the initial reconfigure will\nthrow errors about the data directory being initialized on a different version of PostgreSQL. If this is encountered,\nsee [Revert packaged PostgreSQL server to previous version](#revert-packaged-postgresql-server-to-previous-version).\n\n### Troubleshooting\n\n#### Set `default_transaction_isolation` into `read committed`\n\nIf you see errors similar to the following in your `production/sidekiq` log:\n\n```plaintext\nActiveRecord::StatementInvalid PG::TRSerializationFailure: ERROR:  could not serialize access due to concurrent update\n```\n\nChances are your database\'s `default_transaction_isolation` configuration is not\nin line with GitLab application requirement. You can check this configuration by\nconnecting to your PostgreSQL database and run `SHOW default_transaction_isolation;`.\nGitLab application expects `read committed` to be configured.\n\nThis `default_transaction_isolation` configuration is set in your\n`postgresql.conf` file. You will need to restart/reload the database once you\nchanged the configuration. This configuration comes by default in the packaged\nPostgreSQL server included with Omnibus GitLab.\n\n### Could not load library `plpgsql.so`\n\nYou might see errors similar to the following while running Database migrations\nor in the PostgreSQL/Patroni logs:\n\n```plaintext\nERROR:  could not load library "/opt/gitlab/embedded/postgresql/12/lib/plpgsql.so": /opt/gitlab/embedded/postgresql/12/lib/plpgsql.so: undefined symbol: EnsurePortalSnapshotExists\n```\n\nThis error is caused due to not restarting PostgreSQL after the underlying\nversion changed. To fix this error:\n\n1. Run one of the following commands:\n\n   ```shell\n   # For PostgreSQL\n   sudo gitlab-ctl restart postgresql\n\n   # For Patroni\n   sudo gitlab-ctl restart patroni\n\n   # For Geo PostgreSQL\n   sudo gitlab-ctl restart geo-postgresql\n   ```\n\n1. Reconfigure GitLab:\n\n   ```shell\n   sudo gitlab-ctl reconfigure\n   ```\n\n## Application Settings for the Database\n\n### Disabling automatic database migration\n\nIf you have multiple GitLab servers sharing a database, you will want to limit the\nnumber of nodes that are performing the migration steps during reconfiguration.\n\nEdit `/etc/gitlab/gitlab.rb` to append:\n\n```ruby\n# Enable or disable automatic database migrations\n# on all hosts except the designated deploy node\ngitlab_rails[\'auto_migrate\'] = false\n```\n\n`/etc/gitlab/gitlab.rb` should have file permissions `0600` because it contains\nplain-text passwords.\n\nThe next time hosts carrying the above configuration are reconfigured, the migration steps are not performed.\n\nTo avoid schema related post-upgrade errors, the host marked as [the deploy node](https://docs.gitlab.com/ee/update/zero_downtime.html#multi-node--ha-deployment) must have `gitlab_rails[\'auto_migrate\'] = true` during upgrades.\n\n### Setting client statement_timeout\n\nThe amount of time that Rails will wait for a database transaction to complete\nbefore timing out can now be adjusted with the `gitlab_rails[\'db_statement_timeout\']`\nsetting. By default, this setting is not used.\n\nEdit `/etc/gitlab/gitlab.rb`:\n\n```ruby\ngitlab_rails[\'db_statement_timeout\'] = 45000\n```\n\nIn this case the client `statement_timeout` is set to 45 seconds. The value\nis specified in milliseconds.\n\n### Setting connection timeout\n\nThe amount of time that Rails will wait for a PostgreSQL connection attempt to succeed\nbefore timing out can be adjusted with the `gitlab_rails[\'db_connect_timeout\']`\nsetting. By default, this setting is not used:\n\n1. Edit `/etc/gitlab/gitlab.rb`:\n\n   ```ruby\n   gitlab_rails[\'db_connect_timeout\'] = 5\n   ```\n\n1. Reconfigure GitLab:\n\n   ```shell\n   sudo gitlab-ctl reconfigure\n   ```\n\nIn this case the client `connect_timeout` is set to 5 seconds. The value\nis specified in seconds. A minimum value of 2 seconds applies. Setting this to `<= 0`\nor not specifying the setting at all disables the timeout.\n\n### Setting tcp controls\n\nThe Rails PostgreSQL adapter provides a series of TCP connection controls\nthat may be tuned to improve performance. Consult the\n[PostgreSQL upstream documentation for more information about each parameter](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-KEEPALIVES).\n\nOmnibus sets no defaults for these values and instead uses the defaults\nprovided by PostgreSQL adapter. Override them in `gitlab.rb` using the\nparameters noted in the table below and then run `gitlab-ctl reconfigure`.\n\n| PostgreSQL parameter | `gitlab.rb` parameter |\n|-|-|\n| `keepalives` | `gitlab_rails[\'db_keepalives\']` |\n| `keepalives_idle` | `gitlab_rails[\'db_keepalives_idle\']` |\n| `keepalives_interval` | `gitlab_rails[\'db_keepalives_interval\']` |\n| `keepalives_count` | `gitlab_rails[\'db_keepalives_count\']` |\n| `tcp_user_timeout` | `gitlab_rails[\'db_tcp_user_timeout\']` |\n\n## Automatic database reindexing\n\n> [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/249662) in GitLab 13.5.\n\nWARNING:\nThis is an experimental feature that isn\'t enabled by default.\n\nRecreates database indexes in the background (called "reindexing"). This can\nbe used to remove bloated space that has accumulated in indexes and helps to maintain healthy and\nefficient indexes.\n\nThe reindexing task can be started regularly through a cronjob. To configure the cronjob,\n`gitlab_rails[\'database_reindexing\'][\'enable\']` should be set to `true`.\n\nIn a multi-node environment, this feature should only be enabled on an application host.\nThe reindexing process cannot go through PgBouncer, it has to have a direct database connection.\n\nBy default, this starts the cronjob every hour during weekends (likely a low-traffic time) only.\n\nYou can change the schedule by refining the following settings:\n\n1. Edit `/etc/gitlab/gitlab.rb`:\n\n   ```shell\n   gitlab_rails[\'database_reindexing\'][\'hour\'] = \'*\'\n   gitlab_rails[\'database_reindexing\'][\'minute\'] = 0\n   gitlab_rails[\'database_reindexing\'][\'month\'] = \'*\'\n   gitlab_rails[\'database_reindexing\'][\'day_of_month\'] = \'*\'\n   gitlab_rails[\'database_reindexing\'][\'day_of_week\'] = \'0,6\'\n   ```\n\n1. Reconfigure GitLab:\n\n   ```shell\n   sudo gitlab-ctl reconfigure\n   ```\n\n## Packaged PostgreSQL deployed in an HA/Geo Cluster\n\n### Upgrading a GitLab HA cluster\n\nTo upgrade the PostgreSQL version in a Patroni cluster see [Upgrading PostgreSQL major version in a Patroni cluster](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#upgrading-postgresql-major-version-in-a-patroni-cluster).\n\n### Upgrading a GitLab HA Repmgr cluster\n\nNOTE:\nIf you are upgrading to PostgreSQL 12, you need to switch from Repmgr to Patroni first see [Switching from Repmgr to Patroni](https://docs.gitlab.com/ee/administration/postgresql/replication_and_failover.html#switching-from-repmgr-to-patroni).\n\nThese instructions are provided for upgrading a older GitLab cluster to PostgreSQL 11, when using Repmgr.\n\nIf [PostgreSQL is configured for high availability](https://docs.gitlab.com/ee/administration/postgresql/index.html),\n`pg-upgrade` should be run on all the nodes running PostgreSQL. Other nodes can be\nskipped, but must be running the same GitLab version as the database nodes.\n\nFollow the steps below to upgrade the database nodes:\n\n1. Secondary nodes must be upgraded before the primary node.\n   1. On the secondary nodes, edit `/etc/gitlab/gitlab.rb` to include the following:\n\n   ```shell\n   # Replace X with value of number of db nodes + 1\n   postgresql[\'max_replication_slots\'] = X\n    ```\n\n   1. Run `gitlab-ctl reconfigure` to update the configuration.\n   1. Run `sudo gitlab-ctl restart postgresql` to get PostgreSQL restarted with the new configuration.\n   1. On running `pg-upgrade` on a PostgreSQL secondary node, the node will be removed\n      from the cluster.\n   1. Once all the secondary nodes are upgraded using `pg-upgrade`, the user\n      will be left with a single-node cluster that has only the primary node.\n   1. `pg-upgrade`, on secondary nodes will not update the existing data to\n      match the new version, as that data will be replaced by the data from\n      primary node. It will, however move the existing data to a backup\n      location.\n1. Once all secondary nodes are upgraded, run `pg-upgrade` on primary node.\n   1. On the primary node, edit `/etc/gitlab/gitlab.rb` to include the following:\n\n   ```shell\n   # Replace X with value of number of db nodes + 1\n   postgresql[\'max_replication_slots\'] = X\n    ```\n\n   1. Run `gitlab-ctl reconfigure` to update the configuration.\n   1. Run `sudo gitlab-ctl restart postgresql` to get PostgreSQL restarted with the new configuration.\n   1. On a primary node, `pg-upgrade` will update the existing data to match\n      the new PostgreSQL version.\n1. Recreate the secondary nodes by running the following command on each of them\n\n   ```shell\n   gitlab-ctl repmgr standby setup MASTER_NODE_NAME\n   ```\n\n1. Check if the repmgr cluster is back to the original state\n\n   ```shell\n   gitlab-ctl repmgr cluster show\n   ```\n\n### Troubleshooting upgrades in an HA cluster\n\nIf 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:\n\n- `mv /var/opt/gitlab/postgresql/data/ /var/opt/gitlab/postgresql/data.$(date +%s)`\n\nIf 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`:\n\n```shell\npg_basebackup: could not create temporary replication slot "pg_basebackup_12345": ERROR:  all replication slots are in use\nHINT:  Free one or increase max_replication_slots.\n\n```\n\n### Upgrading a Geo instance\n\nSince Geo depends on PostgreSQL streaming replication by default, there are\nadditional considerations when upgrading GitLab and/or when upgrading\nPostgreSQL described below.\n\nWARNING:\nIf 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](https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/4692) that could cause upgrades of the PostgreSQL database to fail on the secondary. See [Disabling automatic PostgreSQL upgrades](#disabling-automatic-postgresql-upgrades) to workaround the issue. This issue is now fixed.\n\n#### Caveats when upgrading PostgreSQL with Geo\n\nWARNING:\nWhen using Geo, upgrading PostgreSQL **requires downtime on all secondaries**.\n\nWhen using Geo, upgrading PostgreSQL **requires downtime on all secondaries**\nbecause it requires re-initializing PostgreSQL replication to Geo\n**secondaries**. This is due to the way PostgreSQL streaming replication works.\nRe-initializing replication copies all data from the primary again, so it can\ntake a long time depending mostly on the size of the database and available\nbandwidth. For example, at a transfer speed of 30 Mbps, and a database size of\n100 GB, resynchronization could take approximately 8 hours. See\n[PostgreSQL documentation](https://www.postgresql.org/docs/11/pgupgrade.html)\nfor more.\n\n#### Disabling automatic PostgreSQL upgrades\n\nFrom GitLab 12.1 through GitLab 12.9, GitLab package upgrades try to upgrade\nPostgreSQL to version 10.x. In GitLab 12.10 and later, upgrades of PostgreSQL\ndon\'t happen in an unattended manner when using Geo.\n\nBefore upgrading to GitLab 12.1 through GitLab 12.9, we strongly recommend\ndisabling unattended upgrades of PostgreSQL and manually upgrading PostgreSQL\nseparately from upgrading the GitLab package to avoid any unintended downtime.\n\nYou can disable unattended upgrades of PostgreSQL by running the following on\nall nodes running `postgresql` or `geo-postgresql`:\n\n```shell\nsudo touch /etc/gitlab/disable-postgresql-upgrade\n```\n\n#### How to upgrade PostgreSQL when using Geo\n\nTo upgrade PostgreSQL, you will need the name of the replication slot, and the\nreplication user\'s password.\n\n1. Find the name of the existing replication slot on the Geo primary\'s database\n   node, run:\n\n   ```shell\n   sudo gitlab-psql -qt -c \'select slot_name from pg_replication_slots\'\n   ```\n\n   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](https://docs.gitlab.com/ee/administration/geo/replication/troubleshooting.html#check-the-health-of-the-secondary-node).\n\n1. Gather the replication user\'s password. It was set while setting up Geo in\n   [Step 1. Configure the primary server](https://docs.gitlab.com/ee/administration/geo/setup/database.html#step-1-configure-the-primary-server).\n\n1. Manually upgrade PostgreSQL on the Geo primary. Run on the Geo primary\'s\n   database node:\n\n   ```shell\n   sudo gitlab-ctl pg-upgrade\n   ```\n\n   Wait for the **primary database** to finish upgrading before\n   beginning the following step, so the secondary can remain ready as a backup.\n   Afterward, you can upgrade the **tracking database** in parallel with the\n   **secondary database**.\n\n1. Manually upgrade PostgreSQL on the Geo secondaries. Run on the Geo\n   **secondary database** and also on the **tracking database**:\n\n   ```shell\n   sudo gitlab-ctl pg-upgrade\n   ```\n\n1. Restart the database replication on the Geo **secondary database** using the\n   command:\n\n   ```shell\n   sudo gitlab-ctl replicate-geo-database --slot-name=SECONDARY_SLOT_NAME --host=PRIMARY_HOST_NAME\n   ```\n\n   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.\n\n1. [Reconfigure GitLab](https://docs.gitlab.com/ee/administration/restart_gitlab.html#omnibus-gitlab-reconfigure) on the Geo **secondary database** to update the\n   `pg_hba.conf` file. This is needed because `replicate-geo-database`\n   replicates the primary\'s file to the secondary.\n\n1. Restart `puma`, `sidekiq`, and `geo-logcursor`.\n\n   ```shell\n   sudo gitlab-ctl hup puma\n   sudo gitlab-ctl restart sidekiq\n   sudo gitlab-ctl restart geo-logcursor\n   ```\n\n1. Navigate to `https://your_primary_server/admin/geo/nodes` and ensure that all nodes are healthy.\n', 'message': "Merge branch 'docs-add-db-troubleshooting-transaction_isolation' into 'master'\n\ndefault_transaction_isolation info for db settings\n\nSee merge request gitlab-org/omnibus-gitlab!3359", 'author_name': 'Evan Read', 'lang': 'md'}