Migrating the GitLab database to an external PostgreSQL

Hi all!

In this article, we will migrate the GitLab database from the internal PostgreSQL, which is installed with GitLab, to the external PostgreSQL, which is already installed on another server.

NOTE
All steps are guaranteed to work on CentOS 7.7.1908, PostgreSQL 12 and GitLab 12.4.2-ee.0.

Savant Providence

Let's do three things first:

1. On the PostgreSQL server, add a firewall rule to allow incoming connections to PostgreSQL port 5432/TCP.

In my case:

firewall-cmd --add-service=postgresql --zone=internal --permanent
success
firewall-cmd --reload
success

2. In the same place, but in the postgresql.conf file, allow the network interface to accept incoming connections from the outside. Open the postgresql.conf file, find the commented line "#listen_addresses = 'localhost'' and below it add the line as below. Where - 10.0.0.2 is the address of your interface.

In my case:

vi /var/lib/pgsql/12/data/postgresql.conf
# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = 'localhost, 10.0.0.2'
                                        # comma-separated list of addresses;

3. Since the GitLab server will connect to an external database, this must be enabled on the PostgreSQL server in the pg_hba.conf file. My GitLab server address is 10.0.0.4.

Let's open the pg_hba.conf file and add the line there:

host    all             gitlab               10.0.0.4/24             md5

It will look like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                     md5

# IPv4 local connections:
host    all             postgres             127.0.0.1/32            md5
host    all             gitlab               10.0.0.4/24             md5

Finally, we restart the postgresql service:

systemctl restart postgresql-12.service

Export GitLab database

Let's execute, on the GitLab server, backup the database:

sudo -u gitlab-psql /opt/gitlab/embedded/bin/pg_dumpall -U gitlab-psql --host=/var/opt/gitlab/postgresql > /tmp/internal-gitlab.sql

The backup appeared in /tmp:

ls -lh
total 836K
-rw-r--r--. 1 root root 836K Nov 18 12:59 internal-gitlab.sql

Copy this copy to the PostgreSQL server:

scp /tmp/internal-gitlab.sql 10.0.0.2:/tmp/
internal-gitlab.sql                                                                               100%  835KB  50.0MB/s   00:00

Import "internal-gitlab.sql" into PostgreSQL

Import the database into PostgreSQL:

sudo -u postgres psql -f /tmp/internal-gitlab.sql

Check that the database is now in PostgreSQL:

sudo -u postgres psql -l

This line should appear:

gitlabhq_production | gitlab   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

gitlab configuration

After importing the database into PostgreSQL, the gitlab user was created. You need to change this user's password.

Change password:

sudo -u postgres psql -c "ALTER USER gitlab ENCRYPTED PASSWORD 'ПАРОЛЬ' VALID UNTIL 'infinity';"
Password for user postgres:
ALTER ROLE

Then, on the GitLab server, in the /etc/gitlab/gitlab.rb configuration file, we specify all the external PostgreSQL data.

Let's make a backup copy of the gitlab.rb file:

cp /etc/gitlab/gitlab.rb /etc/gitlab/gitlab.rb.orig

Now add these lines to the end of the gitlab.rb file:

# Отключить встроенный PostgreSQL.
postgresql['enable'] = false

# Данные для подключения к внешней базе. Указывайте свои.
gitlab_rails['db_adapter'] = 'postgresql'
gitlab_rails['db_encoding'] = 'utf8'
gitlab_rails['db_host'] = '10.0.0.2'
gitlab_rails['db_port'] = 5432
gitlab_rails['db_database'] = "gitlabhq_production"
gitlab_rails['db_username'] = 'gitlab'
gitlab_rails['db_password'] = '******'

Save the file /etc/gitlab/gitlab.rb and reconfigure GitLab:

gitlab-ctl reconfigure && gitlab-ctl restart

That's all 🙂

Big request. If you put a minus, write the reason in the comment.

Source: habr.com