summaryrefslogtreecommitdiff
path: root/doc/update/mysql_to_postgresql.md
blob: 9a367d218f0d2e330070c958104987b7016ec428 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
---
stage: Enablement
group: Database
info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments
---

# 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

NOTE:
Support for MySQL was removed in GitLab 12.1. This procedure should be performed
**before** installing GitLab 12.1.

[pgLoader](https://pgloader.io/) 3.4.1+ is required, confirm with `pgloader -V`.

You can install it directly from your distribution, for example in
Debian/Ubuntu:

1. Search for the version:

   ```shell
   apt-cache madison pgloader
   ```

1. If the version is 3.4.1+, install it with:

   ```shell
   sudo apt-get install pgloader
   ```

   If your distribution's version is too old, use PostgreSQL's repository:

   ```shell
   # 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](https://www.postgresql.org/download/) 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.

1. Start a shell session in the context of the running container:

   ```shell
   docker exec -it gitlab bash
   ```

1. Install pgLoader:

   ```shell
   apt-get update
   apt-get -y install pgloader
   ```

## Omnibus GitLab installations

For [Omnibus GitLab packages](https://about.gitlab.com/install/), you first
need to enable the bundled PostgreSQL:

1. Stop GitLab:

   ```shell
   sudo gitlab-ctl stop
   ```

1. Edit `/etc/gitlab/gitlab.rb` to enable bundled PostgreSQL:

   ```ruby
   postgresql['enable'] = true
   ```

1. Edit `/etc/gitlab/gitlab.rb` to use the bundled PostgreSQL. Review all of the
   settings beginning with `db_` (such as `gitlab_rails['db_adapter']`). To use
   the default values, you can comment all of them out.

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:

   ```shell
   sudo gitlab-ctl start unicorn
   sudo gitlab-ctl start postgresql
   ```

1. Run the following commands to prepare the schema:

   ```shell
   sudo gitlab-rake db:create db:migrate
   ```

1. Stop Unicorn to prevent other database access from interfering with the loading of data:

   ```shell
   sudo gitlab-ctl stop unicorn
   ```

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:

1. Save the following snippet in a `commands.load` file, and edit with your
   MySQL database `username`, `password` and `host`:

   ```sql
   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'

   ;
   ```

1. Start the migration:

   ```shell
   sudo -u gitlab-psql pgloader commands.load
   ```

1. After the migration finishes, you should see a summary table that looks like
   the following:

   ```plaintext
                                    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:

   ```shell
   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](../install/installation.md#6-database).

After the database is created, go on with the following steps:

1. Stop GitLab:

   ```shell
   sudo service gitlab stop
   ```

1. Switch database from MySQL to PostgreSQL

   ```shell
   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

   ```shell
   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:

   ```shell
   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:

1. Save the following snippet in a `commands.load` file, and edit with your
   MySQL `username`, `password` and `host`:

   ```sql
   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'

   ;
   ```

1. Start the migration:

   ```shell
   sudo -u postgres pgloader commands.load
   ```

1. After the migration finishes, you should see a summary table that looks like
   the following:

   ```plaintext
                                    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:

   ```shell
   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:

```plaintext
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:

```shell
# Omnibus GitLab
sudo gitlab-rake cache:clear

# Installations from source
sudo -u git -H bundle exec rake cache:clear RAILS_ENV=production
```