Migrating from MySQL to PostgreSQL
This guide documents how to take a working GitLab instance that uses MySQL and migrate it to a PostgreSQL database.
Requirements
pgLoader 3.4.1+ is required, confirm with pgloader -V.
You can install it directly from your distribution, for example in Debian/Ubuntu:
-
Search for the version:
apt-cache madison pgloader -
If the version is 3.4.1+, install it with:
sudo apt-get install pgloaderIf your distribution’s version is too old, use PostgreSQL’s repository:
# Add repository sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # Add key sudo apt-get install wget ca-certificates wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - # Install package sudo apt-get update sudo apt-get install pgloader
For other distributions, follow the instructions in PostgreSQL’s
download page to add their repository
and then install pgloader.
If you are migrating to a Docker based installation, you must install pgLoader within the container as it is not included in the container image.
-
Start a shell session in the context of the running container:
docker exec -it gitlab bash -
Install pgLoader:
apt-get update apt-get -y install pgloader
Omnibus GitLab installations
For Omnibus GitLab packages, you first need to enable the bundled PostgreSQL:
-
Stop GitLab:
sudo gitlab-ctl stop -
Edit
/etc/gitlab/gitlab.rbto enable bundled PostgreSQL:postgresql['enable'] = true -
Edit
/etc/gitlab/gitlab.rbto use the bundled PostgreSQL. Review all of the settings beginning withdb_(such asgitlab_rails['db_adapter']). To use the default values, you can comment all of them out. -
Reconfigure GitLab for the changes to take effect.
-
Start Puma and PostgreSQL so that we can prepare the schema:
sudo gitlab-ctl start puma sudo gitlab-ctl start postgresql -
Run the following commands to prepare the schema:
sudo gitlab-rake db:create db:migrate -
Stop Puma to prevent other database access from interfering with the loading of data:
sudo gitlab-ctl stop puma
After these steps, you have a fresh PostgreSQL database with up-to-date schema.
Next, use pgloader to migrate the data from the old MySQL database to the
new PostgreSQL one:
-
Save the following snippet in a
commands.loadfile, and edit with your MySQL databaseusername,passwordandhost:LOAD DATABASE FROM mysql://username:password@host/gitlabhq_production INTO postgresql://gitlab-psql@unix://var/opt/gitlab/postgresql:/gitlabhq_production WITH include no drop, truncate, disable triggers, create no tables, create no indexes, preserve index names, no foreign keys, data only SET MySQL PARAMETERS net_read_timeout = '90', net_write_timeout = '180' ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public' ; -
Start the migration:
sudo -u gitlab-psql pgloader commands.load -
After the migration finishes, you should see a summary table that looks like the following:
table name read imported errors total time ----------------------------------------------- --------- --------- --------- -------------- fetch meta data 119 119 0 0.388s Truncate 119 119 0 1.134s ----------------------------------------------- --------- --------- --------- -------------- public.abuse_reports 0 0 0 0.490s public.appearances 0 0 0 0.488s . . . public.web_hook_logs 0 0 0 1.080s ----------------------------------------------- --------- --------- --------- -------------- COPY Threads Completion 4 4 0 2.008s Reset Sequences 113 113 0 0.304s Install Comments 0 0 0 0.000s ----------------------------------------------- --------- --------- --------- -------------- Total import time 1894 1894 0 12.497sIf there is no output for more than 30 minutes, it’s possible
pgloaderencountered an error. See the troubleshooting guide for more details. -
Start GitLab:
sudo gitlab-ctl start
You can now verify that everything works as expected by visiting GitLab.
Source installations
For installations from source that use MySQL, you must first install PostgreSQL and create a database.
After the database is created, go on with the following steps:
-
Stop GitLab:
sudo service gitlab stop -
Switch database from MySQL to PostgreSQL
cd /home/git/gitlab sudo -u git mv config/database.yml config/database.yml.bak sudo -u git cp config/database.yml.postgresql config/database.yml sudo -u git -H chmod o-rwx config/database.yml -
Install Gems related to PostgreSQL
sudo -u git -H rm .bundle/config sudo -u git -H bundle install --deployment --without development test mysql aws kerberos -
Run the following commands to prepare the schema:
sudo -u git -H bundle exec rake db:create db:migrate RAILS_ENV=production
After these steps, you have a fresh PostgreSQL database with up-to-date schema.
Next, use pgloader to migrate the data from the old MySQL database to the
new PostgreSQL one:
-
Save the following snippet in a
commands.loadfile, and edit with your MySQLusername,passwordandhost:LOAD DATABASE FROM mysql://username:password@host/gitlabhq_production INTO postgresql://postgres@unix://var/run/postgresql:/gitlabhq_production WITH include no drop, truncate, disable triggers, create no tables, create no indexes, preserve index names, no foreign keys, data only SET MySQL PARAMETERS net_read_timeout = '90', net_write_timeout = '180' ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public' ; -
Start the migration:
sudo -u postgres pgloader commands.load -
After the migration finishes, you should see a summary table that looks like the following:
table name read imported errors total time ----------------------------------------------- --------- --------- --------- -------------- fetch meta data 119 119 0 0.388s Truncate 119 119 0 1.134s ----------------------------------------------- --------- --------- --------- -------------- public.abuse_reports 0 0 0 0.490s public.appearances 0 0 0 0.488s . . . public.web_hook_logs 0 0 0 1.080s ----------------------------------------------- --------- --------- --------- -------------- COPY Threads Completion 4 4 0 2.008s Reset Sequences 113 113 0 0.304s Install Comments 0 0 0 0.000s ----------------------------------------------- --------- --------- --------- -------------- Total import time 1894 1894 0 12.497sIf there is no output for more than 30 minutes, it’s possible
pgloaderencountered an error. See the troubleshooting guide for more details. -
Start GitLab:
sudo service gitlab start
You can now verify that everything works as expected by visiting GitLab.
Troubleshooting
Sometimes, you might encounter some errors during or after the migration.
Database error permission denied
The PostgreSQL user that you use for the migration MUST have superuser privileges. Otherwise, you may see a similar message to the following:
debugger invoked on a CL-POSTGRES-ERROR:INSUFFICIENT-PRIVILEGE in thread
#<THREAD "lparallel" RUNNING {10078A3513}>:
Database error 42501: permission denied: "RI_ConstraintTrigger_a_20937" is a system trigger
QUERY: ALTER TABLE ci_builds DISABLE TRIGGER ALL;
2017-08-23T00:36:56.782000Z ERROR Database error 42501: permission denied: "RI_ConstraintTrigger_c_20864" is a system trigger
QUERY: ALTER TABLE approver_groups DISABLE TRIGGER ALL;
Experiencing 500 errors after the migration
If you experience 500 errors after the migration, try to clear the cache:
# Omnibus GitLab
sudo gitlab-rake cache:clear
# Installations from source
sudo -u git -H bundle exec rake cache:clear RAILS_ENV=production