First, I’ll go ahead and admit that I am biased against PostgreSQL:  It’s clunky, has a complex authentication system, and I can never get it to work completely to my liking.  Sure, most of that is probably inexperience, but after 5 years of working with it, I try to find any means possible to get it off my systems.  One of the last major sticking points to achieving that goal on my dedicated server is GitLab.

I use GitLab to archive my old projects, and to keep more secretive projects version-controlled, such as HWLink and my various adventures in reverse-engineering games and software.  It also allows me to control access to these various repositories, so I can invite friends and co-workers to coordinate on things.  In some ways, I like it better than GitHub, as the documentation and issue tracking systems have more features.  On the other hand, everyone and their dog has an account on GitHub and people are hesistant to open an account on an unknown domain. So, my two private GitLab instances stay mostly a personal hobby.

However, back when I installed it on my systems, MySQL wasn’t well-supported by GitLab.  In fact, as I recall, bundler didn’t even permit me to install mysql2 for some reason.  Therefore, after much beard rubbing, I decided to go ahead and throw it into PostgreSQL.  The operation went smoothly (although I somehow managed to screw up the authentication on the dedicated server, requiring a password/username connection rather than account-based authentication), and I moved on to other things.

After installing HHVM a while back, however, it became clear that PostgreSQL was more of a liability than a benefit:  HHVM currently doesn’t support PostgreSQL, despite several PRs and feature requests, and the only other app using it is the 7chan rewrite I’m working on, and even that is being migrated to MariaDB. So, to ease the memory and CPU load on my system, I decided to take the leap and switch GitLab to MariaDB, as well.

However, those of you who just arrived here from El Google have probably noted that there’s no written procedure for doing so, and the pg2mysql PHP script is poorly maintained and misses many things.  Today, I will save you days of blood, sweat, and beers and tell you how you, Joe Sixpack, can switch over to MySQL seamlessly.

Safety Third

It goes without saying, but I will say it anyway:

Back up your shit. 

GitLab is a complex beast and the operation we’re about to take will be just about as complex.  If anything goes wrong, you will need to have something to go back to.

In addition, you’re going to want to do this on a VM or test server first so you know what pitfalls you’ll come across when you do the real deal.  I’ve got a physical test server with a near-identical environment to the production server for this very purpose, and performed this operation there first.  Operating on a test server will also allow you to perform the operation more efficiently when the time comes.

Getting Up to Speed

The first step I do, after backing everything up, is to update GitLab following the update script provided in the repository.  This is important, as any schema updates will cause hell later on when you’re dicking around in the database.  The update process also fixes common screwups and will make everything a bit less screwy when you’re trying to port the database between DBMS’s.

I’m not going to go into detail on how to do this, as it varies between versions and you’ll likely be reading this when a bunch of the instructions are way the hell out of date.  Just get GitLab up to the latest release, and make sure that everything’s running smoothly before continuing.

Configuration

Next, you’re going to want to back up your database.yml and set up a new one using database.yml.mysql, after ensuring that the proper MySQL/MariaDB user and database exist.  Make sure that you do this as your gitlab user account:

# Assuming git is your gitlab account:
cd /home/git/gitlab
mv config/database.yml config/database.yml.bak
sudo -u git cp config/database.yml.mysql config/database.yml
sudo -u git nano -w config/database.yml
# Edit to taste

Now comes the tricky part.

Database Dickery

At this point, you need to make certain that GitLab is completely shut down. If it isn’t, do so and perform another backup, just to be sure everything is flushed to the database.

Now, it’s time to hold your nose and dive into the sewer.

# This should be your server's maintenance account, the one that you SSH into, NOT git or gitlab.
# If you're crazy, you can also do this as root.

# Clone pg2mysql, even though it's horribly out of date.
cd ~
git clone https://github.com/ChrisLundquist/pg2mysql.git

# Download a simple script that fixes mistakes that pg2mysql.php makes.
wget -O pg2mysql/fixSQL.py https://gist.github.com/N3X15/0df8aa159f1bebac9afd/raw/fcd975ff6e3294d37d625718a2eee57ae12455da/fixSQL.py

# And a script that runs this whole mess for us.
wget -O migrateGitlab.sh https://gist.github.com/N3X15/0df8aa159f1bebac9afd/raw/922549dc989b19ba3102cdee4b01dd151607ff65/migrateGitlab.sh

You should now eyeball these two messy, curse-laden scripts to be sure that they do what you want, and edit as necessary.

Now to start the show:

./migrateGitlab.sh

This will produce gitlab.sql in your home directory. Inspect it for screwups, and fix them, if needed. In my case, it didn’t need any edits.

Now, to import the database into the database “gitlab”, using the “gitlab” user’s credentials:

mysql -u gitlab -p gitlab < gitlab.sql
# [type in the password and mash enter]

If all went well, mysql will exit without any errors. If it didn't, fix the SQL file and run it again until it does.

Final Fuckery

At the time of writing, this process works well, but produces one final bug from GitLab:  You try to log in, succeed, but now it wants you to change your password and won't stop asking, even if you do change your password.

Well, my friend, you have one final task to perform, as GitLab expired everyone's password. (Replace 2016 below with current year + 1)

cd /home/git/gitlab
sudo -u git -H RAILS_ENV="production" bundle exec rails console production

At this point, you'll be in the Ruby on Rails console.

# Rails console opens
User.where("password_expires_at < '2016-01-01 00:00:00'" ).each { |u| u.password_expires_at = Time.now.midnight + 1.year; u.save! }
# You'll get a bunch of database records spammed in the console, this is normal.
# Close rails console
exit

Now, start up your server and enjoy.