-
Using the PostgreSQL database service shipped with the Linux package
- Reconfigure and PostgreSQL restarts
- Configuring SSL
- Configure packaged PostgreSQL server to listen on TCP/IP
- Enabling PostgreSQL WAL (Write Ahead Log) Archiving
- Store PostgreSQL data in a different directory
- Upgrade packaged PostgreSQL server
- Revert packaged PostgreSQL server to the previous version
- Configuring multiple database connections
-
Using a non-packaged PostgreSQL database management server
- UNIX socket configuration for non-packaged PostgreSQL
- Configuring SSL
- Backup and restore a non-packaged PostgreSQL database
- Upgrade a non-packaged PostgreSQL database
- Seed the database (fresh installs only)
- Pin the packaged PostgreSQL version (fresh installs only)
- Troubleshooting
- Could not load library
plpgsql.so
- Application Settings for the Database
- Automatic database reindexing
- Packaged PostgreSQL deployed in an HA/Geo Cluster
- Connecting to the PostgreSQL database
Database settings
GitLab supports only the PostgreSQL database management system.
Thus you have two options for database servers to use with a Linux package installation:
- Use the packaged PostgreSQL server included with the Linux package installation (no configuration required, recommended).
- Use an external PostgreSQL server.
Using the PostgreSQL database service shipped with the Linux package
Reconfigure and PostgreSQL restarts
Linux package installations normally restart any service on reconfigure if configuration settings for that service were
changed in the gitlab.rb
file. PostgreSQL is unique in that some of its settings take effect
with a reload (HUP), while others require PostgreSQL to be restarted. Because administrators
frequently want more control over exactly when PostgreSQL is restarted, Linux package installations are configured
to do a reload of PostgreSQL on reconfigure, and not a restart. This means that if you modify any
PostgreSQL setting that requires a restart, you will need to restart PostgreSQL manually after you
reconfigure.
The GitLab config template
identifies which PostgreSQL settings require a restart and which require only a reload. You can also
run a query against your database to determine if any individual setting requires a restart. Start a
database console with sudo gitlab-psql
, then replace <setting name>
in the following query
with the setting you are changing:
SELECT name,setting FROM pg_settings WHERE context = 'postmaster' AND name = '<setting name>';
If changing the setting will require a restart, the query will return the name of the setting and the current value of that setting in the running PostgreSQL instance.
Automatic restart when the PostgreSQL version changes
By default, Linux package installations automatically restart PostgreSQL when the underlying
version changes, as suggested by the upstream documentation.
This behavior can be controlled using the auto_restart_on_version_change
setting
available for postgresql
and geo-postgresql
.
To disable automatic restarts when the PostgreSQL version changes:
-
Edit
/etc/gitlab/gitlab.rb
and add the following line:# For PostgreSQL/Patroni postgresql['auto_restart_on_version_change'] = false # For Geo PostgreSQL geo_postgresql['auto_restart_on_version_change'] = false
-
Reconfigure GitLab:
sudo gitlab-ctl reconfigure
Configuring SSL
Linux package installations automatically enable SSL on the PostgreSQL server, but it will accept
both encrypted and unencrypted connections by default. Enforcing SSL requires
using the hostssl
configuration in pg_hba.conf
. For more details, see the
pg_hba.conf
documentation.
SSL support depends on the following files:
- The public SSL certificate for the database (
server.crt
). - The corresponding private key for the SSL certificate (
server.key
). - A root certificate bundle that validates the server’s certificate (
root.crt
). By default, Linux package installations use the embedded certificate bundle in/opt/gitlab/embedded/ssl/certs/cacert.pem
. This isn’t required for self-signed certificates.
A 10-year self-signed certificate and private key are generated by a Linux package installation for use. If you’d prefer to use a CA-signed certificate or replace this with your own self-signed certificate, use the following steps.
Note that the location of these files can be configurable, but the private key
must be readable by the gitlab-psql
user. Linux package installations manage the permissions of
the files for you, but if the paths are customized, you must ensure that the
gitlab-psql
can access the directory in the files are placed in.
For more details, see the PostgreSQL documentation.
Note that server.crt
and server.key
may be different from the default SSL
certificates used to access GitLab. For example, suppose the external hostname
of your database is database.example.com
, and your external GitLab hostname
is gitlab.example.com
. You will either need a wildcard certificate for
*.example.com
or two different SSL certificates.
The ssl_cert_file
, ssl_key_file
, and ssl_ca_file
files direct PostgreSQL to where
on the filesystem to find the certificate, key, and bundle. These changes are applied to
postgresql.conf
. The directives internal_certificate
and internal_key
are used to
populate the contents of these files. The contents can be added directly or
loaded from file as shown in the following example.
After you have these files, enable SSL:
-
Edit
/etc/gitlab/gitlab.rb
:postgresql['ssl_cert_file'] = '/custom/path/to/server.crt' postgresql['ssl_key_file'] = '/custom/path/to/server.key' postgresql['ssl_ca_file'] = '/custom/path/to/bundle.pem' postgresql['internal_certificate'] = File.read('/custom/path/to/server.crt') postgresql['internal_key'] = File.read('/custom/path/to/server.key')
Relative paths will be rooted in the PostgreSQL data directory (
/var/opt/gitlab/postgresql/data
by default). -
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Require SSL
-
Add the following to
/etc/gitlab/gitlab.rb
:gitlab_rails['db_sslmode'] = 'require'
-
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Disabling SSL
-
Add the following to
/etc/gitlab/gitlab.rb
:postgresql['ssl'] = 'off'
-
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Verifying that SSL is being used
To determine whether SSL is being used by clients, you can run:
sudo gitlab-rails dbconsole --database main
In GitLab 14.1 and earlier:
sudo gitlab-rails dbconsole
At startup, you should see a banner as the following:
psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)
Type "help" for help.
To determine if clients are using SSL, issue this SQL query:
SELECT * FROM pg_stat_ssl;
For example:
gitlabhq_production=> select * from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | clientdn
------+-----+---------+------------------------+------+-------------+------------
384 | f | | | | |
386 | f | | | | |
998 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
933 | f | | | | |
1003 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1016 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1022 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1211 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1214 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1213 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1215 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
1252 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f |
1280 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab
382 | f | | | | |
381 | f | | | | |
383 | f | | | | |
(16 rows)
- Rows that have
t
listed under thessl
column are enabled. - Rows that have a value in the
clientdn
are using thecert
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
- Create a certificate and key for the server, the common name should equal the DNS name of the server
- Copy the server certificate, key, and CA file to the PostgreSQL server, and ensure the permissions are correct
- The certificate should be owned by the database user (default:
gitlab-psql
) - The key file should be owned by the database user, and its permissions should be
0400
- The CA file should be owned by the database user, and its permissions should be
0400
Don’t use the filenamesserver.crt
orserver.key
for these files. These filenames are reserved for the internal use ofomnibus-gitlab
. - The certificate should be owned by the database user (default:
-
Ensure the following is set in
gitlab.rb
:postgresql['ssl_cert_file'] = 'PATH_TO_CERTIFICATE' postgresql['ssl_key_file'] = 'PATH_TO_KEY_FILE' postgresql['ssl_ca_file'] = 'PATH_TO_CA_FILE' postgresql['listen_address'] = 'IP_ADDRESS' postgresql['cert_auth_addresses'] = { 'IP_ADDRESS' => { 'database' => 'gitlabhq_production', 'user' => 'gitlab' }
Set
listen_address
as the IP address of the server that the clients will use to connect to the database. Ensurecert_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 forcert_auth_addresses
to incorporate an IP address range. - Run
gitlab-ctl reconfigure
, and thengitlab-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.
-
Configure
gitlab.rb
gitlab_rails['db_host'] = 'IP_ADDRESS_OR_HOSTNAME_OF_DATABASE_SERVER' gitlab_rails['db_sslcert'] = 'PATH_TO_CERTIFICATE_FILE' gitlab_rails['db_sslkey'] = 'PATH_TO_KEY_FILE' gitlab_rails['db_rootcert'] = 'PATH_TO_CA_FILE'
- Run
gitlab-ctl reconfigure
for the rails client to use the new settings - Follow the steps in Verifying that SSL is being used to ensure the authentication is working.
Configure packaged PostgreSQL server to listen on TCP/IP
The packaged PostgreSQL server can be configured to listen for TCP/IP connections, with the caveat that some non-critical scripts expect UNIX sockets and may misbehave.
To configure the use of TCP/IP for the database service, make changes to both
the postgresql
and gitlab_rails
sections of gitlab.rb
.
Configure PostgreSQL block
The following settings are affected in the postgresql
block:
-
listen_address
: Controls the address on which PostgreSQL will listen. -
port
: Controls the port on which PostgreSQL listens to. The default is5432
. -
md5_auth_cidr_addresses
: A list of CIDR address blocks that are allowed to connect to the server, after authentication with a password. -
trust_auth_cidr_addresses
: A list of CIDR address blocks that are allowed to connect to the server, without authentication of any kind. Be very careful with this setting. It’s suggested that this be limited to the loopback address of127.0.0.1/24
or even127.0.0.1/32
. -
sql_user
: Controls the expected username for MD5 authentication. This defaults togitlab
, and isn’t a required setting. -
sql_user_password
: Sets the password that PostgreSQL will accept for MD5 authentication. Replacesecuresqlpassword
in the following example with an acceptable password.
-
Edit
/etc/gitlab/gitlab.rb
:postgresql['listen_address'] = '0.0.0.0' postgresql['port'] = 5432 postgresql['md5_auth_cidr_addresses'] = %w() postgresql['trust_auth_cidr_addresses'] = %w(127.0.0.1/24) postgresql['sql_user'] = "gitlab" ##! SQL_USER_PASSWORD_HASH can be generated using the command `gitlab-ctl pg-password-md5 gitlab`, ##! where `gitlab` 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
-
Reconfigure GitLab and restart PostrgreSQL:
sudo gitlab-ctl reconfigure sudo gitlab-ctl restart postgresql
Any client or GitLab service which will connect over the network will need to
provide the values of sql_user
for the username, and password provided to the
configuration when connecting to the PostgreSQL server. They must also be in the
network block provided to md5_auth_cidr_addresses
Configure GitLab Rails block
To configure the gitlab-rails
application to connect to the PostgreSQL database
over the network, several settings must be configured:
-
db_host
: Needs to be set to the IP address of the database server. If this is on the same instance as the PostgreSQL service, this can be127.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 ifdb_host
is set. -
db_username
: Configures the username with which to connect to PostgreSQL. This defaults togitlab
. -
db_password
: Must be provided if connecting to PostgreSQL over TCP/IP, and from an instance in thepostgresql['md5_auth_cidr_addresses']
block from settings above. This is not required if you are connecting to127.0.0.1
and have configuredpostgresql['trust_auth_cidr_addresses']
to include it.
-
Edit
/etc/gitlab/gitlab.rb
:gitlab_rails['db_host'] = '127.0.0.1' gitlab_rails['db_port'] = 5432 gitlab_rails['db_username'] = "gitlab" gitlab_rails['db_password'] = "securesqlpassword"
-
Reconfigure GitLab and restart PostrgreSQL:
sudo gitlab-ctl reconfigure sudo gitlab-ctl restart postgresql
Apply and restart services
After making the previous changes, an administrator should run gitlab-ctl reconfigure
.
If you experience any issues in regards to the service not listening on TCP, try
directly restarting the service with gitlab-ctl restart postgresql
.
Some included scripts of the Linux package (such as gitlab-psql
) expect the
connections to PostgreSQL to be handled over the UNIX socket, and may not function
properly. You can enable TCP/IP without disabling UNIX sockets.
Enabling PostgreSQL WAL (Write Ahead Log) Archiving
By default, WAL archiving of the packaged PostgreSQL isn’t enabled. Consider the following when seeking to enable WAL archiving:
- The WAL level needs to be ‘replica’ or higher (9.6+ options are
minimal
,replica
, orlogical
) - Increasing the WAL level will increase the amount of storage consumed in regular operations
To enable WAL Archiving:
-
Edit
/etc/gitlab/gitlab.rb
:# Replication settings postgresql['sql_replication_user'] = "gitlab_replicator" postgresql['wal_level'] = "replica" ... ... # Backup/Archive settings postgresql['archive_mode'] = "on" postgresql['archive_command'] = "/your/wal/archiver/here" postgresql['archive_timeout'] = "60"
-
Reconfigure GitLab for the changes to take effect. This will result in a database restart.
Store PostgreSQL data in a different directory
By default, everything is stored under /var/opt/gitlab/postgresql
, controlled
by the postgresql['dir']
attribute.
This consists of:
- The database socket will be
/var/opt/gitlab/postgresql/.s.PGSQL.5432
. This is controlled bypostgresql['unix_socket_directory']
. - The
gitlab-psql
system user will have itsHOME
directory set to this. This is controlled bypostgresql['home']
. - The actual data will be stored in
/var/opt/gitlab/postgresql/data
.
To change the location of the PostgreSQL data
- If this is an existing installation, stop GitLab:
gitlab-ctl stop
. - Update
postgresql['dir']
to the desired location. - Run
gitlab-ctl reconfigure
. - Start GitLab
gitlab-ctl start
.
Upgrade packaged PostgreSQL server
The Linux package provides the gitlab-ctl pg-upgrade
command to update the
packaged PostgreSQL server to a later version (if one is included in the
package). This updates PostgreSQL to the default shipped version
during package upgrades, unless specifically opted out.
Before upgrading GitLab to a newer version, refer to the version-specific changes of the Linux package to see either:
- When a database version has changed.
- When an upgrade is warranted.
omnibus-gitlab
issue tracker.To upgrade the PostgreSQL version, be sure that:
- You’re running the latest version of GitLab that supports your current version of PostgreSQL.
- If you recently upgraded, you ran
sudo gitlab-ctl reconfigure
successfully before you proceed. -
You have sufficient disk space for two copies of your database. Do not attempt to upgrade unless you have enough free space available.
- Check your database size using
sudo du -sh /var/opt/gitlab/postgresql/data
(or update your database path). - Check the space available using
sudo df -h
. If the partition where the database resides doesn’t have enough space, pass the argument--tmp-dir $DIR
to the command. GitLab 13.3 and above include an available disk space check and aborts the upgrade if the requirements aren’t met.
- Check your database size using
After you confirm that the above checklist is satisfied, you can proceed with the upgrade:
sudo gitlab-ctl pg-upgrade
To upgrade to a specific PostgreSQL version, use the -V
flag to append the
version. For example, to upgrade to PostgreSQL 14:
sudo gitlab-ctl pg-upgrade -V 14
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:
- Checks to ensure the database is in a known good state.
- Checks if there’s enough free disk space and abort otherwise. You can skip this by appending the
--skip-disk-check
flag. - Shuts down the existing database and any unnecessary services, and enables GitLab to deploy page.
- Changes the symlinks in
/opt/gitlab/embedded/bin/
for PostgreSQL to point to the newer version of the database. - Creates a new directory containing a new, empty database with a locale matching the existing database.
- Uses the
pg_upgrade
tool to copy the data from the old database to the new database. - Moves the old database out of the way.
- Moves the new database to the expected location.
- Calls
sudo gitlab-ctl reconfigure
to do the required configuration changes and starts the new database server. - Runs
ANALYZE
to generate database statistics. - Starts the remaining services and removes the deploy page.
- If any errors are detected during this process, it reverts to the old version of the database.
After the upgrade is complete, verify that everything is working as expected.
If there was an error in the output while running the ANALYZE
step, your upgrade
will still be working but will have poor database performance until the
database statistics are generated. Use gitlab-psql
to determine whether ANALYZE
should be run manually:
sudo gitlab-psql -c "SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE last_analyze IS NULL AND last_autoanalyze IS NULL;"
You can run ANALYZE
manually if the query above returned any rows:
sudo gitlab-psql -c 'SET statement_timeout = 0; ANALYZE VERBOSE;'
After you have verified that your GitLab instance is running correctly, you can clean up the old database files:
sudo rm -rf /var/opt/gitlab/postgresql/data.<old_version>
sudo rm -f /var/opt/gitlab/postgresql-version.old
You can find details of PostgreSQL versions shipped with various GitLab versions in PostgreSQL versions shipped with the Linux package.
Opt out of automatic PostgreSQL upgrades
To opt out of automatic PostgreSQL upgrades during GitLab package upgrades, run:
sudo touch /etc/gitlab/disable-postgresql-upgrade
Revert packaged PostgreSQL server to the previous version
On GitLab versions which ship multiple PostgreSQL versions, users can downgrade
an already upgraded PostgreSQL version to the earlier version using the gitlab-ctl
revert-pg-upgrade
command. This command also supports the -V
flag to specify
a target version for scenarios where more than two PostgreSQL versions are shipped in
the package (for example: GitLab 12.8 where PostgreSQL 9.6.x, 10.x, and 11.x are
shipped).
To specify a target PostgreSQL version of 12:
gitlab-ctl revert-pg-upgrade -V 12
If the target version is not specified, it will use the version in /var/opt/gitlab/postgresql-version.old
if available. Otherwise, it falls back to the default version shipped with GitLab.
On other GitLab versions that ship only one PostgreSQL version, you can’t downgrade your PostgreSQL version. You must downgrade GitLab to an older version for this.
Configuring multiple database connections
The gitlab:db:decomposition:connection_status
Rake task was introduced in GitLab 15.11.
In GitLab 16.0, GitLab defaults to using two database connections that point to the same PostgreSQL database.
Before upgrading to GitLab 16.0, check that the PostgreSQL max_connections
setting is high enough so that more than 50% of available connections show as being unused.
For example, if max_connections
is set to 100 and you see 75 connections in use, you must increase max_connections
to at least 150 before upgrading because after
upgrading, the in-use connections will double to 150.
You can verify this by running the following Rake task:
sudo gitlab-rake gitlab:db:decomposition:connection_status
If the task indicates that max_connections
is high enough, then you can proceed with the upgrade.
If, for any reason, you wish to remain on single connection, and you are upgrading
from GitLab 15.11 or earlier to GitLab 16.0, or switch back to single database connection
update this setting in /etc/gitlab/gitlab.rb
:
gitlab_rails['databases']['ci']['enable'] = false
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.
-
Edit
/etc/gitlab/gitlab.rb
:# Disable the built-in Postgres postgresql['enable'] = false # Fill in the connection details for database.yml gitlab_rails['db_adapter'] = 'postgresql' gitlab_rails['db_encoding'] = 'utf8' gitlab_rails['db_host'] = '127.0.0.1' gitlab_rails['db_port'] = 5432 gitlab_rails['db_username'] = 'USERNAME' gitlab_rails['db_password'] = 'PASSWORD'
Don’t forget to remove the
#
comment characters at the beginning of these lines.Note that:
-
/etc/gitlab/gitlab.rb
should have file permissions0600
because it contains plain-text passwords. -
PostgreSQL allows listening on multiple addresses
If you use multiple addresses in
gitlab_rails['db_host']
, comma-separated, the first address in the list will be used for the connection.
-
-
Reconfigure GitLab for the changes to take effect.
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:
-
Edit
/etc/gitlab/gitlab.rb
:# Disable the built-in Postgres postgresql['enable'] = false # Fill in the connection details for database.yml gitlab_rails['db_adapter'] = 'postgresql' gitlab_rails['db_encoding'] = 'utf8' # The path where the socket lives gitlab_rails['db_host'] = '/var/run/postgresql/'
-
Reconfigure GitLab for the changes to take effect:
sudo gitlab-ctl-reconfigure
Configuring SSL
Require SSL
-
Add the following to
/etc/gitlab/gitlab.rb
:gitlab_rails['db_sslmode'] = 'require'
-
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Require SSL and verify server certificate against CA bundle
PostgreSQL can be configured to require SSL and verify the server certificate
against a CA bundle to prevent spoofing.
The CA bundle that’s specified in gitlab_rails['db_sslrootcert']
must contain
both the root and intermediate certificates.
-
Add the following to
/etc/gitlab/gitlab.rb
: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 for
gitlab_rails['db_sslrootcert']
. More information on this can be found in the using SSL/TLS to Encrypt a Connection to a DB Instance article on AWS. -
Reconfigure GitLab to apply the configuration changes.
-
Restart PostgreSQL for the changes to take effect:
gitlab-ctl restart postgresql
If PostgreSQL fails to start, check the logs (for example,
/var/log/gitlab/postgresql/current
) for more details.
Backup and restore a non-packaged PostgreSQL database
When using the backup
and restore
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
:
/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.
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 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:
-
Add symbolic links to the non-packaged versions:
ln -s /path/to/new/pg_dump /path/to/new/psql /opt/gitlab/bin/
-
Check the versions:
/opt/gitlab/bin/pg_dump --version /opt/gitlab/bin/psql --version
They should now be the same as your non-packaged external PostgreSQL.
After this is done, ensure that the backup and restore tasks are using the correct executables by running both the backup and restore 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):
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.
- Read about significant changes to the PostgreSQL versions which shipped with the Linux package: 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 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 determines which client binaries (such as the PostgreSQL backup/restore binaries) are active.
The following example demonstrates upgrading from a database host running PostgreSQL 12 to another database host running PostgreSQL 13 and incurs downtime:
-
Spin up a new PostgreSQL 13 database server that’s set up according to the database requirements.
-
Ensure that the compatible versions of
pg_dump
andpg_restore
are being used on the GitLab Rails instance. To amend GitLab configuration, edit/etc/gitlab/gitlab.rb
and specify the value ofpostgresql['version']
:postgresql['version'] = 13
-
Reconfigure GitLab:
sudo gitlab-ctl reconfigure
-
Stop GitLab (note that this step causes downtime):
sudo gitlab-ctl stop
-
Run the backup Rake task using the SKIP options to back up only the database. Make a note of the backup file name; you’ll use it later to restore.
sudo gitlab-backup create SKIP=repositories,uploads,builds,artifacts,lfs,pages,registry
-
Shutdown the PostgreSQL 12 database host.
-
Edit
/etc/gitlab/gitlab.rb
and update thegitlab_rails['db_host']
setting to point to the PostgreSQL database 13 host. -
Reconfigure GitLab:
sudo gitlab-ctl reconfigure
The backup command requires additional parameters when your installation is using PgBouncer. -
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”:
# Use the backup timestamp https://docs.gitlab.com/ee/administration/backup_restore/backup_gitlab.html#backup-timestamp sudo gitlab-backup restore BACKUP=<backup-timestamp>
-
Start GitLab:
sudo gitlab-ctl start
-
After a upgrading PostgreSQL to a new major release:
- With Amazon RDS (as recommended by AWS), or
- If the database server is running very high (near 100%) CPU utilization.
Recreating the table statistics will ensure efficient query plans are picked and reduce database server CPU load.
Run the following query on the PostgreSQL database console:
SET statement_timeout = 0; ANALYZE VERBOSE;
Seed the database (fresh installs only)
The Linux package installation does not seed your external database. Run the following command to import the schema and create the first administration user:
# Remove 'sudo' if you are the 'git' user
sudo gitlab-rake gitlab:setup
If you want to specify a password for the default root
user, specify the
initial_root_password
setting in /etc/gitlab/gitlab.rb
before running the
gitlab:setup
command above:
gitlab_rails['initial_root_password'] = 'nonstandardpassword'
If you want to specify the initial registration token for shared GitLab Runners,
specify the initial_shared_runners_registration_token
setting in /etc/gitlab/gitlab.rb
before running the gitlab:setup
command:
gitlab_rails['initial_shared_runners_registration_token'] = 'token'
Pin the packaged PostgreSQL version (fresh installs only)
The Linux package installation initializes PostgreSQL with the default version.
To initialize PostgreSQL with a non-default version, you can set postgresql['version']
to the major version one of
the packaged PostgreSQL versions prior to the initial reconfigure.
For example, in GitLab 15.0 you can use postgresql['version'] = 12
to use PostgreSQL 12 instead of the default of PostgreSQL 13.
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.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.
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 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:
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:
-
Run one of the following commands:
# For PostgreSQL sudo gitlab-ctl restart postgresql # For Patroni sudo gitlab-ctl restart patroni # For Geo PostgreSQL sudo gitlab-ctl restart geo-postgresql
-
Reconfigure GitLab:
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:
# 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 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
:
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:
-
Edit
/etc/gitlab/gitlab.rb
:gitlab_rails['db_connect_timeout'] = 5
-
Reconfigure GitLab:
sudo gitlab-ctl reconfigure
In this case, the client connect_timeout
is set to 5 seconds. The value
is specified in seconds. A minimum value of 2 seconds applies. Setting this to <= 0
or not specifying the setting at all disables the timeout.
Setting TCP controls
The Rails PostgreSQL adapter provides a series of TCP connection controls that may be tuned to improve performance. Consult the PostgreSQL upstream documentation for more information about each parameter.
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
Introduced in GitLab 13.5.
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:
-
Edit
/etc/gitlab/gitlab.rb
:gitlab_rails['database_reindexing']['hour'] = '*' gitlab_rails['database_reindexing']['minute'] = 0 gitlab_rails['database_reindexing']['month'] = '*' gitlab_rails['database_reindexing']['day_of_month'] = '*' gitlab_rails['database_reindexing']['day_of_week'] = '0,6'
-
Reconfigure GitLab:
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.
Upgrading a GitLab HA Repmgr cluster
These instructions are provided for upgrading an older GitLab cluster to PostgreSQL 11 when using Repmgr.
If PostgreSQL is configured for high availability,
pg-upgrade
should be run on all the nodes running PostgreSQL. Other nodes can be
skipped but must be running the same GitLab version as the database nodes.
Follow the steps below to upgrade the database nodes:
- Secondary nodes must be upgraded before the primary node.
- On the secondary nodes, edit
/etc/gitlab/gitlab.rb
to include the following:
# Replace X with the number of DB nodes + 1 postgresql['max_replication_slots'] = X
- Run
gitlab-ctl reconfigure
to update the configuration. - Run
sudo gitlab-ctl restart postgresql
to get PostgreSQL restarted with the new configuration. - On running
pg-upgrade
on a PostgreSQL secondary node, the node will be removed from the cluster. - 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. -
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.
- On the secondary nodes, edit
- Once all secondary nodes are upgraded, run
pg-upgrade
on the primary node.- On the primary node, edit
/etc/gitlab/gitlab.rb
to include the following:
# Replace X with the number of DB nodes + 1 postgresql['max_replication_slots'] = X
- Run
gitlab-ctl reconfigure
to update the configuration. - Run
sudo gitlab-ctl restart postgresql
to get PostgreSQL restarted with the new configuration. - On a primary node,
pg-upgrade
will update the existing data to match the new PostgreSQL version.
- On the primary node, edit
-
Recreate the secondary nodes by running the following command on each of them
gitlab-ctl repmgr standby setup MASTER_NODE_NAME
-
Check if the repmgr cluster is back to the original state
gitlab-ctl repmgr cluster show
Troubleshooting upgrades in an HA cluster
If at some point, the bundled PostgreSQL had been running on a node before upgrading to an HA setup, the old data directory may remain. This will cause gitlab-ctl reconfigure
to downgrade the version of the PostgreSQL utilities it uses on that node. Move (or remove) the directory to prevent this:
mv /var/opt/gitlab/postgresql/data/ /var/opt/gitlab/postgresql/data.$(date +%s)
If you encounter the following error when recreating the secondary nodes with gitlab-ctl repmgr standby setup MASTER_NODE_NAME
, ensure that you have postgresql['max_replication_slots'] = X
(where X
is the number of DB nodes + 1), is included in /etc/gitlab/gitlab.rb
:
pg_basebackup: could not create temporary replication slot "pg_basebackup_12345": ERROR: all replication slots are in use
HINT: Free one or increase max_replication_slots.
Upgrading a Geo instance
Since Geo depends on PostgreSQL streaming replication by default, there are additional considerations when upgrading GitLab and/or when upgrading PostgreSQL is described below.
Caveats when upgrading PostgreSQL with Geo
When using Geo, upgrading PostgreSQL requires downtime on all secondaries because it requires re-initializing PostgreSQL replication to Geo secondaries. This is due to the way PostgreSQL streaming replication works. Re-initializing replication copies all data from the primary again, so it can take a long time depending mostly on the size of the database and available bandwidth. For example, at a transfer speed of 30 Mbps, and a database size of 100 GB, resynchronization could take approximately 8 hours. See PostgreSQL documentation for more.
Disabling automatic PostgreSQL upgrades
From GitLab 12.1 through GitLab 12.9, GitLab package upgrades try to upgrade PostgreSQL to version 10.x. In GitLab 12.10 and later, upgrades of PostgreSQL don’t happen in an unattended manner when using Geo.
Before upgrading to GitLab 12.1 through GitLab 12.9, we strongly recommend disabling unattended upgrades of PostgreSQL and manually upgrading PostgreSQL separately from upgrading the GitLab package to avoid any unintended downtime.
You can disable unattended upgrades of PostgreSQL by running the following on
all nodes running postgresql
or geo-postgresql
:
sudo touch /etc/gitlab/disable-postgresql-upgrade
How to upgrade PostgreSQL when using Geo
To upgrade PostgreSQL, you will need the name of the replication slot, and the replication user’s password.
-
Find the name of the existing replication slot on the Geo primary’s database node, run:
sudo gitlab-psql -qt -c 'select slot_name from pg_replication_slots'
If you can’t find your
slot_name
here, or there is no output returned, your Geo secondaries may not be healthy. In that case, make sure the secondaries are healthy and replication is working. -
Gather the replication user’s password. It was set while setting up Geo in Step 1. Configure the primary site.
-
Manually upgrade PostgreSQL on the Geo primary. Run on the Geo primary’s database node:
sudo gitlab-ctl pg-upgrade
Wait for the primary database to finish upgrading before beginning the following step, so the secondary can remain ready as a backup. Afterward, you can upgrade the tracking database in parallel with the secondary database.
-
Manually upgrade PostgreSQL on the Geo secondaries. Run on the Geo secondary database and also on the tracking database:
sudo gitlab-ctl pg-upgrade
-
Restart the database replication on the Geo secondary database using the command:
sudo gitlab-ctl replicate-geo-database --slot-name=SECONDARY_SLOT_NAME --host=PRIMARY_HOST_NAME
You will be prompted for the replication user’s password of the primary. Replace
SECONDARY_SLOT_NAME
with the slot name retrieved from the first step above. -
Reconfigure GitLab on the Geo secondary database to update the
pg_hba.conf
file. This is needed becausereplicate-geo-database
replicates the primary’s file to the secondary. -
Restart
puma
,sidekiq
, andgeo-logcursor
.sudo gitlab-ctl hup puma sudo gitlab-ctl restart sidekiq sudo gitlab-ctl restart geo-logcursor
-
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:
sudo gitlab-rails dbconsole --database main
In GitLab 14.1 and earlier:
sudo gitlab-rails dbconsole
or as a PostgreSQL superuser:
sudo gitlab-psql -d gitlabhq_production