PostgreSQL All tiers Self-managed
This page contains information about PostgreSQL the GitLab Support team uses when troubleshooting. GitLab makes this information public, so that anyone can make use of the Support team’s collected knowledge.
If you’re on a paid tier and aren’t sure how to use these commands, contact Support for assistance with any issues you’re having.
Start a database console
Recommended for:
- Single-node instances.
- Scaled out or hybrid environments, on the Patroni nodes, usually the leader.
- Scaled out or hybrid environments, on the server running the PostgreSQL service.
sudo gitlab-psql
On a single-node instance, or a web or Sidekiq node you can also use the Rails database console, but it takes longer to initialize:
-
sudo gitlab-rails db-console --database main
-
In GitLab 14.1 and earlier:
sudo gitlab-rails db-console
To exit the console, type: quit
.
Other GitLab PostgreSQL documentation
This section is for links to information elsewhere in the GitLab documentation.
Procedures
-
Database procedures for Linux package installations including:
- SSL: enabling, disabling, and verifying.
- Enabling Write Ahead Log (WAL) archiving.
- Using an external (non-Omnibus) PostgreSQL installation; and backing it up.
- Listening on TCP/IP as well as or instead of sockets.
- Storing data in another location.
- Destructively reseeding the GitLab database.
- Guidance around updating packaged PostgreSQL, including how to stop it from happening automatically.
-
Consuming PostgreSQL from within CI runners.
-
Managing PostgreSQL versions on Linux package installations from the development docs.
-
PostgreSQL scaling
- Including troubleshooting
gitlab-ctl patroni check-leader
and PgBouncer errors.
- Including troubleshooting
-
Developer database documentation,
some of which is absolutely not for production use. Including:
- Understanding EXPLAIN plans.
Support topics
Database deadlocks
References:
- Issue #1 Deadlocks with GitLab 12.1, PostgreSQL 10.7.
- Customer ticket (internal) GitLab 12.1.6 and Google doc (internal).
- Issue #2 deadlocks can occur if an instance is flooded with pushes. Provided for context about how GitLab code can have this sort of unanticipated effect in unusual situations.
ERROR: deadlock detected
Three applicable timeouts are identified in the issue #30528; our recommended settings are as follows:
deadlock_timeout = 5s
statement_timeout = 15s
idle_in_transaction_session_timeout = 60s
Quoting from issue #30528:
“If a deadlock is hit, and we resolve it through aborting the transaction after a short period, then the retry mechanisms we already have will make the deadlocked piece of work try again, and it’s unlikely we’ll deadlock multiple times in a row.”
In this case, the guidance we had from development was to drop deadlock_timeout
or statement_timeout
, but to leave the third setting at 60 seconds. Setting
idle_in_transaction
protects the database from sessions potentially hanging for
days. There’s more discussion in the issue relating to introducing this timeout on GitLab.com.
PostgreSQL defaults:
-
statement_timeout = 0
(never) -
idle_in_transaction_session_timeout = 0
(never)
Comments in issue #30528
indicate that these should both be set to at least a number of minutes for all
Linux package installations (so they don’t hang indefinitely). However, 15 s
for statement_timeout
is very short, and is only effective if the
underlying infrastructure is very performant.
See current settings with:
sudo gitlab-rails runner "c = ApplicationRecord.connection ; puts c.execute('SHOW statement_timeout').to_a ;
puts c.execute('SHOW deadlock_timeout').to_a ;
puts c.execute('SHOW idle_in_transaction_session_timeout').to_a ;"
It may take a little while to respond.
{"statement_timeout"=>"1min"}
{"deadlock_timeout"=>"0"}
{"idle_in_transaction_session_timeout"=>"1min"}
These settings can be updated in /etc/gitlab/gitlab.rb
with:
postgresql['deadlock_timeout'] = '5s'
postgresql['statement_timeout'] = '15s'
postgresql['idle_in_transaction_session_timeout'] = '60s'
Once saved, reconfigure GitLab for the changes to take effect.
Temporarily changing the statement timeout
In some situations, it may be desirable to set a different statement timeout without having to reconfigure GitLab, which in this case would restart Puma and Sidekiq.
For example, a backup may fail with the following errors in the output of the backup command because the statement timeout was too short:
pg_dump: error: Error message from server: server closed the connection unexpectedly
You may also see errors in the PostgreSQL logs:
canceling statement due to statement timeout
To temporarily change the statement timeout:
- Open
/var/opt/gitlab/gitlab-rails/etc/database.yml
in an editor - Set the value of
statement_timeout
to0
, which sets an unlimited statement timeout. -
Confirm in a new Rails console session that this value is used:
sudo gitlab-rails runner "ActiveRecord::Base.connection_config[:variables]"
- Perform the action for which you need a different timeout (for example the backup or the Rails command).
- Revert the edit in
/var/opt/gitlab/gitlab-rails/etc/database.yml
.
Troubleshooting
Database is not accepting commands to avoid wraparound data loss
This error likely means that autovacuum
is failing to complete its run:
ERROR: database is not accepting commands to avoid wraparound data loss in database "gitlabhq_production"
Or
ERROR: failed to re-find parent key in index "XXX" for deletion target page XXX
To resolve the error, run VACUUM
manually:
- Stop GitLab with the command
gitlab-ctl stop
. -
Place the database in single-user mode with the command:
/opt/gitlab/embedded/bin/postgres --single -D /var/opt/gitlab/postgresql/data gitlabhq_production
- In the
backend>
prompt, runVACUUM;
. This command can take several minutes to complete. - Wait for the command to complete, then press Control + D to exit.
- Start GitLab with the command
gitlab-ctl start
.
GitLab database requirements
See database requirements and review and install the required extension list.
Serialization errors in the production/sidekiq
log
If you receive errors like this example in your production/sidekiq
log, read
about setting default_transaction_isolation
into read committed to fix the problem:
ActiveRecord::StatementInvalid PG::TRSerializationFailure: ERROR: could not serialize access due to concurrent update
PostgreSQL replication slot errors
If you receive errors like this example, read about how to resolve PostgreSQL HA replication slot errors:
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.
Geo replication errors
If you receive errors like this example, read about how to resolve Geo replication errors:
ERROR: replication slots can only be used if max_replication_slots > 0
FATAL: could not start WAL streaming: ERROR: replication slot "geo_secondary_my_domain_com" does not exist
Command exceeded allowed execution time
PANIC: could not write to file 'pg_xlog/xlogtemp.123': No space left on device
Review Geo configuration and common errors
When troubleshooting problems with Geo, you should:
- Review common Geo errors.
-
Review your Geo configuration, including:
- Reconfiguring hosts and ports.
- Reviewing and fixing the user and password mappings.
Mismatch in pg_dump
and psql
versions
If you receive errors like this example, read about how to back up and restore a non-packaged PostgreSQL database:
Dumping PostgreSQL database gitlabhq_production ... pg_dump: error: server version: 13.3; pg_dump version: 14.2
pg_dump: error: aborting because of server version mismatch
Extension btree_gist
is not allow-listed
Deploying PostgreSQL on an Azure Database for PostgreSQL - Flexible Server may result in this error:
extension "btree_gist" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
To resolve this error, allow-list the extension prior to install.