GitLab database migration to external PostgreSQL

Hello!



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 actions are guaranteed to work on CentOS 7.7.1908, PostgreSQL 12 and GitLab 12.4.2-ee.0.

Preliminary preparation

We will do three things in advance:



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



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 outside. Open the postgresql.conf file, find the commented out line " #listen_addresses = 'localhost' " and add a line below it as below. Where is 10.0.0.2, 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. The address of my GitLab server is 10.0.0.4.



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, restart the postgresql service:

 systemctl restart postgresql-12.service
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Export GitLab Database

Let's execute, on the GitLab server, backing up 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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The following line should appear:

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Configuring GitLab

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



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 configuration file /etc/gitlab/gitlab.rb we will specify all the data of the external PostgreSQL.



Make a backup copy of the gitlab.rb file:

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

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

 #   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 put a minus, write the reason in the comment.