CINXE.COM

<!doctype html><html lang=en-US dir=ltr><head><script src=https://cdn.cookielaw.org/consent/7f944245-c5cd-4eed-a90e-dd955adfdd08/OtAutoBlock.js></script><script src=https://cdn.cookielaw.org/scripttemplates/otSDKStub.js data-domain-script=7f944245-c5cd-4eed-a90e-dd955adfdd08></script><script type=text/javascript>function OptanonWrapper(){}</script><script>const callback=(e)=>{for(const t of e)t.type==="childList"&&t.addedNodes.forEach(e=>{e.nodeName==="IMG"&&document.querySelectorAll('img:not([src^="http"]):not([data-ot-ignore])').forEach(e=>{e.setAttribute("data-ot-ignore","")})})},config={attributes:!0,childList:!0,subtree:!0,attributeFilter:["src"]},observer=new MutationObserver(callback);observer.observe(document.documentElement,config)</script><script>window.dataLayer=window.dataLayer||[];function gtag(){dataLayer.push(arguments)}gtag("js",new Date),gtag("config","GTM-NJXWQL"),gtag("consent","default",{analytics_storage:"granted",ad_storage:"granted",functionality_storage:"granted",wait_for_update:500}),gtag("consent","default",{analytics_storage:"denied",ad_storage:"denied",functionality_storage:"denied",region:["AT","BE","BG","HR","CY","CZ","DK","EE","FI","FR","DE","GR","HU","IE","IT","LV","LT","LU","MT","NL","PL","PT","RO","SK","SI","ES","SE","IS","LI","NO","GB","PE","RU"],wait_for_update:500}),window.geofeed=e=>{dataLayer.push({event:"OneTrustCountryLoad",oneTrustCountryId:e.country.toString()})};const json=document.createElement("script");json.setAttribute("src","https://geolocation.onetrust.com/cookieconsentpub/v1/geo/location/geofeed"),document.head.appendChild(json)</script><meta charset=utf-8><meta name=viewport content="width=device-width"><title>Database settings | GitLab Docs</title> <link rel=icon href=/favicon.ico sizes=any><link rel=icon href=/favicon.svg type=image/svg+xml><link rel=apple-touch-icon href=/apple-touch-icon.png><link rel=manifest href=/manifests/manifest.webmanifest><meta name=theme-color content="#FC6D26"><link rel=canonical href=https://docs.gitlab.com/omnibus/settings/database/><meta name=description content="GitLab product documentation."><link rel=preload href=/gitlab_ui/fonts/GitLabSans.woff2 type=font/woff2 as=font crossorigin><link rel=prefetch href=/gitlab_ui/fonts/GitLabSans-Italic.woff2 crossorigin><link rel=prefetch href=/gitlab_ui/fonts/GitLabMono.woff2 crossorigin><link rel=stylesheet href=/gitlab_ui/ui/index.css><link rel=stylesheet href=/vite/main.css><meta name=gitlab_docs_base_url content="/"><meta class=elastic name=gitlab_docs_version content="17.10"><meta class=elastic name=gitlab_docs_section content="install"><meta class=elastic name=gitlab_docs_breadcrumbs content="Install › Installation methods › Linux package (Omnibus) › Configure"><meta name=gitlab_docs_legacy_path content="/omnibus/settings/database.html"><meta name=gitlab_docs_hugo_launch_version content="17.9"><script>const ELASTIC_KEY="cDFpLWJaSUJXVHBqWWI4VGZKN3M6eENBSjl4WDRSRnlCUW94ajRQazhLQQ==",ELASTIC_INDEX="search-gitlab-docs-hugo"</script><script type=module src=/vite/elastic_search.js></script><script type=module src=/vite/history.js></script><meta name=google-site-verification content="AcGSBNaKDWnLgcYotlVibGy6STm2Y6_KJSaRxrA90xY"><meta name=google-site-verification content="6eFQOFLxYAer08ROqc3I-SAi44F9NmvH7PrUUBR3oCI"><meta name=google-site-verification content="xAUTWp3CDg-tU1LVVwsM9OrVhLR7L3SmiyKzkOuPNos"><meta name=google-site-verification content="F0zzwaMpiyWFcPQ1Lqu18qN3EnuQsqFXbySl_29yvHs"><meta name=google-site-verification content="nwo1bVaU0t9TZxZyM-aOI6-CofaH9GRL-uBPbdREWgc"><meta name=google-site-verification content="rWoHrtHEmIX0t28oOb1ZEDMYZb_EZA6rr6ZOl5otEPI"><meta name=google-site-verification content="fSxr8-uslxcuFL0N-oECp3Tm0RPNEGX97wbdayKOEL8"><meta name=google-site-verification content="ISxyLVnZqU8oY3jwrK7EO9o-2DOTvLJwPse7bZz6yhs"><meta name=google-site-verification content="x1WspIvz3ZHqS0gezfX_P-qiRDOeP2Oyrd68zrU2ErI"><meta name=google-site-verification content="94tkqWSqC1gAkWpsWgOA0l908EXJz_ncu794v5XjpWs"><meta name=google-site-verification content="DfXB2Za52GT3zs_vuLIAL4Mi3M3K4qxXcg7MAs0CUqo"><meta name=google-site-verification content="BCEBC2LC7A1NzO9Com1oBrWK88tV_QXfUL0i9mwXPL0"><meta name=google-site-verification content="a2lNcHMorfS43aoISjZt5_BBPo-H1UaTKMQdBgZO9iY"><meta name=google-site-verification content="0s16pP9MelY6wDHRf-izXb5pwLU01IogP-Uc_e8f3GU"><meta name=google-site-verification content="H474RNof35Xp8fLg02fZbg9Dzxdtfch6vtcjzpmUraU"><meta name=google-site-verification content="E0FlhpgBGeE7d1pQ6amdcIWPMDLDeu15-HLQVoDTguE"><meta name=google-site-verification content="opQd7_rXtPy-pX5CO_XZiztzeQEsXnB3j6Y1_dZAizA"><meta name=google-site-verification content="06Kq4AoXdmBOjOAkbPvnYGtSxnn4Q9QBqEO55PLlw5c"><meta name=google-site-verification content="djBBokRFSWV_VRlSE51V5TZSPzMC6hml5l-Sb22WglE"><meta name=google-site-verification content="UOW6nOsvbyMeIySuamzbws4kNC_WqehamWfoxxtKjZ8"><meta name=google-site-verification content="hXU1Gsdba74DUbvbdUHRl9o0cQeiwXIhAdIllOG6p8E"><meta name=google-site-verification content="YFeHIAPk9lE76ubVMeq4P0sQVnzo2-a4k1oU_bPY8yE"><meta name=google-site-verification content="h8ICI4eDkvXmYaGDuLTLoWuXnLn-KUkChqYB-roMRsw"><meta name=zd-site-verification content="ony3w7hk1vs6tfyrc51mld"><meta name=zd-site-verification content="gtuq65qdzt6n31viazi6hj"></head><body data-elastic-exclude><nav data-elastic-exclude class="header gl-w-full gl-fixed gl-py-0 gl-px-5 gl-z-4 gl-bg-theme-indigo-900 gl-flex gl-justify-between gl-items-center"><a href=#skipTarget class="gl-sr-only skip-link">Skip to main content</a> <a class="header-logo gl-flex lg:gl-mr-5" href=/><img src=/gitlab-logo-header.svg alt="GitLab documentation home" class=logo> <span class="border-light gl-ml-3 gl-pl-3 gl-py-1 gl-border-0 gl-border-l gl-border-solid gl-border-gray-100 gl-align-middle gl-text-white">Docs</span> </a><button class="lg:gl-hidden gl-mt-5 gl-border-0 navbar-toggle" aria-label="Toggle navbar" data-toggle=collapse data-target=.header-right></button><div class="mobile-header gl-w-full gl-text-base"><div class="header-right collapse md:gl-mt-3 lg:gl-mt-0"><div class="js-elastic-search-form gl-spinner-container"><span role=status aria-label=Loading class="gl-ml-3 gl-align-text-bottom! gl-spinner gl-spinner-light gl-spinner-sm"></span></div><div class="gl-flex gl-flex-col lg:gl-flex-row lg:gl-items-center gl-mb-0"><a class="!gl-text-white gl-mr-5 gl-w-fit md:gl-mt-4 lg:gl-mt-0" href=https://about.gitlab.com/releases/categories/releases/ target=_blank rel="noopener noreferrer">What's new?</a><div data-vue-app=versions-menu></div><a class="cta-button gl-my-3 lg:gl-my-0" href="https://about.gitlab.com/free-trial/?hosted=self-managed" target=_blank rel="noopener noreferrer" role=button>Get free trial</a></div></div></div></nav><main><div class=template-single><div data-vue-app=sidebar-menu></div><div data-pagefind-body class=main-content><div class="docs-content gl-overflow-y-auto gl-pb-7"><h1 id=skipTarget class="gl-mt-5 lg:gl-mt-8 gl-mb-6">Database settings</h1><div data-elastic-include><div class="availability gl-text-base gl-pl-4 gl-mb-5"><ul class="gl-list-none gl-p-0 gl-m-0"><li><span class=gl-font-bold>Tier</span>: Free, Premium, Ultimate</li><li><span class=gl-font-bold>Offering</span>: GitLab Self-Managed</li></ul></div><p>GitLab supports only the PostgreSQL database management system.</p><p>Thus you have two options for database servers to use with a Linux package installation:</p><ul><li>Use the packaged PostgreSQL server included with the Linux package installation (no configuration required, recommended).</li><li>Use an <a href=/omnibus/settings/database/#using-a-non-packaged-postgresql-database-management-server>external PostgreSQL server</a>.</li></ul><h2 id=using-the-postgresql-database-service-shipped-with-the-linux-package>Using the PostgreSQL database service shipped with the Linux package</h2><h3 id=reconfigure-and-postgresql-restarts>Reconfigure and PostgreSQL restarts</h3><p>Linux package installations usually restart any service on reconfigure if configuration settings for that service were changed in the <code>gitlab.rb</code> 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.</p><p>The <a href=https://gitlab.com/gitlab-org/omnibus-gitlab/blob/master/files/gitlab-config-template/gitlab.rb.template>GitLab config template</a> 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 <code>sudo gitlab-psql</code>, then replace <code><setting name></code> in the following query with the setting you are changing:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:green;font-weight:700>SELECT</span><span style=color:#bbb> </span>name,setting<span style=color:#bbb> </span><span style=color:green;font-weight:700>FROM</span><span style=color:#bbb> </span>pg_settings<span style=color:#bbb> </span><span style=color:green;font-weight:700>WHERE</span><span style=color:#bbb> </span>context<span style=color:#bbb> </span><span style=color:#666>=</span><span style=color:#bbb> </span><span style=color:#ba2121>'postmaster'</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>AND</span><span style=color:#bbb> </span>name<span style=color:#bbb> </span><span style=color:#666>=</span><span style=color:#bbb> </span><span style=color:#ba2121>'<setting name>'</span>;</span></span></code></pre></div></div><p>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.</p><h4 id=automatic-restart-when-the-postgresql-version-changes>Automatic restart when the PostgreSQL version changes</h4><p>By default, Linux package installations automatically restart PostgreSQL when the underlying version changes, as suggested by the <a href=https://www.postgresql.org/docs/14/upgrading.html>upstream documentation</a>. This behavior can be controlled using the <code>auto_restart_on_version_change</code> setting available for <code>postgresql</code> and <code>geo-postgresql</code>.</p><p>To disable automatic restarts when the PostgreSQL version changes:</p><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code> and add the following line:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span><span style=color:#408080;font-style:italic># For PostgreSQL/Patroni</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'auto_restart_on_version_change'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>false</span> </span></span><span style=display:flex><span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># For Geo PostgreSQL</span> </span></span><span style=display:flex><span>geo_postgresql<span style=color:#666>[</span><span style=color:#ba2121>'auto_restart_on_version_change'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>false</span></span></span></code></pre></div></div></li><li><p>Reconfigure GitLab:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure</span></span></code></pre></div></div></li></ol><div class="alert alert-type-note"><p>It is highly recommended to restart PostgreSQL when the underlying version changes, to avoid errors like the <a href=/omnibus/settings/database/#could-not-load-library-plpgsqlso>one related to loading necessary libraries</a>.</p></div><h3 id=configuring-ssl>Configuring SSL</h3><p>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 <code>hostssl</code> configuration in <code>pg_hba.conf</code>. For more details, see the <a href=https://www.postgresql.org/docs/14/auth-pg-hba-conf.html><code>pg_hba.conf</code> documentation</a>.</p><p>SSL support depends on the following files:</p><ul><li>The public SSL certificate for the database (<code>server.crt</code>).</li><li>The corresponding private key for the SSL certificate (<code>server.key</code>).</li><li>A root certificate bundle that validates the server’s certificate (<code>root.crt</code>). By default, Linux package installations use the embedded certificate bundle in <code>/opt/gitlab/embedded/ssl/certs/cacert.pem</code>. This isn’t required for self-signed certificates.</li></ul><p>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.</p><p>Note that the location of these files can be configurable, but the private key <em>must</em> be readable by the <code>gitlab-psql</code> user. Linux package installations manage the permissions of the files for you, but if the paths are customized, you <em>must</em> ensure that the <code>gitlab-psql</code> can access the directory in the files are placed in.</p><p>For more details, see the <a href=https://www.postgresql.org/docs/11/ssl-tcp.html>PostgreSQL documentation</a>.</p><p>Note that <code>server.crt</code> and <code>server.key</code> may be different from the default SSL certificates used to access GitLab. For example, suppose the external hostname of your database is <code>database.example.com</code>, and your external GitLab hostname is <code>gitlab.example.com</code>. You will either need a wildcard certificate for <code>*.example.com</code> or two different SSL certificates.</p><p>The <code>ssl_cert_file</code>, <code>ssl_key_file</code>, and <code>ssl_ca_file</code> files direct PostgreSQL to where on the filesystem to find the certificate, key, and bundle. These changes are applied to <code>postgresql.conf</code>. The directives <code>internal_certificate</code> and <code>internal_key</code> 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.</p><p>After you have these files, enable SSL:</p><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'ssl_cert_file'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'/custom/path/to/server.crt'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'ssl_key_file'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'/custom/path/to/server.key'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'ssl_ca_file'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'/custom/path/to/bundle.pem'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'internal_certificate'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#800>File</span><span style=color:#666>.</span>read(<span style=color:#ba2121>'/custom/path/to/server.crt'</span>) </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'internal_key'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#800>File</span><span style=color:#666>.</span>read(<span style=color:#ba2121>'/custom/path/to/server.key'</span>)</span></span></code></pre></div></div><p>Relative paths will be rooted in the PostgreSQL data directory (<code>/var/opt/gitlab/postgresql/data</code> by default).</p></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> to apply the configuration changes.</p></li><li><p>Restart PostgreSQL for the changes to take effect:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl restart postgresql</span></span></code></pre></div></div><p>If PostgreSQL fails to start, check the logs (for example, <code>/var/log/gitlab/postgresql/current</code>) for more details.</p></li></ol><h4 id=require-ssl>Require SSL</h4><ol><li><p>Add the following to <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_sslmode'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'require'</span></span></span></code></pre></div></div></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> to apply the configuration changes.</p></li><li><p>Restart PostgreSQL for the changes to take effect:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl restart postgresql</span></span></code></pre></div></div><p>If PostgreSQL fails to start, check the logs (for example, <code>/var/log/gitlab/postgresql/current</code>) for more details.</p></li></ol><h4 id=disabling-ssl>Disabling SSL</h4><ol><li><p>Add the following to <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'ssl'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'off'</span></span></span></code></pre></div></div></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> to apply the configuration changes.</p></li><li><p>Restart PostgreSQL for the changes to take effect:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl restart postgresql</span></span></code></pre></div></div><p>If PostgreSQL fails to start, check the logs (for example, <code>/var/log/gitlab/postgresql/current</code>) for more details.</p></li></ol><h4 id=verifying-that-ssl-is-being-used>Verifying that SSL is being used</h4><p>To determine whether SSL is being used by clients, you can run:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-rails dbconsole --database main</span></span></code></pre></div></div><p>At startup, you should see a banner as the following:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-plaintext data-lang=plaintext><span style=display:flex><span>psql (13.14) </span></span><span style=display:flex><span>SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on) </span></span><span style=display:flex><span>Type "help" for help.</span></span></code></pre></div></div><p>To determine if clients are using SSL, issue this SQL query:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:green;font-weight:700>SELECT</span><span style=color:#bbb> </span><span style=color:#666>*</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>FROM</span><span style=color:#bbb> </span>pg_stat_ssl;</span></span></code></pre></div></div><p>For example:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-plaintext data-lang=plaintext><span style=display:flex><span>gitlabhq_production=> select * from pg_stat_ssl; </span></span><span style=display:flex><span> pid | ssl | version | cipher | bits | compression | clientdn </span></span><span style=display:flex><span>------+-----+---------+------------------------+------+-------------+------------ </span></span><span style=display:flex><span> 384 | f | | | | | </span></span><span style=display:flex><span> 386 | f | | | | | </span></span><span style=display:flex><span> 998 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 933 | f | | | | | </span></span><span style=display:flex><span> 1003 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 1016 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 1022 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 1211 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 1214 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 1213 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 1215 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 1252 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | </span></span><span style=display:flex><span> 1280 | t | TLSv1.3 | TLS_AES_256_GCM_SHA384 | 256 | f | /CN=gitlab </span></span><span style=display:flex><span> 382 | f | | | | | </span></span><span style=display:flex><span> 381 | f | | | | | </span></span><span style=display:flex><span> 383 | f | | | | | </span></span><span style=display:flex><span>(16 rows)</span></span></code></pre></div></div><ol><li>Rows that have <code>t</code> listed under the <code>ssl</code> column are enabled.</li><li>Rows that have a value in the <code>clientdn</code> are using the <code>cert</code> authentication method</li></ol><h4 id=configure-ssl-client-authentication>Configure SSL client authentication</h4><p>Client SSL certificates can be used to authenticate to the database server. Creating the certificates is beyond the scope of <code>omnibus-gitlab</code>. But users who have an existing SSL certificate management solution can use this.</p><h5 id=configure-the-database-server>Configure the database server</h5><ol><li><p>Create a certificate and key for the server, the common name should equal the DNS name of the server</p></li><li><p>Copy the server certificate, key, and CA file to the PostgreSQL server, and ensure the permissions are correct</p><ol><li>The certificate should be owned by the database user (default: <code>gitlab-psql</code>)</li><li>The key file should be owned by the database user, and its permissions should be <code>0400</code></li><li>The CA file should be owned by the database user, and its permissions should be <code>0400</code></li></ol><div class="alert alert-type-note"><p>Don’t use the file names <code>server.crt</code> or <code>server.key</code> for these files. These file names are reserved for the internal use of <code>omnibus-gitlab</code>.</p></div></li><li><p>Ensure the following is set in <code>gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'ssl_cert_file'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'PATH_TO_CERTIFICATE'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'ssl_key_file'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'PATH_TO_KEY_FILE'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'ssl_ca_file'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'PATH_TO_CA_FILE'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'listen_address'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'IP_ADDRESS'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'cert_auth_addresses'</span><span style=color:#666>]</span> <span style=color:#666>=</span> { </span></span><span style=display:flex><span><span style=color:#ba2121>'IP_ADDRESS'</span> <span style=color:#666>=></span> { </span></span><span style=display:flex><span> <span style=color:#ba2121>'database'</span> <span style=color:#666>=></span> <span style=color:#ba2121>'gitlabhq_production'</span>, </span></span><span style=display:flex><span> <span style=color:#ba2121>'user'</span> <span style=color:#666>=></span> <span style=color:#ba2121>'gitlab'</span> </span></span><span style=display:flex><span>}</span></span></code></pre></div></div><p>Set <code>listen_address</code> as the IP address of the server that the clients will use to connect to the database. Ensure <code>cert_auth_addresses</code> 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 <code>cert_auth_addresses</code> to incorporate an IP address range.</p></li><li><p>Run <code>gitlab-ctl reconfigure</code>, and then <code>gitlab-ctl restart postgresql</code> for the new settings to take effect.</p></li></ol><h4 id=configure-the-rails-client>Configure the Rails client</h4><p>For the rails client to connect to the server, you will need a certificate and key with the <code>commonName</code> set to <code>gitlab</code>, which is signed by a certificate authority trusted in the CA file specified in <code>ssl_ca_file</code> on the database server.</p><ol><li><p>Configure <code>gitlab.rb</code></p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_host'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'IP_ADDRESS_OR_HOSTNAME_OF_DATABASE_SERVER'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_sslcert'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'PATH_TO_CERTIFICATE_FILE'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_sslkey'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'PATH_TO_KEY_FILE'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_rootcert'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'PATH_TO_CA_FILE'</span></span></span></code></pre></div></div></li><li><p>Run <code>gitlab-ctl reconfigure</code> for the rails client to use the new settings</p></li><li><p>Follow the steps in <a href=/omnibus/settings/database/#verifying-that-ssl-is-being-used>Verifying that SSL is being used</a> to ensure the authentication is working.</p></li></ol><h3 id=configure-packaged-postgresql-server-to-listen-on-tcpip>Configure packaged PostgreSQL server to listen on TCP/IP</h3><p>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.</p><p>To configure the use of TCP/IP for the database service, make changes to both the <code>postgresql</code> and <code>gitlab_rails</code> sections of <code>gitlab.rb</code>.</p><h4 id=configure-postgresql-block>Configure PostgreSQL block</h4><p>The following settings are affected in the <code>postgresql</code> block:</p><ul><li><code>listen_address</code>: Controls the address on which PostgreSQL will listen.</li><li><code>port</code>: Controls the port on which PostgreSQL listens to. The default is <code>5432</code>.</li><li><code>md5_auth_cidr_addresses</code>: A list of CIDR address blocks that are allowed to connect to the server, after authentication with a password.</li><li><code>trust_auth_cidr_addresses</code>: 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 (<code>127.0.0.1/32</code>).</li><li><code>sql_user</code>: Controls the expected username for MD5 authentication. This defaults to <code>gitlab</code>, and isn’t a required setting.</li><li><code>sql_user_password</code>: Sets the password that PostgreSQL will accept for MD5 authentication.</li></ul><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'listen_address'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'0.0.0.0'</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'port'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#666>5432</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'md5_auth_cidr_addresses'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>%w()</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'trust_auth_cidr_addresses'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>%w(127.0.0.1/24)</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'sql_user'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"gitlab"</span> </span></span><span style=display:flex><span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic>##! SQL_USER_PASSWORD_HASH can be generated using the command `gitlab-ctl pg-password-md5 'gitlab'`,</span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic>##! where 'gitlab' (single-quoted to avoid shell interpolation) is the name of the SQL user that connects to GitLab.</span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic>##! You will be prompted for a password which other clients will use to authenticate with database, such as `securesqlpassword` in the below section.</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'sql_user_password'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"SQL_USER_PASSWORD_HASH"</span> </span></span><span style=display:flex><span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># force ssl on all connections defined in trust_auth_cidr_addresses and md5_auth_cidr_addresses</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'hostssl'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>true</span></span></span></code></pre></div></div></li><li><p>Reconfigure GitLab and restart PostrgreSQL:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure </span></span><span style=display:flex><span>sudo gitlab-ctl restart postgresql</span></span></code></pre></div></div></li></ol><p>Any client or GitLab service which will connect over the network will need to provide the values of <code>sql_user</code> 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 <code>md5_auth_cidr_addresses</code></p><h4 id=configure-gitlab-rails-block>Configure GitLab Rails block</h4><p>To configure the <code>gitlab-rails</code> application to connect to the PostgreSQL database over the network, several settings must be configured:</p><ul><li><code>db_host</code>: 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 <code>127.0.0.1</code> and <em>will not require</em> password authentication.</li><li><code>db_port</code>: Sets the port on the PostgreSQL server to connect to, and <em>must be set</em> if <code>db_host</code> is set.</li><li><code>db_username</code>: Configures the username with which to connect to PostgreSQL. This defaults to <code>gitlab</code>.</li><li><code>db_password</code>: Must be provided if connecting to PostgreSQL over TCP/IP, and from an instance in the <code>postgresql['md5_auth_cidr_addresses']</code> block from settings above. This is not required if you are connecting to <code>127.0.0.1</code> and have configured <code>postgresql['trust_auth_cidr_addresses']</code> to include it.</li></ul><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_host'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'127.0.0.1'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_port'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#666>5432</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_username'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"gitlab"</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_password'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"securesqlpassword"</span></span></span></code></pre></div></div></li><li><p>Reconfigure GitLab and restart PostrgreSQL:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure </span></span><span style=display:flex><span>sudo gitlab-ctl restart postgresql</span></span></code></pre></div></div></li></ol><h4 id=apply-and-restart-services>Apply and restart services</h4><p>After making the previous changes, an administrator should run <code>gitlab-ctl reconfigure</code>. If you experience any issues in regards to the service not listening on TCP, try directly restarting the service with <code>gitlab-ctl restart postgresql</code>.</p><p>Some included scripts of the Linux package (such as <code>gitlab-psql</code>) 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.</p><p>To test access from other clients, you can run:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-rails dbconsole --database main</span></span></code></pre></div></div><h3 id=enabling-postgresql-wal-write-ahead-log-archiving>Enabling PostgreSQL WAL (Write Ahead Log) Archiving</h3><p>By default, WAL archiving of the packaged PostgreSQL isn’t enabled. Consider the following when seeking to enable WAL archiving:</p><ul><li>The WAL level needs to be ‘replica’ or higher (9.6+ options are <code>minimal</code>, <code>replica</code>, or <code>logical</code>)</li><li>Increasing the WAL level will increase the amount of storage consumed in regular operations</li></ul><p>To enable WAL Archiving:</p><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span><span style=color:#408080;font-style:italic># Replication settings</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'sql_replication_user'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"gitlab_replicator"</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'wal_level'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"replica"</span> </span></span><span style=display:flex><span> <span style=color:#666>...</span> </span></span><span style=display:flex><span> <span style=color:#666>...</span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># Backup/Archive settings</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'archive_mode'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"on"</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'archive_command'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"/your/wal/archiver/here"</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'archive_timeout'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"60"</span></span></span></code></pre></div></div></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> for the changes to take effect. This will result in a database restart.</p></li></ol><h3 id=store-postgresql-data-in-a-different-directory>Store PostgreSQL data in a different directory</h3><p>By default, everything is stored under <code>/var/opt/gitlab/postgresql</code>, controlled by the <code>postgresql['dir']</code> attribute.</p><p>This consists of:</p><ul><li>The database socket will be <code>/var/opt/gitlab/postgresql/.s.PGSQL.5432</code>. This is controlled by <code>postgresql['unix_socket_directory']</code>.</li><li>The <code>gitlab-psql</code> system user will have its <code>HOME</code> directory set to this. This is controlled by <code>postgresql['home']</code>.</li><li>The actual data will be stored in <code>/var/opt/gitlab/postgresql/data</code>.</li></ul><p>To change the location of the PostgreSQL data</p><div class="alert alert-type-warning"><p>If you have an existing database, you need to move the data to the new location first.</p></div><div class="alert alert-type-warning"><p>This is an intrusive operation. It cannot be done without downtime on an existing installation</p></div><ol><li>If this is an existing installation, stop GitLab: <code>gitlab-ctl stop</code>.</li><li>Update <code>postgresql['dir']</code> to the desired location.</li><li>Run <code>gitlab-ctl reconfigure</code>.</li><li>Start GitLab <code>gitlab-ctl start</code>.</li></ol><h3 id=upgrade-packaged-postgresql-server>Upgrade packaged PostgreSQL server</h3><p>The Linux package provides the <code>gitlab-ctl pg-upgrade</code> command to update the packaged PostgreSQL server to a later version (if one is included in the package). This updates PostgreSQL to the <a href=https://docs.gitlab.com/administration/package_information/postgresql_versions/>default shipped version</a> during package upgrades, unless specifically <a href=/omnibus/settings/database/#opt-out-of-automatic-postgresql-upgrades>opted out</a>.</p><p>Before upgrading GitLab to a newer version, refer to the <a href=https://docs.gitlab.com/update/#version-specific-upgrading-instructions>version-specific changes</a> of the Linux package to see either:</p><ul><li>When a database version has changed.</li><li>When an upgrade is warranted.</li></ul><div class="alert alert-type-warning"><p>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.</p></div><div class="alert alert-type-note"><p>If you encounter any problems during the upgrade, raise an issue with a full description at the <a href=https://gitlab.com/gitlab-org/omnibus-gitlab><code>omnibus-gitlab</code> issue tracker</a>.</p></div><p>To upgrade the PostgreSQL version, be sure that:</p><ul><li><p>You’re running the latest version of GitLab that supports your current version of PostgreSQL.</p></li><li><p>If you recently upgraded, you ran <code>sudo gitlab-ctl reconfigure</code> successfully before you proceed.</p></li><li><p>You have sufficient disk space for two copies of your database. <em>Do not attempt to upgrade unless you have enough free space available.</em></p><ul><li>Check your database size using <code>sudo du -sh /var/opt/gitlab/postgresql/data</code> (or update your database path).</li><li>Check the space available using <code>sudo df -h</code>. If the partition where the database resides doesn’t have enough space, pass the argument <code>--tmp-dir $DIR</code> to the command. The upgrade task includes an available disk space check and aborts the upgrade if the requirements aren’t met.</li></ul></li></ul><p>After you confirm that the above checklist is satisfied, you can proceed with the upgrade:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl pg-upgrade</span></span></code></pre></div></div><p>To upgrade to a specific PostgreSQL version, use the <code>-V</code> flag to append the version. For example, to upgrade to PostgreSQL 16:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl pg-upgrade -V <span style=color:#666>16</span></span></span></code></pre></div></div><div class="alert alert-type-note"><p><code>pg-upgrade</code> can take arguments; for example, you can set the timeout for the execution of the underlying commands (<code>--timeout=1d2h3m4s5ms</code>). Run <code>gitlab-ctl pg-upgrade -h</code> to see the full list.</p></div><p><code>gitlab-ctl pg-upgrade</code> performs the following steps:</p><ol><li>Checks to ensure the database is in a known good state.</li><li>Checks if there’s enough free disk space and abort otherwise. You can skip this by appending the <code>--skip-disk-check</code> flag.</li><li>Shuts down the existing database and any unnecessary services, and enables GitLab to deploy page.</li><li>Changes the symlinks in <code>/opt/gitlab/embedded/bin/</code> for PostgreSQL to point to the newer version of the database.</li><li>Creates a new directory containing a new, empty database with a locale matching the existing database.</li><li>Uses the <code>pg_upgrade</code> tool to copy the data from the old database to the new database.</li><li>Moves the old database out of the way.</li><li>Moves the new database to the expected location.</li><li>Calls <code>sudo gitlab-ctl reconfigure</code> to do the required configuration changes and starts the new database server.</li><li>Runs <code>ANALYZE</code> to generate database statistics.</li><li>Starts the remaining services and removes the deploy page.</li><li>If any errors are detected during this process, it reverts to the old version of the database.</li></ol><p>After the upgrade is complete, verify that everything is working as expected.</p><p>If there was an error in the output while running the <code>ANALYZE</code> step, your upgrade will still be working but will have poor database performance until the database statistics are generated. Use <code>gitlab-psql</code> to determine whether <code>ANALYZE</code> should be run manually:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-psql -c <span style=color:#ba2121>"SELECT relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE last_analyze IS NULL AND last_autoanalyze IS NULL;"</span></span></span></code></pre></div></div><p>You can run <code>ANALYZE</code> manually if the query above returned any rows:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-psql -c <span style=color:#ba2121>'SET statement_timeout = 0; ANALYZE VERBOSE;'</span></span></span></code></pre></div></div><p>The execution time of the <code>ANALYZE</code> command can vary significantly depending on your database size. To monitor the progress of this operation, you can periodically run the following query in another console session. The <code>tables_remaining</code> column should gradually reach <code>0</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-psql -c <span style=color:#ba2121>" </span></span></span><span style=display:flex><span><span style=color:#ba2121>SELECT </span></span></span><span style=display:flex><span><span style=color:#ba2121> COUNT(*) AS total_tables, </span></span></span><span style=display:flex><span><span style=color:#ba2121> SUM(CASE WHEN last_analyze IS NULL OR last_analyze < (NOW() - INTERVAL '2 hours') THEN 1 ELSE 0 END) AS tables_remaining </span></span></span><span style=display:flex><span><span style=color:#ba2121>FROM pg_stat_user_tables; </span></span></span><span style=display:flex><span><span style=color:#ba2121>"</span></span></span></code></pre></div></div><p><em>After you have verified that your GitLab instance is running correctly</em>, you can clean up the old database files:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo rm -rf /var/opt/gitlab/postgresql/data.<old_version> </span></span><span style=display:flex><span>sudo rm -f /var/opt/gitlab/postgresql-version.old</span></span></code></pre></div></div><p>You can find details of PostgreSQL versions shipped with various GitLab versions in <a href=https://docs.gitlab.com/administration/package_information/postgresql_versions/>PostgreSQL versions shipped with the Linux package</a>.</p><h4 id=opt-out-of-automatic-postgresql-upgrades>Opt out of automatic PostgreSQL upgrades</h4><p>To opt out of automatic PostgreSQL upgrades during GitLab package upgrades, run:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo touch /etc/gitlab/disable-postgresql-upgrade</span></span></code></pre></div></div><h3 id=revert-packaged-postgresql-server-to-the-previous-version>Revert packaged PostgreSQL server to the previous version</h3><div class="alert alert-type-warning"><p>This operation will revert your current database, <em>including its data</em>, to its state before your last upgrade. Be sure to create a backup before attempting to downgrade your packaged PostgreSQL database.</p></div><p>On GitLab versions which ship multiple PostgreSQL versions, users can downgrade an already upgraded PostgreSQL version to the earlier version using the <code>gitlab-ctl revert-pg-upgrade</code> command. This command also supports the <code>-V</code> 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).</p><p>To specify a target PostgreSQL version of 14:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl revert-pg-upgrade -V <span style=color:#666>14</span></span></span></code></pre></div></div><p>If the target version is not specified, it will use the version in <code>/var/opt/gitlab/postgresql-version.old</code> if available. Otherwise, it falls back to the default version shipped with GitLab.</p><p>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.</p><h3 id=configuring-multiple-database-connections>Configuring multiple database connections</h3><div class="version-history gl-text-base gl-pl-4 gl-mb-5"><div class=gl-font-bold>History <button class="gl-align-middle gl-mb-1 gl-ml-2 gl-rounded-base gl-border gl-border-solid gl-border-gray-100 gl-h-5" type=button aria-expanded=false aria-controls=history-9 aria-label="Toggle version history" data-toggle=collapse data-target=#history-9></button></div><div id=history-9 class="history-list collapse" aria-hidden=true><ul><li>The <code>gitlab:db:decomposition:connection_status</code> Rake task was <a href=https://gitlab.com/gitlab-org/gitlab/-/merge_requests/111927>introduced</a> in GitLab 15.11.</li><li>Support for single database will be <a href=https://docs.gitlab.com/update/deprecations/#running-a-single-database-is-deprecated>removed in a future release</a>.</li></ul></div></div><p>In GitLab 16.0, GitLab defaults to using two database connections that point to the same PostgreSQL database.</p><p>Before upgrading to GitLab 16.0, check that the PostgreSQL <code>max_connections</code> setting is high enough so that more than 50% of available connections show as being unused. For example, if <code>max_connections</code> is set to 100 and you see 75 connections in use, you must increase <code>max_connections</code> to at least 150 before upgrading because after upgrading, the in-use connections will double to 150.</p><p>You can verify this by running the following Rake task:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-rake gitlab:db:decomposition:connection_status</span></span></code></pre></div></div><p>If the task indicates that <code>max_connections</code> is high enough, then you can proceed with the upgrade.</p><h2 id=using-a-non-packaged-postgresql-database-management-server>Using a non-packaged PostgreSQL database management server</h2><p>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.</p><div class="alert alert-type-warning"><p>If you are using a non-packaged PostgreSQL server, you need to make sure that PostgreSQL is set up according to the <a href=https://docs.gitlab.com/install/requirements/#database>database requirements document</a>.</p></div><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span><span style=color:#408080;font-style:italic># Disable the built-in Postgres</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'enable'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>false</span> </span></span><span style=display:flex><span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># Fill in the connection details for database.yml</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_adapter'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'postgresql'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_encoding'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'utf8'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_host'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'127.0.0.1'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_port'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#666>5432</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_username'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'USERNAME'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_password'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'PASSWORD'</span></span></span></code></pre></div></div><p>Don’t forget to remove the <code>#</code> comment characters at the beginning of these lines.</p><p>Note that:</p><ul><li><p><code>/etc/gitlab/gitlab.rb</code> should have file permissions <code>0600</code> because it contains plain-text passwords.</p></li><li><p>PostgreSQL allows listening on <a href=https://www.postgresql.org/docs/11/runtime-config-connection.html>multiple addresses</a></p><p>If you use multiple addresses in <code>gitlab_rails['db_host']</code>, comma-separated, the first address in the list will be used for the connection.</p></li></ul></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> for the changes to take effect.</p></li><li><p><a href=/omnibus/settings/database/#seed-the-database-fresh-installs-only>Seed the database</a>.</p></li></ol><h3 id=unix-socket-configuration-for-non-packaged-postgresql>UNIX socket configuration for non-packaged PostgreSQL</h3><p>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:</p><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span><span style=color:#408080;font-style:italic># Disable the built-in Postgres</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'enable'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>false</span> </span></span><span style=display:flex><span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># Fill in the connection details for database.yml</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_adapter'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'postgresql'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_encoding'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'utf8'</span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># The path where the socket lives</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_host'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'/var/run/postgresql/'</span></span></span></code></pre></div></div></li><li><p>Reconfigure GitLab for the changes to take effect:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>sudo gitlab<span style=color:#666>-</span>ctl<span style=color:#666>-</span>reconfigure</span></span></code></pre></div></div></li></ol><h3 id=configuring-ssl-1>Configuring SSL</h3><h4 id=require-ssl-1>Require SSL</h4><ol><li><p>Add the following to <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_sslmode'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'require'</span></span></span></code></pre></div></div></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> to apply the configuration changes.</p></li><li><p>Restart PostgreSQL for the changes to take effect:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl restart postgresql</span></span></code></pre></div></div><p>If PostgreSQL fails to start, check the logs (for example, <code>/var/log/gitlab/postgresql/current</code>) for more details.</p></li></ol><h4 id=require-ssl-and-verify-server-certificate-against-ca-bundle>Require SSL and verify server certificate against CA bundle</h4><p>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 <code>gitlab_rails['db_sslrootcert']</code> must contain both the root and intermediate certificates.</p><ol><li><p>Add the following to <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_sslmode'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"verify-full"</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_sslrootcert'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>"<full_path_to_your_ca-bundle.pem>"</span></span></span></code></pre></div></div><p>If you are using Amazon RDS for your PostgreSQL server, ensure you download and use the <a href=https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem>combined CA bundle</a> for <code>gitlab_rails['db_sslrootcert']</code>. More information on this can be found in the <a href=https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html>using SSL/TLS to Encrypt a Connection to a DB Instance</a> article on AWS.</p></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> to apply the configuration changes.</p></li><li><p>Restart PostgreSQL for the changes to take effect:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl restart postgresql</span></span></code></pre></div></div><p>If PostgreSQL fails to start, check the logs (for example, <code>/var/log/gitlab/postgresql/current</code>) for more details.</p></li></ol><h3 id=backup-and-restore-a-non-packaged-postgresql-database>Backup and restore a non-packaged PostgreSQL database</h3><p>When using the <a href=https://docs.gitlab.com/administration/backup_restore/backup_gitlab/#backup-command>backup</a> and <a href=https://docs.gitlab.com/administration/backup_restore/restore_gitlab/#restore-for-linux-package-installations>restore</a> commands, GitLab will attempt to use the packaged <code>pg_dump</code> command to create a database backup file and the packaged <code>psql</code> command to restore a backup. This will only work if they are the correct versions. Check the versions of the packaged <code>pg_dump</code> and <code>psql</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>/opt/gitlab/embedded/bin/pg_dump --version </span></span><span style=display:flex><span>/opt/gitlab/embedded/bin/psql --version</span></span></code></pre></div></div><p>If these versions are different from your non-packaged external PostgreSQL, you may encounter the following error output when attempting to run the <a href=https://docs.gitlab.com/administration/backup_restore/backup_gitlab/#backup-command>backup command</a>.</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-plaintext data-lang=plaintext><span style=display:flex><span>Dumping PostgreSQL database gitlabhq_production ... pg_dump: error: server version: 13.3; pg_dump version: 12.6 </span></span><span style=display:flex><span>pg_dump: error: aborting because of server version mismatch</span></span></code></pre></div></div><p>In this example, the error occurs on GitLab 14.1 when using PostgreSQL version 13.3, instead of the <a href=https://docs.gitlab.com/administration/package_information/postgresql_versions/>default shipped PostgreSQL version</a> of 12.6.</p><p>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 <a href=https://www.postgresql.org/download/>https://www.postgresql.org/download/</a> for options.</p><p>Once the correct <code>psql</code> and <code>pg_dump</code> tools are available on your system, follow these steps, using the correct path to the location you installed the new tools:</p><ol><li><p>Add symbolic links to the non-packaged versions:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>ln -s /path/to/new/pg_dump /path/to/new/psql /opt/gitlab/bin/</span></span></code></pre></div></div></li><li><p>Check the versions:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>/opt/gitlab/bin/pg_dump --version </span></span><span style=display:flex><span>/opt/gitlab/bin/psql --version</span></span></code></pre></div></div><p>They should now be the same as your non-packaged external PostgreSQL.</p></li></ol><p>After this is done, ensure that the backup and restore tasks are using the correct executables by running both the <a href=https://docs.gitlab.com/administration/backup_restore/backup_gitlab/#backup-command>backup</a> and <a href=https://docs.gitlab.com/administration/backup_restore/restore_gitlab/#restore-for-linux-package-installations>restore</a> commands.</p><h3 id=upgrade-a-non-packaged-postgresql-database>Upgrade a non-packaged PostgreSQL database</h3><p>You can upgrade the external database after stopping all the processes that are connected to the database (Puma, Sidekiq):</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl stop puma </span></span><span style=display:flex><span>sudo gitlab-ctl stop sidekiq</span></span></code></pre></div></div><p>Before proceeding with the upgrade, note the following:</p><ul><li>Check compatibility between GitLab releases and PostgreSQL versions:<ul><li>Read about which GitLab versions introduced a requirement for a <a href=https://docs.gitlab.com/install/requirements/#postgresql-requirements>minimum PostgreSQL version</a>.</li><li>Read about significant changes to the PostgreSQL versions which <a href=https://docs.gitlab.com/administration/package_information/postgresql_versions/>shipped with the Linux package</a>: The Linux package is tested for compatibility with the major releases of PostgreSQL that it ships with.</li></ul></li><li>When using GitLab backup or restore, you <em>must</em> keep the same version of GitLab. If you plan to upgrade to a later GitLab version as well, upgrade PostgreSQL first.</li><li>The <a href=https://docs.gitlab.com/administration/backup_restore/backup_gitlab/#backup-command>backup and restore commands</a> can be used to back up and restore the database to a later version of PostgreSQL.</li><li>If a PostgreSQL version is specified with <code>postgresql['version']</code> that doesn’t ship with that Linux package release, the <a href=https://docs.gitlab.com/administration/package_information/postgresql_versions/>default version in the compatibility table</a> determines which client binaries (such as the PostgreSQL backup/restore binaries) are active.</li></ul><p>The following example demonstrates upgrading from a database host running PostgreSQL 13 to another database host running PostgreSQL 14 and incurs downtime:</p><ol><li><p>Spin up a new PostgreSQL 14 database server that’s set up according to the <a href=https://docs.gitlab.com/install/requirements/#database>database requirements</a>.</p></li><li><p>Ensure that the compatible versions of <code>pg_dump</code> and <code>pg_restore</code> are being used on the GitLab Rails instance. To amend GitLab configuration, edit <code>/etc/gitlab/gitlab.rb</code> and specify the value of <code>postgresql['version']</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'version'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#666>14</span></span></span></code></pre></div></div></li><li><p>Reconfigure GitLab:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure</span></span></code></pre></div></div></li><li><p>Stop GitLab (note that this step causes downtime):</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl stop</span></span></code></pre></div></div></li></ol><div class="alert alert-type-warning"><p>The backup command requires <a href=https://docs.gitlab.com/administration/backup_restore/backup_gitlab/#back-up-and-restore-for-installations-using-pgbouncer>additional parameters</a> when your installation is using PgBouncer.</p></div><ol><li><p>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.</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-backup create <span style=color:#19177c>SKIP</span><span style=color:#666>=</span>repositories,uploads,builds,artifacts,lfs,pages,registry</span></span></code></pre></div></div></li><li><p>Shutdown the PostgreSQL 13 database host.</p></li><li><p>Edit <code>/etc/gitlab/gitlab.rb</code> and update the <code>gitlab_rails['db_host']</code> setting to point to the PostgreSQL database 14 host.</p></li><li><p>Reconfigure GitLab:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure</span></span></code></pre></div></div><div class="alert alert-type-warning"><p>The backup command requires <a href=https://docs.gitlab.com/administration/backup_restore/backup_gitlab/#back-up-and-restore-for-installations-using-pgbouncer>additional parameters</a> when your installation is using PgBouncer.</p></div></li><li><p>Restore the database using the database backup file created earlier, and be sure to answer <strong>no</strong> when asked “This task will now rebuild the authorized_keys file”:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span><span style=color:#408080;font-style:italic># Use the backup timestamp https://docs.gitlab.com/administration/backup_restore/backup_gitlab/#backup-timestamp</span> </span></span><span style=display:flex><span>sudo gitlab-backup restore <span style=color:#19177c>BACKUP</span><span style=color:#666>=</span><backup-timestamp></span></span></code></pre></div></div></li><li><p>Start GitLab:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl start</span></span></code></pre></div></div></li><li><p>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.</p><p>If the upgrade was “in-place” using <code>pg_upgrade</code>, run the following query on the PostgreSQL database console:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-SQL data-lang=SQL><span style=display:flex><span><span style=color:green;font-weight:700>SET</span><span style=color:#bbb> </span>statement_timeout<span style=color:#bbb> </span><span style=color:#666>=</span><span style=color:#bbb> </span><span style=color:#666>0</span>;<span style=color:#bbb> </span><span style=color:green;font-weight:700>ANALYZE</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>VERBOSE</span>;</span></span></code></pre></div></div><p>The execution time of the <code>ANALYZE</code> command can vary significantly depending on your database size. To monitor the progress of this operation, you can periodically run the following query in another PostgreSQL database console. The <code>tables_remaining</code> column should gradually reach <code>0</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-SQL data-lang=SQL><span style=display:flex><span><span style=color:green;font-weight:700>SELECT</span><span style=color:#bbb> </span></span></span><span style=display:flex><span><span style=color:#bbb> </span><span style=color:green;font-weight:700>COUNT</span>(<span style=color:#666>*</span>)<span style=color:#bbb> </span><span style=color:green;font-weight:700>AS</span><span style=color:#bbb> </span>total_tables,<span style=color:#bbb> </span></span></span><span style=display:flex><span><span style=color:#bbb> </span><span style=color:green;font-weight:700>SUM</span>(<span style=color:green;font-weight:700>CASE</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>WHEN</span><span style=color:#bbb> </span>last_analyze<span style=color:#bbb> </span><span style=color:green;font-weight:700>IS</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>NULL</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>OR</span><span style=color:#bbb> </span>last_analyze<span style=color:#bbb> </span><span style=color:#666><</span><span style=color:#bbb> </span>(NOW()<span style=color:#bbb> </span><span style=color:#666>-</span><span style=color:#bbb> </span><span style=color:green>INTERVAL</span><span style=color:#bbb> </span><span style=color:#ba2121>'2 hours'</span>)<span style=color:#bbb> </span><span style=color:green;font-weight:700>THEN</span><span style=color:#bbb> </span><span style=color:#666>1</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>ELSE</span><span style=color:#bbb> </span><span style=color:#666>0</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>END</span>)<span style=color:#bbb> </span><span style=color:green;font-weight:700>AS</span><span style=color:#bbb> </span>tables_remaining<span style=color:#bbb> </span></span></span><span style=display:flex><span><span style=color:#bbb></span><span style=color:green;font-weight:700>FROM</span><span style=color:#bbb> </span>pg_stat_user_tables;</span></span></code></pre></div></div><p>If the upgrade used <code>pg_dump</code> and <code>pg_restore</code>, run the following query on the PostgreSQL database console:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-SQL data-lang=SQL><span style=display:flex><span><span style=color:green;font-weight:700>SET</span><span style=color:#bbb> </span>statement_timeout<span style=color:#bbb> </span><span style=color:#666>=</span><span style=color:#bbb> </span><span style=color:#666>0</span>;<span style=color:#bbb> </span><span style=color:green;font-weight:700>VACUUM</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>VERBOSE</span><span style=color:#bbb> </span><span style=color:green;font-weight:700>ANALYZE</span>;</span></span></code></pre></div></div></li></ol><h3 id=seed-the-database-fresh-installs-only>Seed the database (fresh installs only)</h3><div class="alert alert-type-warning"><p>This is a destructive command; do not run it on an existing database.</p></div><p>The Linux package installation does not seed your external database. Run the following command to import the schema and create the first administration user:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span><span style=color:#408080;font-style:italic># Remove 'sudo' if you are the 'git' user</span> </span></span><span style=display:flex><span>sudo gitlab-rake gitlab:setup</span></span></code></pre></div></div><p>If you want to specify a password for the default <code>root</code> user, specify the <code>initial_root_password</code> setting in <code>/etc/gitlab/gitlab.rb</code> before running the <code>gitlab:setup</code> command above:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'initial_root_password'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'nonstandardpassword'</span></span></span></code></pre></div></div><p>If you want to specify the initial registration token for shared GitLab Runners, specify the <code>initial_shared_runners_registration_token</code> setting in <code>/etc/gitlab/gitlab.rb</code> before running the <code>gitlab:setup</code> command:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'initial_shared_runners_registration_token'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'token'</span></span></span></code></pre></div></div><h3 id=pin-the-packaged-postgresql-version-fresh-installs-only>Pin the packaged PostgreSQL version (fresh installs only)</h3><p>The Linux package ships with <a href=https://docs.gitlab.com/administration/package_information/postgresql_versions/>different PostgreSQL versions</a> and initializes the default version if not specified otherwise.</p><p>To initialize PostgreSQL with a non-default version, you can set <code>postgresql['version']</code> to the major version one of the <a href=https://docs.gitlab.com/administration/package_information/postgresql_versions/>packaged PostgreSQL versions</a> prior to the initial reconfigure. For example, in GitLab 15.0 you can use <code>postgresql['version'] = 12</code> to use PostgreSQL 12 instead of the default of PostgreSQL 13.</p><div class="alert alert-type-warning"><p>Setting <code>postgresql['version']</code> 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 <a href=/omnibus/settings/database/#revert-packaged-postgresql-server-to-the-previous-version>Revert packaged PostgreSQL server to the previous version</a>.</p></div><p>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.</p><h2 id=provide-sensitive-data-configuration-to-gitlab-rails-without-plain-text-storage>Provide sensitive data configuration to GitLab Rails without plain text storage</h2><p>For more information, see the example in <a href=/omnibus/settings/configuration/#provide-the-postgresql-user-password-to-gitlab-rails>configuration documentation</a>.</p><h2 id=application-settings-for-the-database>Application Settings for the Database</h2><h3 id=disabling-automatic-database-migration>Disabling automatic database migration</h3><p>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.</p><p>Edit <code>/etc/gitlab/gitlab.rb</code> to append:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span><span style=color:#408080;font-style:italic># Enable or disable automatic database migrations</span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># on all hosts except the designated deploy node</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'auto_migrate'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:green>false</span></span></span></code></pre></div></div><p><code>/etc/gitlab/gitlab.rb</code> should have file permissions <code>0600</code> because it contains plain-text passwords.</p><p>The next time hosts carrying the above configuration are reconfigured, the migration steps are not performed.</p><p>To avoid schema-related post-upgrade errors, the host marked as <a href=https://docs.gitlab.com/update/zero_downtime/#multi-node--ha-deployment>the deploy node</a> must have <code>gitlab_rails['auto_migrate'] = true</code> during upgrades.</p><h3 id=setting-client-statement_timeout>Setting client statement_timeout</h3><p>The amount of time that Rails will wait for a database transaction to complete before timing out can now be adjusted with the <code>gitlab_rails['db_statement_timeout']</code> setting. By default, this setting is not used.</p><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_statement_timeout'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#666>45000</span></span></span></code></pre></div></div><p>In this case, the client <code>statement_timeout</code> is set to 45 seconds. The value is specified in milliseconds.</p><h3 id=setting-connection-timeout>Setting connection timeout</h3><p>The amount of time that Rails will wait for a PostgreSQL connection attempt to succeed before timing out can be adjusted with the <code>gitlab_rails['db_connect_timeout']</code> setting. By default, this setting is not used:</p><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-ruby data-lang=ruby><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'db_connect_timeout'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#666>5</span></span></span></code></pre></div></div></li><li><p>Reconfigure GitLab:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure</span></span></code></pre></div></div></li></ol><p>In this case, the client <code>connect_timeout</code> is set to 5 seconds. The value is specified in seconds. A minimum value of 2 seconds applies. Setting this to <code><= 0</code> or not specifying the setting at all disables the timeout.</p><h3 id=setting-tcp-controls>Setting TCP controls</h3><p>The Rails PostgreSQL adapter provides a series of TCP connection controls that may be tuned to improve performance. Consult the <a href=https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-KEEPALIVES>PostgreSQL upstream documentation for more information about each parameter</a>.</p><p>The Linux package sets no defaults for these values and instead uses the defaults provided by the PostgreSQL adapter. Override them in <code>gitlab.rb</code> using the parameters noted in the table below and then run <code>gitlab-ctl reconfigure</code>.</p><table><thead><tr><th>PostgreSQL parameter</th><th><code>gitlab.rb</code> parameter</th></tr></thead><tbody><tr><td><code>keepalives</code></td><td><code>gitlab_rails['db_keepalives']</code></td></tr><tr><td><code>keepalives_idle</code></td><td><code>gitlab_rails['db_keepalives_idle']</code></td></tr><tr><td><code>keepalives_interval</code></td><td><code>gitlab_rails['db_keepalives_interval']</code></td></tr><tr><td><code>keepalives_count</code></td><td><code>gitlab_rails['db_keepalives_count']</code></td></tr><tr><td><code>tcp_user_timeout</code></td><td><code>gitlab_rails['db_tcp_user_timeout']</code></td></tr></tbody></table><h2 id=automatic-database-reindexing>Automatic database reindexing</h2><div class="alert alert-type-warning"><p>This is an experimental feature that isn’t enabled by default.</p></div><p>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.</p><p>The reindexing task can be started regularly through a cronjob. To configure the cronjob, <code>gitlab_rails['database_reindexing']['enable']</code> should be set to <code>true</code>.</p><p>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.</p><p>By default, this starts the cronjob every hour during weekends (likely a low-traffic time) only.</p><p>You can change the schedule by refining the following settings:</p><ol><li><p>Edit <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'database_reindexing'</span><span style=color:#666>][</span><span style=color:#ba2121>'hour'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'*'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'database_reindexing'</span><span style=color:#666>][</span><span style=color:#ba2121>'minute'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#666>0</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'database_reindexing'</span><span style=color:#666>][</span><span style=color:#ba2121>'month'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'*'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'database_reindexing'</span><span style=color:#666>][</span><span style=color:#ba2121>'day_of_month'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'*'</span> </span></span><span style=display:flex><span>gitlab_rails<span style=color:#666>[</span><span style=color:#ba2121>'database_reindexing'</span><span style=color:#666>][</span><span style=color:#ba2121>'day_of_week'</span><span style=color:#666>]</span> <span style=color:#666>=</span> <span style=color:#ba2121>'0,6'</span></span></span></code></pre></div></div></li><li><p>Reconfigure GitLab:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure</span></span></code></pre></div></div></li></ol><h2 id=packaged-postgresql-deployed-in-an-hageo-cluster>Packaged PostgreSQL deployed in an HA/Geo Cluster</h2><h3 id=upgrading-a-gitlab-ha-cluster>Upgrading a GitLab HA cluster</h3><p>To upgrade the PostgreSQL version in a Patroni cluster see <a href=https://docs.gitlab.com/administration/postgresql/replication_and_failover/#upgrading-postgresql-major-version-in-a-patroni-cluster>Upgrading PostgreSQL major version in a Patroni cluster</a>.</p><h3 id=upgrading-a-gitlab-ha-repmgr-cluster>Upgrading a GitLab HA Repmgr cluster</h3><div class="alert alert-type-note"><p>If you are upgrading to PostgreSQL 12, you need to switch from Repmgr to Patroni first see <a href=https://docs.gitlab.com/administration/postgresql/replication_and_failover/#switching-from-repmgr-to-patroni>Switching from Repmgr to Patroni</a>.</p></div><p>These instructions are provided for upgrading an older GitLab cluster to PostgreSQL 11 when using Repmgr.</p><p>If <a href=https://docs.gitlab.com/administration/postgresql/>PostgreSQL is configured for high availability</a>, <code>pg-upgrade</code> 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.</p><p>Follow the steps below to upgrade the database nodes:</p><ol><li><p>Secondary nodes must be upgraded before the primary node.</p><ol><li><p>On the secondary nodes, edit <code>/etc/gitlab/gitlab.rb</code> to include the following:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span><span style=color:#408080;font-style:italic># Replace X with the number of DB nodes + 1</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'max_replication_slots'</span><span style=color:#666>]</span> <span style=color:#666>=</span> X</span></span></code></pre></div></div></li><li><p>Run <code>gitlab-ctl reconfigure</code> to update the configuration.</p></li><li><p>Run <code>sudo gitlab-ctl restart postgresql</code> to get PostgreSQL restarted with the new configuration.</p></li><li><p>On running <code>pg-upgrade</code> on a PostgreSQL secondary node, the node will be removed from the cluster.</p></li><li><p>Once all the secondary nodes are upgraded using <code>pg-upgrade</code>, the user will be left with a single-node cluster that has only the primary node.</p></li><li><p><code>pg-upgrade</code>, 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.</p></li></ol></li><li><p>Once all secondary nodes are upgraded, run <code>pg-upgrade</code> on the primary node.</p><ol><li><p>On the primary node, edit <code>/etc/gitlab/gitlab.rb</code> to include the following:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span><span style=color:#408080;font-style:italic># Replace X with the number of DB nodes + 1</span> </span></span><span style=display:flex><span>postgresql<span style=color:#666>[</span><span style=color:#ba2121>'max_replication_slots'</span><span style=color:#666>]</span> <span style=color:#666>=</span> X</span></span></code></pre></div></div></li><li><p>Run <code>gitlab-ctl reconfigure</code> to update the configuration.</p></li><li><p>Run <code>sudo gitlab-ctl restart postgresql</code> to get PostgreSQL restarted with the new configuration.</p></li><li><p>On a primary node, <code>pg-upgrade</code> will update the existing data to match the new PostgreSQL version.</p></li></ol></li><li><p>Recreate the secondary nodes by running the following command on each of them</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl repmgr standby setup MASTER_NODE_NAME</span></span></code></pre></div></div></li><li><p>Check if the repmgr cluster is back to the original state</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>gitlab-ctl repmgr cluster show</span></span></code></pre></div></div></li></ol><h3 id=troubleshooting-upgrades-in-an-ha-cluster>Troubleshooting upgrades in an HA cluster</h3><p>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 <code>gitlab-ctl reconfigure</code> to downgrade the version of the PostgreSQL utilities it uses on that node. Move (or remove) the directory to prevent this:</p><ul><li><code>mv /var/opt/gitlab/postgresql/data/ /var/opt/gitlab/postgresql/data.$(date +%s)</code></li></ul><p>If you encounter the following error when recreating the secondary nodes with <code>gitlab-ctl repmgr standby setup MASTER_NODE_NAME</code>, ensure that you have <code>postgresql['max_replication_slots'] = X</code> (where <code>X</code> is the number of DB nodes + 1), is included in <code>/etc/gitlab/gitlab.rb</code>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>pg_basebackup: could not create temporary replication slot <span style=color:#ba2121>"pg_basebackup_12345"</span>: ERROR: all replication slots are in use </span></span><span style=display:flex><span>HINT: Free one or increase max_replication_slots.</span></span></code></pre></div></div><h3 id=upgrading-a-geo-instance>Upgrading a Geo instance</h3><p>Since Geo depends on PostgreSQL streaming replication by default, there are additional considerations when upgrading GitLab and/or when upgrading PostgreSQL is described below.</p><h4 id=caveats-when-upgrading-postgresql-with-geo>Caveats when upgrading PostgreSQL with Geo</h4><div class="alert alert-type-warning"><p>When using Geo, upgrading PostgreSQL <strong>requires downtime on all secondaries</strong>.</p></div><p>When using Geo, upgrading PostgreSQL <strong>requires downtime on all secondaries</strong> because it requires re-initializing PostgreSQL replication to Geo <strong>secondaries</strong>. 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 <a href=https://www.postgresql.org/docs/11/pgupgrade.html>PostgreSQL documentation</a> for more.</p><h4 id=how-to-upgrade-postgresql-when-using-geo>How to upgrade PostgreSQL when using Geo</h4><p>To upgrade PostgreSQL, you will need the name of the replication slot, and the replication user’s password.</p><ol><li><p>Find the name of the existing replication slot on the Geo primary’s database node, run:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-psql -qt -c <span style=color:#ba2121>'select slot_name from pg_replication_slots'</span></span></span></code></pre></div></div><p>If you can’t find your <code>slot_name</code> here, or there is no output returned, your Geo secondaries may not be healthy. In that case, make sure the <a href=https://docs.gitlab.com/administration/geo/replication/troubleshooting/common/#health-check-rake-task>secondaries are healthy and replication is working</a>.</p><p>Even if the query is empty, you can try to reinitialize the secondary database with the <code>slot_name</code> found on the <a href=https://docs.gitlab.com/administration/geo_sites/>Geo sites admin area</a>.</p></li><li><p>Gather the replication user’s password. It was set while setting up Geo in <a href=https://docs.gitlab.com/administration/geo/setup/database/#step-1-configure-the-primary-site>Step 1. Configure the primary site</a>.</p></li><li><p>Optional. <a href=https://docs.gitlab.com/administration/geo/#pausing-and-resuming-replication>Pause replication on each <strong>secondary</strong> site</a> to protect their disaster recovery (DR) capability.</p></li><li><p>Manually upgrade PostgreSQL on the Geo primary. Run on the Geo primary’s database node:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl pg-upgrade</span></span></code></pre></div></div><p>Wait for the <strong>primary database</strong> to finish upgrading before beginning the following step, so the secondary can remain ready as a backup. Afterward, you can upgrade the <strong>tracking database</strong> in parallel with the <strong>secondary database</strong>.</p></li><li><p>Manually upgrade PostgreSQL on the Geo secondaries. Run on the Geo <strong>secondary database</strong> and also on the <strong>tracking database</strong>:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl pg-upgrade</span></span></code></pre></div></div></li><li><p>Restart the database replication on the Geo <strong>secondary database</strong> using the command:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl replicate-geo-database --slot-name<span style=color:#666>=</span>SECONDARY_SLOT_NAME --host<span style=color:#666>=</span>PRIMARY_HOST_NAME --sslmode<span style=color:#666>=</span>verify-ca</span></span></code></pre></div></div><p>You will be prompted for the replication user’s password of the primary. Replace <code>SECONDARY_SLOT_NAME</code> with the slot name retrieved from the first step above.</p></li><li><p><a href=https://docs.gitlab.com/administration/restart_gitlab/#omnibus-gitlab-reconfigure>Reconfigure GitLab</a> on the Geo <strong>secondary database</strong> to update the <code>pg_hba.conf</code> file. This is needed because <code>replicate-geo-database</code> replicates the primary’s file to the secondary.</p></li><li><p>If you paused replication in step 3, <a href=https://docs.gitlab.com/administration/geo/#pausing-and-resuming-replication>resume replication on each <strong>secondary</strong></a>. Then, restart <code>puma</code>, <code>sidekiq</code>, and <code>geo-logcursor</code>.</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl hup puma </span></span><span style=display:flex><span>sudo gitlab-ctl restart sidekiq </span></span><span style=display:flex><span>sudo gitlab-ctl restart geo-logcursor</span></span></code></pre></div></div></li><li><p>Navigate to <code>https://your_primary_server/admin/geo/nodes</code> and ensure that all nodes are healthy.</p></li></ol><h2 id=connecting-to-the-postgresql-database>Connecting to the PostgreSQL database</h2><p>If you need to connect to the PostgreSQL database, you can connect as the application user:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-rails dbconsole --database main</span></span></code></pre></div></div><h2 id=troubleshooting>Troubleshooting</h2><h3 id=set-default_transaction_isolation-into-read-committed>Set <code>default_transaction_isolation</code> into <code>read committed</code></h3><p>If you see errors similar to the following in your <code>production/sidekiq</code> log:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-plaintext data-lang=plaintext><span style=display:flex><span>ActiveRecord::StatementInvalid PG::TRSerializationFailure: ERROR: could not serialize access due to concurrent update</span></span></code></pre></div></div><p>Chances are your database’s <code>default_transaction_isolation</code> configuration is not in line with the GitLab application requirement. You can check this configuration by connecting to your PostgreSQL database and run <code>SHOW default_transaction_isolation;</code>. GitLab application expects <code>read committed</code> to be configured.</p><p>This <code>default_transaction_isolation</code> configuration is set in your <code>postgresql.conf</code> 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.</p><h3 id=could-not-load-library-plpgsqlso>Could not load library <code>plpgsql.so</code></h3><p>You might see errors similar to the following while running Database migrations or in the PostgreSQL/Patroni logs:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-plaintext data-lang=plaintext><span style=display:flex><span>ERROR: could not load library "/opt/gitlab/embedded/postgresql/12/lib/plpgsql.so": /opt/gitlab/embedded/postgresql/12/lib/plpgsql.so: undefined symbol: EnsurePortalSnapshotExists</span></span></code></pre></div></div><p>This error is caused due to not restarting PostgreSQL after the underlying version changed. To fix this error:</p><ol><li><p>Run one of the following commands:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span><span style=color:#408080;font-style:italic># For PostgreSQL</span> </span></span><span style=display:flex><span>sudo gitlab-ctl restart postgresql </span></span><span style=display:flex><span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># For Patroni</span> </span></span><span style=display:flex><span>sudo gitlab-ctl restart patroni </span></span><span style=display:flex><span> </span></span><span style=display:flex><span><span style=color:#408080;font-style:italic># For Geo PostgreSQL</span> </span></span><span style=display:flex><span>sudo gitlab-ctl restart geo-postgresql</span></span></code></pre></div></div></li><li><p>Reconfigure GitLab:</p><div class=codeblock-wrapper><div data-vue-app=codeblock-toolbar></div><div class=highlight><pre tabindex=0 style=-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-shell data-lang=shell><span style=display:flex><span>sudo gitlab-ctl reconfigure</span></span></code></pre></div></div></li></ol><h3 id=database-cpu-load-very-high>Database CPU load very high</h3><p>If the database CPU load is very high, it could be caused by the <a href=https://docs.gitlab.com/ci/pipelines/settings/#auto-cancel-redundant-pipelines>auto cancel redundant pipelines setting</a>. For more details, see <a href=https://gitlab.com/gitlab-org/gitlab/-/issues/435250>issue 435250</a>.</p><p>To work around this issue:</p><ul><li>You can allocate more CPU resources to the database server.</li><li>If Sidekiq is overloaded, you might need to <a href=https://docs.gitlab.com/administration/sidekiq/extra_sidekiq_processes/#start-multiple-processes>add more Sidekiq processes</a> for the <code>ci_cancel_redundant_pipelines</code> queue if your projects have a very large number of pipelines.</li><li>You can enable the <code>disable_cancel_redundant_pipelines_service</code> feature flag to disable this setting instance-wide and see if the CPU load goes down. This disables the feature for all projects, and can lead to increased resource use by pipelines that are no longer being cancelled automatically.</li></ul></div><div class="help-feedback gl-rounded-base gl-mt-7 gl-p-5"><div class="help-feedback-container gl-flex"><div class=help-feedback-question-icon></div><button class="gl-flex gl-flex-row gl-items-center gl-justify-between" data-target=.feedback-wrapper data-toggle=collapse><h2 class="gl-m-0 gl-font-bold gl-text-lg">Help & feedback</h2><span class=help-feedback-toggle></span></button></div><div class="feedback-wrapper collapse"><div class="help-feedback-container xl:gl-flex"><div class="feedback gl-pr-5"><h3>Docs</h3><p><a href=https://gitlab.com/gitlab-org/omnibus-gitlab/-/blob/master/doc/settings/database.md>Edit this page</a> to fix an error or add an improvement in a merge request.</p><p><a href="https://gitlab.com/gitlab-org/gitlab/-/issues/new?issuable_template=Documentation">Create an issue</a> to suggest an improvement to this page.</p><h3>Product</h3><p><a href="https://gitlab.com/gitlab-org/gitlab/-/issues/new?issue%5Bdescription%5D=Describe%20what%20you%20would%20like%20to%20see%20improved.%0A%0A%3C!--%20Don%27t%20edit%20below%20this%20line%20--%3E%0A%0A%2Flabel%20~%22docs%5C-comments%22%20&issue%5Btitle%5D=Docs%20-%20product%20feedback:%20Write%20your%20title">Create an issue</a> if there's something you don't like about this feature.</p><p><a href="https://gitlab.com/gitlab-org/gitlab/-/issues/new?issuable_template=Feature%20proposal%20-%20detailed&issue%5Btitle%5D=Docs%20feedback%20-%20feature%20proposal:%20Write%20your%20title">Propose functionality</a> by submitting a feature request.</p><h3>Feature availability and product trials</h3><p><a href=https://about.gitlab.com/pricing/>View pricing</a> to see all GitLab tiers and features, or to upgrade.</p><p><a href=https://about.gitlab.com/free-trial/>Try GitLab for free</a> with access to all features for 30 days.</p></div><div class=help><h3>Get help</h3><p>If you didn't find what you were looking for, <a href=/search/>search the docs</a>.</p><p>If you want help with something specific and could use community support, <a href="https://forum.gitlab.com/new-topic?title=topic%20title&body=topic%20body&tags=docs-feedback">post on the GitLab forum</a>.</p><p>For problems setting up or using this feature (depending on your GitLab subscription).</p><a class="btn btn-default btn-md gl-button" href=https://about.gitlab.com/support/>Request support</a></div></div></div></div></div><footer class=gl-pb-6><div class="gl-flex gl-justify-between gl-border-0 gl-border-t gl-border-solid gl-border-gray-100 gl-pt-5"><a href=https://gitlab.com/dashboard><img src=/gitlab-logo.svg alt="GitLab logo"></a><ul class="docs-social gl-list-none gl-flex"><li><a href=https://www.facebook.com/gitlab class=facebook><span class=gl-sr-only>Facebook</span></a></li><li><a href=https://www.linkedin.com/company/gitlab-com class=linkedin><span class=gl-sr-only>LinkedIn</span></a></li><li><a href=https://twitter.com/gitlab class=twitter><span class=gl-sr-only>Twitter</span></a></li><li><a href=https://www.youtube.com/channel/UCnMGQ8QHMAnVIsI3xJrihhg class=youtube><span class=gl-sr-only>YouTube</span></a></li></ul></div><ul class="docs-footer-links gl-list-none gl-pl-0 xl:gl-flex"><li class="gl-pr-5 gl-mb-2 xl:gl-mb-0"><a href=https://gitlab.com/gitlab-org/technical-writing/docs-gitlab-com class=gl-text-gray-600>Docs Repo</a></li><li class="gl-pr-5 gl-mb-2 xl:gl-mb-0"><a href=https://about.gitlab.com/company/ class=gl-text-gray-600>About GitLab</a></li><li class="gl-pr-5 gl-mb-2 xl:gl-mb-0"><a href=https://about.gitlab.com/terms/ class=gl-text-gray-600>Terms</a></li><li class="gl-pr-5 gl-mb-2 xl:gl-mb-0"><a href=https://about.gitlab.com/privacy/ class=gl-text-gray-600>Privacy Statement</a></li><li class="gl-pr-5 gl-mb-2 xl:gl-mb-0"><button id=ot-sdk-btn class=ot-sdk-show-settings>Cookie Settings</button></li><li class="gl-pr-5 gl-mb-2 xl:gl-mb-0"><a href=https://about.gitlab.com/company/contact/ class=gl-text-gray-600>Contact</a></li></ul><nav class=docs-edit-links aria-label=Footer><ul class="docs-footer-links-secondary gl-list-none gl-pl-0 gl-pb-5 xl:gl-flex"><li><a href=https://gitlab.com/gitlab-org/omnibus-gitlab/-/blob/master/doc/settings/database.md>View page source</a></li><li><a href=https://gitlab.com/-/ide/project/gitlab-org/omnibus-gitlab/edit/master/-/doc/settings/database.md>Edit in Web IDE</a></li><li><a href=https://creativecommons.org/licenses/by-sa/4.0/ class="gl-mb-5 md:gl-mb-0"><img alt="Creative Commons License" src=/by-sa.svg></a></li></ul></nav></footer></div><aside class=sidebar-right><div class="js-toc gl-text-base"></div></aside></div></main><script type=module src=/vite/main.js></script><script async>(function(e,t,n,s,o){e[s]=e[s]||[],e[s].push({"gtm.start":(new Date).getTime(),event:"gtm.js"});var a=t.getElementsByTagName(n)[0],i=t.createElement(n),r=s!="dataLayer"?"&l="+s:"";i.async=!0,i.src="https://www.googletagmanager.com/gtm.js?id="+o+r,a.parentNode.insertBefore(i,a)})(window,document,"script","dataLayer","GTM-NJXWQL")</script><noscript><iframe src="https://www.googletagmanager.com/ns.html?id=GTM-NJXWQL" height=0 width=0 style=display:none;visibility:hidden></iframe></noscript><script async>(function(){var e,t=!1;function n(){t===!1&&(t=!0,Munchkin.init("194-VVC-221",{useBeaconAPI:!0}))}e=document.createElement("script"),e.type="text/javascript",e.async=!0,e.src="https://munchkin.marketo.net/munchkin.js",e.onreadystatechange=function(){(this.readyState=="complete"||this.readyState=="loaded")&&n()},e.onload=n,document.getElementsByTagName("head")[0].appendChild(e)})()</script><script async src=https://cdn.bizible.com/scripts/bizible.js></script><script async>_linkedin_partner_id="30694",window._linkedin_data_partner_ids=window._linkedin_data_partner_ids||[],window._linkedin_data_partner_ids.push(_linkedin_partner_id),function(){var t=document.getElementsByTagName("script")[0],e=document.createElement("script");e.type="text/javascript",e.async=!0,e.src="https://snap.licdn.com/li.lms-analytics/insight.min.js",t.parentNode.insertBefore(e,t)}()</script><noscript><img height=1 width=1 style=display:none alt src="https://dc.ads.linkedin.com/collect/?pid=30694&fmt=gif"></noscript><script src=https://cdn.jsdelivr.net/npm/@gitlab/application-sdk-browser@0.2.8/dist/gl-sdk.min.js></script><script>const GL_PRODUCT_ANALYTICS_JSON={appId:"e1c8d446-8edf-46fa-9e6a-9f964b8675c8",host:"https://collector.prod-1.gl-product-analytics.com",hasCookieConsent:!0};Object.values(GL_PRODUCT_ANALYTICS_JSON).includes("")||(window.glClient=window.glSDK.glClientSDK(),window.glClient?.page())</script></body></html>