summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAchilleas Pipinellis <axil@gitlab.com>2018-12-28 08:43:50 +0100
committerAchilleas Pipinellis <axil@gitlab.com>2019-04-03 20:25:57 +0200
commit7fcd386a6c0255f388183e3f74c652f9f5bbd649 (patch)
treec6598a19e61ebcc0f0cfa29e2c77a9a33dfedbd9
parent14bbc9362af374b7387a475821db5738b9032eb0 (diff)
downloadgitlab-ce-7fcd386a6c0255f388183e3f74c652f9f5bbd649.tar.gz
Clean up the mysql to postgres guidedocs/refactor-pgloader
This change refactors the pgloader instructions to have less headings and be more straightforward.
-rw-r--r--doc/update/mysql_to_postgresql.md286
1 files changed, 139 insertions, 147 deletions
diff --git a/doc/update/mysql_to_postgresql.md b/doc/update/mysql_to_postgresql.md
index 350072186ee..b7f7d71689d 100644
--- a/doc/update/mysql_to_postgresql.md
+++ b/doc/update/mysql_to_postgresql.md
@@ -1,31 +1,58 @@
---
-last_updated: 2018-02-07
+last_updated: 2019-03-27
---
# Migrating from MySQL to PostgreSQL
-> **Note:** This guide assumes you have a working GitLab instance with
-> MySQL and want to migrate to bundled PostgreSQL database.
+This guide documents how to take a working GitLab instance that uses MySQL and
+migrate it to a PostgreSQL database.
-## Omnibus installation
+## Requirements
-### Prerequisites
+[pgloader](http://pgloader.io) 3.4.1+ is required.
-First, we'll need to enable the bundled PostgreSQL database with up-to-date
-schema. Next, we'll use [pgloader](http://pgloader.io) to migrate the data
-from the old MySQL database to the new PostgreSQL one.
+You can install it directly from your distribution, for example in
+Debian/Ubuntu:
-Here's what you'll need to have installed:
+1. Search for the version:
-- pgloader 3.4.1+
-- Omnibus GitLab
-- MySQL
+ ```bash
+ apt-cache madison pgloader
+ ```
-### Enable bundled PostgreSQL database
+1. If the version is 3.4.1+, install it with:
+
+ ```bash
+ sudo apt-get install pgloader
+ ```
+
+ If your distribution's version is too old, use PostgreSQL's repository:
+
+ ```bash
+ # 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 PostrgreSQL's
+[download page](https://www.postgresql.org/download/) to add their repository
+and then install `pgloader`.
+
+## Omnibus GitLab installations
+
+For [Omnibus GitLab packages](https://about.gitlab.com/install/), you'll first
+need to enable the bundled PostgreSQL:
1. Stop GitLab:
- ``` bash
+ ```bash
sudo gitlab-ctl stop
```
@@ -40,39 +67,34 @@ Here's what you'll need to have installed:
and alike. You could just comment all of them out so that we'll just use
the defaults.
-1. [Reconfigure GitLab] for the changes to take effect:
-
- ``` bash
- sudo gitlab-ctl reconfigure
- ```
-
+1. [Reconfigure GitLab](../administration/restart_gitlab.md#omnibus-gitlab-reconfigure)
+ for the changes to take effect.
1. Start Unicorn and PostgreSQL so that we can prepare the schema:
- ``` bash
+ ```bash
sudo gitlab-ctl start unicorn
sudo gitlab-ctl start postgresql
```
1. Run the following commands to prepare the schema:
- ``` bash
+ ```bash
sudo gitlab-rake db:create db:migrate
```
1. Stop Unicorn to prevent other database access from interfering with the loading of data:
- ``` bash
+ ```bash
sudo gitlab-ctl stop unicorn
```
After these steps, you'll have a fresh PostgreSQL database with up-to-date schema.
-### Migrate data from MySQL to PostgreSQL
-
-Now, you can use pgloader to migrate the data from MySQL to PostgreSQL:
+Next, we'll use `pgloader` to migrate the data from the old MySQL database to the
+new PostgreSQL one:
1. Save the following snippet in a `commands.load` file, and edit with your
- database `username`, `password` and `host`:
+ MySQL database `username`, `password` and `host`:
```
LOAD DATABASE
@@ -90,7 +112,7 @@ Now, you can use pgloader to migrate the data from MySQL to PostgreSQL:
1. Start the migration:
- ``` bash
+ ```bash
sudo -u gitlab-psql pgloader commands.load
```
@@ -117,170 +139,140 @@ Now, you can use pgloader to migrate the data from MySQL to PostgreSQL:
Total import time 1894 1894 0 12.497s
```
- If there is no output for more than 30 minutes, it's possible pgloader encountered an error. See
- the [troubleshooting guide](#Troubleshooting) for more details.
+ If there is no output for more than 30 minutes, it's possible `pgloader` encountered an error. See
+ the [troubleshooting guide](#troubleshooting) for more details.
1. Start GitLab:
- ``` bash
+ ```bash
sudo gitlab-ctl start
```
-Now, you can verify that everything worked by visiting GitLab.
-
-### Troubleshooting
-
-#### Permissions
-
-Note that the PostgreSQL user that you use for the above 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:
-
-``` bash
-sudo gitlab-rake cache:clear
-```
-
-[reconfigure GitLab]: ../administration/restart_gitlab.md#omnibus-gitlab-reconfigure
-
-## Source installation
+You can now verify that everything works as expected by visiting GitLab.
-### Prerequisites
+## Source installations
-#### Install PostgreSQL and create database
+For installations from source that use MySQL, you'll first need to
+[install PostgreSQL and create a database](../install/installation.md#6-database).
-See [installation guide](../install/installation.md#6-database).
-
-#### Install [pgloader](http://pgloader.io) 3.4.1+
-
-Install directly from your distro:
-``` bash
-sudo apt-get install pgloader
-```
-
-If this version is too old, use PostgreSQL's repository:
-``` bash
-# 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
-```
-
-### Enable bundled PostgreSQL database
+After the database is created, go on with the following steps:
1. Stop GitLab:
- ``` bash
- sudo service gitlab stop
- ```
+ ```bash
+ sudo service gitlab stop
+ ```
1. Switch database from MySQL to PostgreSQL
- ``` bash
- 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
- ```
+ ```bash
+ 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
+ ```
+
1. Install Gems related to Postgresql
- ``` bash
- sudo -u git -H rm .bundle/config
- sudo -u git -H bundle install --deployment --without development test mysql aws kerberos
- ```
+ ```bash
+ sudo -u git -H rm .bundle/config
+ sudo -u git -H bundle install --deployment --without development test mysql aws kerberos
+ ```
1. Run the following commands to prepare the schema:
- ``` bash
- sudo -u git -H bundle exec rake db:create db:migrate RAILS_ENV=production
- ```
+ ```bash
+ sudo -u git -H bundle exec rake db:create db:migrate RAILS_ENV=production
+ ```
After these steps, you'll have a fresh PostgreSQL database with up-to-date schema.
-### Migrate data from MySQL to PostgreSQL
-
-Now, you can use pgloader to migrate the data from MySQL to PostgreSQL:
+Next, we'll use `pgloader` to migrate the data from the old MySQL database to the
+new PostgreSQL one:
1. Save the following snippet in a `commands.load` file, and edit with your
MySQL `username`, `password` and `host`:
- ```
- LOAD DATABASE
- FROM mysql://username:password@host/gitlabhq_production
- INTO postgresql://postgres@unix://var/run/postgresql:/gitlabhq_production
+ ```
+ 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
+ WITH include no drop, truncate, disable triggers, create no tables,
+ create no indexes, preserve index names, no foreign keys,
+ data only
- ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'
+ ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'
- ;
- ```
+ ;
+ ```
1. Start the migration:
- ``` bash
- sudo -u postgres pgloader commands.load
- ```
+ ```bash
+ sudo -u postgres pgloader commands.load
+ ```
1. Once 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.497s
- ```
-
- If there is no output for more than 30 minutes, it's possible pgloader encountered an error. See
- the [troubleshooting guide](#Troubleshooting) for more details.
+ ```
+ 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.497s
+ ```
+
+ If there is no output for more than 30 minutes, it's possible `pgloader` encountered an error. See
+ the [troubleshooting guide](#troubleshooting) for more details.
1. Start GitLab:
- ``` bash
- sudo service gitlab start
- ```
+ ```bash
+ 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.
-Now, you can verify that everything worked by visiting GitLab.
+### Database error permission denied
-### Troubleshooting
+The PostgreSQL user that you use for the migration MUST have **superuser** privileges.
+Otherwise, you may see a similar message to the following:
-#### Experiencing 500 errors after the migration
+```
+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:
-``` bash
+```bash
+# Omnibus GitLab
+sudo gitlab-rake cache:clear
+
+# Installations from source
sudo -u git -H bundle exec rake cache:clear RAILS_ENV=production
```