Migrating Gitlab to PostgreSQL

Advertisement

Currently I'm evaluating Scaleway as an alternative to my vps hosted by Server4you. While setting up a test environment the job to migrate Gitlab from MySQL to PostgreSQL also came up.

I'm using the sameersbn/gitlab docker image. The following describes the steps I took to migrate Gitlab from MySQL to PostgreSQL.

Setup

The Gitlab instance is using the following docker-compose.yml:

version: '2'

services:  
  redis:
    ...
  mysql:
   container_name: gitlab-mysql
   restart: always
   image: sameersbn/mysql:latest
   environment:
    - DB_USER=gitlab
    - DB_PASS=XXXXXXXXXXXXXXXXXXXXXXXX
    - DB_NAME=gitlabhq_production
  volumes:
   - /data/docker/mysql:/var/lib/mysql:Z

  gitlab:
    container_name: gitlab
    restart: always
    image: sameersbn/gitlab:8.12.6
    depends_on:
     - redis
     - mysql
    environment:
      - DB_ADAPTER=mysql2
      - DB_HOST=mysql
      - DB_PORT=3306 
      - DB_USER=server
      - DB_PASS=XXXXXXXXXXXXXXXXXXXXXXXX
      - DB_NAME=gitlabhq_production
      ....

Before anything a backup of Gitlab should be made in case something goes wrong:

docker exec -it gitlab sudo -HEu git bundle exec rake gitlab:backup:create  

Migrating

The first step is to find the network the Gitlab instance uses. In most cases this should be "gitlab_default":

$ docker network ls
NETWORK ID          NAME                DRIVER              SCOPE  
0db20321f873        bridge              bridge              local  
319340864ae5        gitlab_default      bridge              local  
c0693a4b3965        host                host                local  
12a15ff82242        none                null                local  

Gitlab should not be running while the migration is taken place:

docker stop gitlab  

To migrate the database a new PostgreSQL container is required:

docker run -it --rm --name gitlab-postgresql \  
  -v /data/docker/postgresql:/var/lib/postgresql \
  --network=gitlab_default \
  -e DB_USER=gitlab \
  -e DB_PASS=XXXXXXXXXXXXXXXXXXXXXXXX \
  -e DB_NAME=gitlabhq_production \
  -e DB_EXTENSION=pg_trgm \
  sameersbn/postgresql

In order to perform the migration inside docker a bash session inside another PostgreSQL container, which is linked to the MySQL and the new PostgreSQL container, is needed:

docker run -it --rm --name gitlab-postgresql-migrate \  
  --network=gitlab_default \
  --link gitlab-mysql:mysql --link gitlab-postgresql:postgresql \
  sameersbn/postgresql bash

Inside the container gitlab-postgresql-migrate the following commands need to be executed:

apt-get update && apt-get install -y ed git python mysql-client  
git clone https://github.com/gitlabhq/mysql-postgresql-converter.git -b gitlab  
cd mysql-postgresql-converter

# dump the mysql db
mysqldump --compatible=postgresql --default-character-set=utf8 -r gitlabhq_production.mysql -h mysql -u gitlab --password=XXXXXXXXXXXXXXXXXXXXXXXX gitlabhq_production

# convert dump to postgresql
python db_converter.py gitlabhq_production.mysql gitlabhq_production.psql

# import into postgesql
PGPASSWORD=XXXXXXXXXXXXXXXXXXXXXXXX psql -h postgresql -U gitlab -f gitlabhq_production.psql -d gitlabhq_production 

# all done
exit  

After the import into the new database the entire Gitlab stack (Gitlab, redis, MySQL) needs to be stopped

docker-compose down  

Finally some modifications to the docker-compose.yml are required to use PostgreSQL instead of MySQL

version: '2'

services:  
  redis:
    ...
  postgresql:
    container_name: gitlab-postgresql
    restart: always
    image: sameersbn/postgresql:latest
    environment:
      - DB_USER=gitlab
      - DB_PASS=XXXXXXXXXXXXXXXXXXXXXXXX
      - DB_NAME=gitlabhq_production
      - DB_EXTENSION=pg_trgm
    volumes:
      - /data/docker/postgresql:/var/lib/postgresql:Z

  gitlab:
    container_name: gitlab
    restart: always
    image: sameersbn/gitlab:8.12.6
    depends_on:
     - redis
     - postgresql
    environment:
      - DB_ADAPTER=postgresql
      - DB_HOST=postgresql
      - DB_PORT=5432
      - DB_USER=gitlab
      - DB_PASS=XXXXXXXXXXXXXXXXXXXXXXXX
      - DB_NAME=gitlabhq_production
      ....

After the modifications to the docker-compose.yml the entire stack can be fired up and everything should work:

docker-compose up  

Sources