mysql_to_postgresql.md 10.2 KB
Newer Older
Lin Jen-Shin's avatar
Lin Jen-Shin committed
1
---
2
last_updated: 2019-06-18
Lin Jen-Shin's avatar
Lin Jen-Shin committed
3
---
4

Lin Jen-Shin's avatar
Lin Jen-Shin committed
5
# Migrating from MySQL to PostgreSQL
6

7 8
This guide documents how to take a working GitLab instance that uses MySQL and
migrate it to a PostgreSQL database.
dosire's avatar
dosire committed
9

10
## Requirements
11

12 13 14 15
NOTE: **Note:**
Support for MySQL was removed in GitLab 12.1. This procedure should be performed
**before** installing GitLab 12.1.

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

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

21
1. Search for the version:
22

23
   ```shell
24 25
   apt-cache madison pgloader
   ```
26

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

29
   ```shell
30 31 32 33 34
   sudo apt-get install pgloader
   ```

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

35
   ```shell
36 37 38 39 40 41 42 43 44 45 46 47
   # 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
   ```

Katrin Leinweber's avatar
Katrin Leinweber committed
48
For other distributions, follow the instructions in PostgreSQL's
49 50 51
[download page](https://www.postgresql.org/download/) to add their repository
and then install `pgloader`.

Ian Baum's avatar
Ian Baum committed
52 53 54 55 56
If you are migrating to a Docker based installation, you will need to 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:

57
   ```shell
Ian Baum's avatar
Ian Baum committed
58 59 60 61
   docker exec -it gitlab bash
   ```

1. Install pgloader:
62

63
   ```shell
Ian Baum's avatar
Ian Baum committed
64 65 66 67
   apt-get update
   apt-get -y install pgloader
   ```

68 69 70 71
## Omnibus GitLab installations

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

Lin Jen-Shin's avatar
Lin Jen-Shin committed
73
1. Stop GitLab:
74

75
   ```shell
76 77
   sudo gitlab-ctl stop
   ```
78

Lin Jen-Shin's avatar
Lin Jen-Shin committed
79
1. Edit `/etc/gitlab/gitlab.rb` to enable bundled PostgreSQL:
80

81
   ```ruby
82 83
   postgresql['enable'] = true
   ```
84

Lin Jen-Shin's avatar
Lin Jen-Shin committed
85 86 87 88
1. Edit `/etc/gitlab/gitlab.rb` to use the bundled PostgreSQL. Please check
   all the settings beginning with `db_`, such as `gitlab_rails['db_adapter']`
   and alike. You could just comment all of them out so that we'll just use
   the defaults.
89

90 91
1. [Reconfigure GitLab](../administration/restart_gitlab.md#omnibus-gitlab-reconfigure)
   for the changes to take effect.
Stan Hu's avatar
Stan Hu committed
92
1. Start Unicorn and PostgreSQL so that we can prepare the schema:
Lin Jen-Shin's avatar
Lin Jen-Shin committed
93

94
   ```shell
95 96 97
   sudo gitlab-ctl start unicorn
   sudo gitlab-ctl start postgresql
   ```
Lin Jen-Shin's avatar
Lin Jen-Shin committed
98 99 100

1. Run the following commands to prepare the schema:

101
   ```shell
102 103
   sudo gitlab-rake db:create db:migrate
   ```
Lin Jen-Shin's avatar
Lin Jen-Shin committed
104

Stan Hu's avatar
Stan Hu committed
105
1. Stop Unicorn to prevent other database access from interfering with the loading of data:
106

107
   ```shell
108 109
   sudo gitlab-ctl stop unicorn
   ```
110

Lin Jen-Shin's avatar
Lin Jen-Shin committed
111
After these steps, you'll have a fresh PostgreSQL database with up-to-date schema.
112

113 114
Next, we'll use `pgloader` to migrate the data from the old MySQL database to the
new PostgreSQL one:
115

Lin Jen-Shin's avatar
Lin Jen-Shin committed
116
1. Save the following snippet in a `commands.load` file, and edit with your
117
   MySQL database `username`, `password` and `host`:
118

119
   ```sql
120 121 122
   LOAD DATABASE
        FROM mysql://username:password@host/gitlabhq_production
        INTO postgresql://gitlab-psql@unix://var/opt/gitlab/postgresql:/gitlabhq_production
123

124 125 126
   WITH include no drop, truncate, disable triggers, create no tables,
        create no indexes, preserve index names, no foreign keys,
        data only
127

128 129 130 131
   SET MySQL PARAMETERS
   net_read_timeout = '90',
   net_write_timeout = '180'

132
   ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'
133

134 135
   ;
   ```
136

Lin Jen-Shin's avatar
Lin Jen-Shin committed
137
1. Start the migration:
138

139
   ```shell
140 141
   sudo -u gitlab-psql pgloader commands.load
   ```
Lin Jen-Shin's avatar
Lin Jen-Shin committed
142

Stan Hu's avatar
Stan Hu committed
143
1. Once the migration finishes, you should see a summary table that looks like
144
   the following:
Stan Hu's avatar
Stan Hu committed
145

146
   ```plaintext
147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167
                                    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.
Stan Hu's avatar
Stan Hu committed
168 169

1. Start GitLab:
Lin Jen-Shin's avatar
Lin Jen-Shin committed
170

171
   ```shell
172 173
   sudo gitlab-ctl start
   ```
Lin Jen-Shin's avatar
Lin Jen-Shin committed
174

175
You can now verify that everything works as expected by visiting GitLab.
176

177
## Source installations
178

179 180
For installations from source that use MySQL, you'll first need to
[install PostgreSQL and create a database](../install/installation.md#6-database).
181

182
After the database is created, go on with the following steps:
183 184 185

1. Stop GitLab:

186
   ```shell
187 188
   sudo service gitlab stop
   ```
189 190 191

1. Switch database from MySQL to PostgreSQL

192
   ```shell
193 194 195 196 197 198
   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
   ```

199
1. Install Gems related to PostgreSQL
Miao Wang's avatar
Miao Wang committed
200

201
   ```shell
202 203 204
   sudo -u git -H rm .bundle/config
   sudo -u git -H bundle install --deployment --without development test mysql aws kerberos
   ```
205 206 207

1. Run the following commands to prepare the schema:

208
   ```shell
209 210
   sudo -u git -H bundle exec rake db:create db:migrate RAILS_ENV=production
   ```
211 212 213

After these steps, you'll have a fresh PostgreSQL database with up-to-date schema.

214 215
Next, we'll use `pgloader` to migrate the data from the old MySQL database to the
new PostgreSQL one:
216 217 218 219

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

220
   ```sql
221 222 223
   LOAD DATABASE
        FROM mysql://username:password@host/gitlabhq_production
        INTO postgresql://postgres@unix://var/run/postgresql:/gitlabhq_production
224

225 226 227
   WITH include no drop, truncate, disable triggers, create no tables,
        create no indexes, preserve index names, no foreign keys,
        data only
228

229 230
   SET MySQL PARAMETERS
   net_read_timeout = '90',
231
   net_write_timeout = '180'
232

233
   ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'
234

235 236
   ;
   ```
237 238 239

1. Start the migration:

240
   ```shell
241 242
   sudo -u postgres pgloader commands.load
   ```
243 244

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

247
   ```plaintext
248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268
                                    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.
269 270 271

1. Start GitLab:

272
   ```shell
273 274 275 276 277 278 279 280
   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.
281

282
### Database error permission denied
283

284 285
The PostgreSQL user that you use for the migration MUST have **superuser** privileges.
Otherwise, you may see a similar message to the following:
286

287
```plaintext
288 289 290 291 292 293 294 295 296
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
297 298 299

If you experience 500 errors after the migration, try to clear the cache:

300
```shell
301 302 303 304
# Omnibus GitLab
sudo gitlab-rake cache:clear

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