1241 lines
51 KiB
Markdown
1241 lines
51 KiB
Markdown
---
|
|
stage: Systems
|
|
group: Distribution
|
|
info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://handbook.gitlab.com/handbook/product/ux/technical-writing/#assignments
|
|
---
|
|
|
|
# Database settings
|
|
|
|
DETAILS:
|
|
**Tier:** Free, Premium, Ultimate
|
|
**Offering:** Self-managed
|
|
|
|
GitLab supports only the PostgreSQL database management system.
|
|
|
|
Thus you have two options for database servers to use with a Linux package installation:
|
|
|
|
- Use the packaged PostgreSQL server included with the Linux package installation (no
|
|
configuration required, recommended).
|
|
- Use an [external PostgreSQL server](#using-a-non-packaged-postgresql-database-management-server).
|
|
|
|
## Using the PostgreSQL database service shipped with the Linux package
|
|
|
|
### Reconfigure and PostgreSQL restarts
|
|
|
|
Linux package installations normally restart any service on reconfigure if configuration settings for that service were
|
|
changed in the `gitlab.rb` file. PostgreSQL is unique in that some of its settings take effect
|
|
with a reload (HUP), while others require PostgreSQL to be restarted. Because administrators
|
|
frequently want more control over exactly when PostgreSQL is restarted, Linux package installations are configured
|
|
to do a reload of PostgreSQL on reconfigure, and not a restart. This means that if you modify any
|
|
PostgreSQL setting that requires a restart, you will need to restart PostgreSQL manually after you
|
|
reconfigure.
|
|
|
|
The [GitLab config template](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, Linux package installations automatically restart PostgreSQL when the underlying
|
|
version changes, as suggested by the [upstream documentation](https://www.postgresql.org/docs/14/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 the underlying version changes,
|
|
to avoid errors like the [one related to loading necessary libraries](#could-not-load-library-plpgsqlso).
|
|
|
|
### Configuring SSL
|
|
|
|
Linux package installations automatically enable SSL on the PostgreSQL server, but it will accept
|
|
both encrypted and unencrypted connections by default. Enforcing SSL requires
|
|
using the `hostssl` configuration in `pg_hba.conf`. For more details, see the
|
|
[`pg_hba.conf` documentation](https://www.postgresql.org/docs/14/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, Linux package installations use the embedded certificate bundle in
|
|
`/opt/gitlab/embedded/ssl/certs/cacert.pem`. This isn't required for
|
|
self-signed certificates.
|
|
|
|
A 10-year self-signed certificate and private key are generated by a Linux package installation for use. If you'd
|
|
prefer to use a CA-signed certificate or replace this with your own self-signed certificate, use the following steps.
|
|
|
|
Note that the location of these files can be configurable, but the private key
|
|
_must_ be readable by the `gitlab-psql` user. Linux package installations manage the permissions of
|
|
the files for you, but if the paths are customized, you *must* ensure that the
|
|
`gitlab-psql` can access the directory in the files are placed in.
|
|
|
|
For more details, see the [PostgreSQL documentation](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.
|
|
|
|
The `ssl_cert_file`, `ssl_key_file`, and `ssl_ca_file` files direct PostgreSQL to where
|
|
on the filesystem to find the certificate, key, and bundle. These changes are applied to
|
|
`postgresql.conf`. The directives `internal_certificate` and `internal_key` are used to
|
|
populate the contents of these files. The contents can be added directly or
|
|
loaded from file as shown in the following example.
|
|
|
|
After you have these files, enable SSL:
|
|
|
|
1. Edit `/etc/gitlab/gitlab.rb`:
|
|
|
|
```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'] = File.read('/custom/path/to/server.crt')
|
|
postgresql['internal_key'] = File.read('/custom/path/to/server.key')
|
|
```
|
|
|
|
Relative paths will be rooted in the PostgreSQL data directory
|
|
(`/var/opt/gitlab/postgresql/data` by default).
|
|
|
|
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:
|
|
|
|
```shell
|
|
sudo gitlab-rails dbconsole --database main
|
|
```
|
|
|
|
At startup, you should see a banner as the following:
|
|
|
|
```plaintext
|
|
psql (13.14)
|
|
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 file names `server.crt` or `server.key` for these files. These
|
|
file names 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
|
|
|
|
For the rails client to connect to the server, you will need a certificate and key with the `commonName` set to `gitlab`, which is signed by a certificate authority trusted in the CA file specified in `ssl_ca_file` on the database server.
|
|
|
|
1. Configure `gitlab.rb`
|
|
|
|
```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 listens to. The default is `5432`.
|
|
- `md5_auth_cidr_addresses`: A list of CIDR address blocks that are allowed to
|
|
connect to the server, after authentication with a password.
|
|
- `trust_auth_cidr_addresses`: A list of CIDR address blocks that are allowed
|
|
to connect to the server, without authentication of any kind. You should
|
|
only set this setting to allow connections from nodes that need to connect,
|
|
such as GitLab Rails or Sidekiq. This includes local connections when deployed
|
|
on the same node or from components such as Postgres Exporter (`127.0.0.1/32`).
|
|
- `sql_user`: Controls the expected username for MD5 authentication. This
|
|
defaults to `gitlab`, and isn't a required setting.
|
|
- `sql_user_password`: Sets the password that PostgreSQL will accept for MD5
|
|
authentication. Replace `securesqlpassword` in the following example with an
|
|
acceptable password.
|
|
|
|
1. Edit `/etc/gitlab/gitlab.rb`:
|
|
|
|
```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' (single-quoted to avoid shell interpolation) is the name of the SQL user that connects to GitLab.
|
|
postgresql['sql_user_password'] = "SQL_USER_PASSWORD_HASH"
|
|
|
|
# force ssl on all connections defined in trust_auth_cidr_addresses and md5_auth_cidr_addresses
|
|
postgresql['hostssl'] = true
|
|
```
|
|
|
|
1. Reconfigure GitLab and restart PostrgreSQL:
|
|
|
|
```shell
|
|
sudo gitlab-ctl reconfigure
|
|
sudo gitlab-ctl restart postgresql
|
|
```
|
|
|
|
Any client or GitLab service which will connect over the network will need to
|
|
provide the values of `sql_user` for the username, and password provided to the
|
|
configuration when connecting to the PostgreSQL server. They must also be in the
|
|
network block provided to `md5_auth_cidr_addresses`
|
|
|
|
#### Configure GitLab Rails block
|
|
|
|
To configure the `gitlab-rails` application to connect to the PostgreSQL database
|
|
over the network, several settings must be configured:
|
|
|
|
- `db_host`: Needs to be set to the IP address of the database server. If this is
|
|
on the same instance as the PostgreSQL service, this can be `127.0.0.1` and _will
|
|
not require_ password authentication.
|
|
- `db_port`: Sets the port on the PostgreSQL server to connect to, and _must be set_
|
|
if `db_host` is set.
|
|
- `db_username`: Configures the username with which to connect to PostgreSQL. This
|
|
defaults to `gitlab`.
|
|
- `db_password`: Must be provided if connecting to PostgreSQL over TCP/IP, and from
|
|
an instance in the `postgresql['md5_auth_cidr_addresses']` block from settings
|
|
above. This is not required if you are connecting to `127.0.0.1` and have configured
|
|
`postgresql['trust_auth_cidr_addresses']` to include it.
|
|
|
|
1. Edit `/etc/gitlab/gitlab.rb`:
|
|
|
|
```ruby
|
|
gitlab_rails['db_host'] = '127.0.0.1'
|
|
gitlab_rails['db_port'] = 5432
|
|
gitlab_rails['db_username'] = "gitlab"
|
|
gitlab_rails['db_password'] = "securesqlpassword"
|
|
```
|
|
|
|
1. Reconfigure GitLab and restart PostrgreSQL:
|
|
|
|
```shell
|
|
sudo gitlab-ctl reconfigure
|
|
sudo gitlab-ctl restart postgresql
|
|
```
|
|
|
|
#### Apply and restart services
|
|
|
|
After making the previous changes, an administrator should run `gitlab-ctl reconfigure`.
|
|
If you experience any issues in regards to the service not listening on TCP, try
|
|
directly restarting the service with `gitlab-ctl restart postgresql`.
|
|
|
|
Some included scripts of the Linux package (such as `gitlab-psql`) expect the
|
|
connections to PostgreSQL to be handled over the UNIX socket, and may not function
|
|
properly. You can enable TCP/IP without disabling UNIX sockets.
|
|
|
|
### Enabling PostgreSQL WAL (Write Ahead Log) Archiving
|
|
|
|
By default, WAL archiving of the packaged PostgreSQL isn't enabled. Consider the
|
|
following when seeking to enable WAL archiving:
|
|
|
|
- The WAL level needs to be 'replica' or higher (9.6+ options are `minimal`,
|
|
`replica`, or `logical`)
|
|
- Increasing the WAL level will increase the amount of storage consumed in
|
|
regular operations
|
|
|
|
To enable WAL Archiving:
|
|
|
|
1. Edit `/etc/gitlab/gitlab.rb`:
|
|
|
|
```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
|
|
|
|
The Linux package provides the `gitlab-ctl pg-upgrade` command to update the
|
|
packaged PostgreSQL server to a later version (if one is included in the
|
|
package). This updates PostgreSQL to the [default shipped version](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)
|
|
during package upgrades, unless specifically [opted out](#opt-out-of-automatic-postgresql-upgrades).
|
|
|
|
Before upgrading GitLab to a newer version, refer to the [version-specific changes](https://docs.gitlab.com/ee/update/#version-specific-upgrading-instructions)
|
|
of the Linux package to see either:
|
|
|
|
- When a database version has changed.
|
|
- When an upgrade is warranted.
|
|
|
|
WARNING:
|
|
Before upgrading, it's important that you fully read this section before running any commands. For
|
|
single-node installations, this upgrade needs downtime, as the database must be
|
|
down while the upgrade is being performed. The length of time depends on the
|
|
size of your database.
|
|
|
|
NOTE:
|
|
If you encounter any problems during the upgrade, raise an issue with a full
|
|
description at the [`omnibus-gitlab` issue tracker](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 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. The upgrade task 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
|
|
```
|
|
|
|
To upgrade to a specific PostgreSQL version, use the `-V` flag to append the
|
|
version. For example, to upgrade to PostgreSQL 14:
|
|
|
|
```shell
|
|
sudo gitlab-ctl pg-upgrade -V 14
|
|
```
|
|
|
|
NOTE:
|
|
`pg-upgrade` can take arguments; for example, you can set the timeout for the
|
|
execution of the underlying commands (`--timeout=1d2h3m4s5ms`). Run `gitlab-ctl pg-upgrade -h`
|
|
to see the full list.
|
|
|
|
`gitlab-ctl pg-upgrade` performs the following steps:
|
|
|
|
1. Checks to ensure the database is in a known good state.
|
|
1. Checks if there's enough free disk space and abort otherwise. You can skip this by appending the `--skip-disk-check` flag.
|
|
1. Shuts down the existing database and any unnecessary services, and enables GitLab to 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 the Linux package](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html).
|
|
|
|
#### Opt out of automatic PostgreSQL upgrades
|
|
|
|
To opt out of automatic PostgreSQL upgrades during GitLab package upgrades, run:
|
|
|
|
```shell
|
|
sudo touch /etc/gitlab/disable-postgresql-upgrade
|
|
```
|
|
|
|
### Revert packaged PostgreSQL server to the previous version
|
|
|
|
WARNING:
|
|
This operation will revert your current database, _including its data_, to its state
|
|
before your last upgrade. Be sure to create a backup before attempting to downgrade
|
|
your packaged PostgreSQL database.
|
|
|
|
On GitLab versions which ship multiple PostgreSQL versions, users can downgrade
|
|
an already upgraded PostgreSQL version to the earlier version using the `gitlab-ctl
|
|
revert-pg-upgrade` command. This command also supports the `-V` flag to specify
|
|
a target version for scenarios where more than two PostgreSQL versions are shipped in
|
|
the package (for example: GitLab 12.8 where PostgreSQL 9.6.x, 10.x, and 11.x are
|
|
shipped).
|
|
|
|
To specify a target PostgreSQL version of 12:
|
|
|
|
```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 that ship only one PostgreSQL version, you can't
|
|
downgrade your PostgreSQL version. You must downgrade GitLab to an older version for
|
|
this.
|
|
|
|
### Configuring multiple database connections
|
|
|
|
> - The `gitlab:db:decomposition:connection_status` Rake task was [introduced](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/111927) in GitLab 15.11.
|
|
> - Support for single database will be [removed in GitLab 18.0](https://docs.gitlab.com/ee/update/deprecations.html#running-a-single-database-is-deprecated).
|
|
|
|
In GitLab 16.0, GitLab defaults to using two database connections that point to the same PostgreSQL database.
|
|
|
|
Before upgrading to GitLab 16.0, check that the PostgreSQL `max_connections` setting is high enough so that more than 50% of available connections show as being unused.
|
|
For example, if `max_connections` is set to 100 and you see 75 connections in use, you must increase `max_connections` to at least 150 before upgrading because after
|
|
upgrading, the in-use connections will double to 150.
|
|
|
|
You can verify this by running the following Rake task:
|
|
|
|
```shell
|
|
sudo gitlab-rake gitlab:db:decomposition:connection_status
|
|
```
|
|
|
|
If the task indicates that `max_connections` is high enough, then you can proceed with the upgrade.
|
|
|
|
## Using a non-packaged PostgreSQL database management server
|
|
|
|
By default, GitLab is configured to use the PostgreSQL server that's included
|
|
in the Linux package. You can also reconfigure it to use an external instance of
|
|
PostgreSQL.
|
|
|
|
WARNING:
|
|
If you are using a non-packaged PostgreSQL server, you need to make
|
|
sure that PostgreSQL is set up according to the [database requirements document](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 listening 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 the 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'] = "<full_path_to_your_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 [backup](https://docs.gitlab.com/ee/administration/backup_restore/backup_gitlab.html#backup-command)
|
|
and [restore](https://docs.gitlab.com/ee/administration/backup_restore/restore_gitlab.html#restore-for-linux-package-installations)
|
|
commands, 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 the [backup command](https://docs.gitlab.com/ee/administration/backup_restore/backup_gitlab.html#backup-command).
|
|
|
|
```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/administration/backup_restore/backup_gitlab.html#backup-command) and
|
|
[restore](https://docs.gitlab.com/ee/administration/backup_restore/restore_gitlab.html#restore-for-linux-package-installations) commands.
|
|
|
|
### Upgrade a non-packaged PostgreSQL database
|
|
|
|
You can upgrade the external database 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:
|
|
|
|
- Check compatibility between GitLab releases and PostgreSQL versions:
|
|
- Read about which GitLab versions introduced a requirement for a
|
|
[minimum PostgreSQL version](https://docs.gitlab.com/ee/install/requirements.html#postgresql-requirements).
|
|
- Read about significant changes to the PostgreSQL versions which
|
|
[shipped with the Linux package](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html):
|
|
The Linux package is tested for compatibility with the major releases of PostgreSQL that it ships with.
|
|
- When using GitLab backup or restore, you _must_ keep the same version of GitLab.
|
|
If you plan to upgrade to a later GitLab version as well, upgrade PostgreSQL first.
|
|
- The [backup and restore commands](https://docs.gitlab.com/ee/administration/backup_restore/backup_gitlab.html#backup-command)
|
|
can be used to back up and restore the database to a later version of PostgreSQL.
|
|
- If a PostgreSQL version is specified with `postgresql['version']` that doesn't ship
|
|
with that Linux package release, the
|
|
[default version in the compatibility table](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)
|
|
determines which client binaries (such as the PostgreSQL backup/restore binaries) are active.
|
|
|
|
The following example demonstrates upgrading from a database host running PostgreSQL 13 to another database host running PostgreSQL 14 and incurs downtime:
|
|
|
|
1. Spin up a new PostgreSQL 14 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'] = 14
|
|
```
|
|
|
|
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/administration/backup_restore/backup_gitlab.html#back-up-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 13 database host.
|
|
|
|
1. Edit `/etc/gitlab/gitlab.rb` and update the `gitlab_rails['db_host']` setting
|
|
to point to the PostgreSQL database 14 host.
|
|
|
|
1. Reconfigure GitLab:
|
|
|
|
```shell
|
|
sudo gitlab-ctl reconfigure
|
|
```
|
|
|
|
WARNING:
|
|
The backup command requires [additional parameters](https://docs.gitlab.com/ee/administration/backup_restore/backup_gitlab.html#back-up-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/administration/backup_restore/backup_gitlab.html#backup-timestamp
|
|
sudo gitlab-backup restore BACKUP=<backup-timestamp>
|
|
```
|
|
|
|
1. Start GitLab:
|
|
|
|
```shell
|
|
sudo gitlab-ctl start
|
|
```
|
|
|
|
1. After upgrading PostgreSQL to a new major release, recreate the table statistics to ensure efficient query plans are picked and
|
|
to reduce database server CPU load.
|
|
|
|
If the upgrade was "in-place" using `pg_upgrade`, run the following query on the PostgreSQL database console:
|
|
|
|
```SQL
|
|
SET statement_timeout = 0; ANALYZE VERBOSE;
|
|
```
|
|
|
|
If the upgrade used `pg_dump` and `pg_restore`, run the following query on the PostgreSQL database console:
|
|
|
|
```SQL
|
|
SET statement_timeout = 0; VACUUM ANALYZE VERBOSE;
|
|
```
|
|
|
|
### Seed the database (fresh installs only)
|
|
|
|
WARNING:
|
|
This is a destructive command; do not run it on an existing database.
|
|
|
|
The Linux package installation does 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)
|
|
|
|
The Linux package ships with [different PostgreSQL versions](https://docs.gitlab.com/ee/administration/package_information/postgresql_versions.html)
|
|
and initializes the default version if not specified otherwise.
|
|
|
|
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 PostgreSQL packaged with the Linux package 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 the previous version](#revert-packaged-postgresql-server-to-the-previous-version).
|
|
|
|
If you are doing a fresh install on an environment that previously had GitLab installed on it and you are using a pinned PostgreSQL version, first make
|
|
sure that any folders that relate to PostgreSQL are deleted and that there are no PostgreSQL processes running on the instance.
|
|
|
|
## Provide sensitive data configuration to GitLab Rails without plain text storage
|
|
|
|
For more information, see the example in [configuration documentation](../settings/configuration.md#provide-the-postgresql-user-password-to-gitlab-rails).
|
|
|
|
## 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).
|
|
|
|
The Linux package sets no defaults for these values and instead uses the defaults
|
|
provided by the 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
|
|
|
|
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 an 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 the 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
|
|
the primary node. It will however move the existing data to a backup
|
|
location.
|
|
1. Once all secondary nodes are upgraded, run `pg-upgrade` on the primary node.
|
|
1. On the primary node, edit `/etc/gitlab/gitlab.rb` to include the following:
|
|
|
|
```shell
|
|
# Replace X with the 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` (where `X` is the 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 is described below.
|
|
|
|
#### 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.
|
|
|
|
#### 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 site](https://docs.gitlab.com/ee/administration/geo/setup/database.html#step-1-configure-the-primary-site).
|
|
|
|
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 --sslmode=verify-ca
|
|
```
|
|
|
|
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.
|
|
|
|
## Connecting to the PostgreSQL database
|
|
|
|
If you need to connect to the PostgreSQL database, you can connect as the
|
|
application user:
|
|
|
|
```shell
|
|
sudo gitlab-rails dbconsole --database main
|
|
```
|
|
|
|
## 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 the 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 the Linux package.
|
|
|
|
### 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
|
|
```
|