Migrating Gitlab from MySQL to Postgres

Migrating Gitlab from MySQL to Postgres

Apr 23, 2014
Categories: Sysadmin
Tags: linux

Recently, I migrated Apovedas Gitlab installation from using MySQL as database server to PostgreSQL. Thats because until now, I have used the manual gitlab setup guides, and I now want to change this to use the automatic setup/provided packages, which all use postgres.

Install Postgres and create the database #

(Taken from the Gitlab installation manual ):

sudo apt-get install -y postgresql postgresql-client libpq-dev

# Login to PostgreSQL
sudo -u postgres psql -d template1

# Create a user for GitLab.
template1=# CREATE USER git;

# Create the GitLab production database & grant all privileges on database
template1=# CREATE DATABASE gitlabhq_production OWNER git;

# Quit the database session
template1=# \q

# Try connecting to the new database with the new user
sudo -u git -H psql -d gitlabhq_production

Install PGloader #

git cl https://github.com/dimitri/pgloader.git
apt-get install sbcl libsqlite3-dev make curl
cd pgloader
make pgloader
cd ~

Migrate the database #

We need to create a new file containing the commands for pgloader: nano pgloader-commands, and insert the following into it:

 FROM    mysql://gitlab:<your-mysql-password>@localhost/gitlabhq_production
 INTO    postgresql://git@/gitlabhq_production
 WITH    create tables, create indexes, foreign keys, reset sequences;

Then, run it:

sudo service gitlab stop
sudo -u git pgloader/build/bin/pgloader pgloader-commands
rm pgloader-commands

Change Gitlab config, and restart #

cd /home/git/gitlab/config
sudo mv database.yml database.yml.backup
sudo cp database.yml.postgresql database.yml
sudo chown git:git database.yml
sudo chmod 0644 database.yml

# install pg gem
cd /home/git/gitlab
sudo -u git -H bundle install --deployment --without development test mysql aws

# restart gitlab
sudo service gitlab start

# check if app runs, if not, check log
tail -f log/unicorn.stderr.log

Uninstall mysql #

sudo apt-get purge mysql-client mysql-common mysql-server

Done! You are now running Postgres.

Painless way to install NumPy & Co. on OSX →