Migrating GitLab Database to External PostgreSQL

Hello everyone!

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

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

Preliminary Preparation

We will perform three actions in advance:

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

In my case:

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

2. There, in the postgresql.conf file, allow the network interface to accept incoming connections from outside. Open the postgresql.conf file, find the commented line "#listen_addresses = ‘localhost’" and add a line below it as shown. 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 the external database, this must be allowed in the pg_hba.conf file. server The address of my GitLab server is 10.0.0.4.

Open the pg_hba.conf file and add the following line:

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

And finally, restart the PostgreSQL service:

systemctl restart postgresql-12.service

Export the GitLab Database

On the GitLab server, perform a backup of 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

Let's copy this backup 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 the "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

A line like this should appear:

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

Configuring GitLab

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

Changing the password:

sudo -u postgres psql -c "ALTER USER gitlab ENCRYPTED PASSWORD 'PASSWORD' VALID UNTIL 'infinity';"
Password for user postgres:
ALTER ROLE

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

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

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

Now, let's 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 🙂

A big request. If you've downvoted, please leave a reason in the comments.

Source: habr.com