Migration from MySQL to Postgres fails with `syntax error at or near ";"`

Current status

We run a gitlab 10.4.2 from sources with mysql. Our MySQL database runs its own container.

Our goal

We want to migrate from sources with mysql to docker with postgresql. Firstly, we want to migarte from MySQL to Postgres.

Our mysql-to-postgres migration process

  1. Create a new gitlab-container to get an uü2date PostgreSQL database
  2. Run the fresh PostgreSQL as its own container
  3. Check Postgres access from host to container
  4. Check MySQL access from host to container
  5. Run migration

Step 1: gitlab-container

We created a new gitlab 10.4.2 container as follows:

docker run \
    --rm \
    --hostname gitlab.example.com \
    --publish 10443:443 \
    --publish 10080:80 \
    --publish 10022:22 \
    --name gitlab \
    --volume /docker/gitlab/config:/etc/gitlab \
    --volume /docker/gitlab/logs:/var/log/gitlab \
    --volume /docker/gitlab/data:/var/opt/gitlab \
    gitlab/gitlab-ce:10.4.2-ce.0

After the container was started, we followed the migration steps from the docu within the container:

gitlab-ctl stop
gitlab-ctl reconfigure
gitlab-ctl start unicorn
gitlab-ctl start postgresql
gitlab-rake db:create db:migrate
gitlab-ctl stop unicorn

Like this, we created a fresh PostgreSQL database with up-to-date schema, which is located under /docker/gitlab/data/postgresql/data now.

Afterwards, we stopped the gitlab container.

Step 2: Run the PostgreSQL database in its own container

We want to start the freshly generated PostgreSQL database as its own container. Thus, we configured these two files manually as follows:

  1. /docker/gitlab/data/postgresql/data/pg_hba.conf:
	host  all  all 0.0.0.0/0 trust
  1. /docker/gitlab/data/postgresql/data/postgresql.conf:
	listen_addresses = '*'
	ssl = off
	unix_socket_directories = ''

With this modified configuration, we run the postgresql in its own container:

docker run \
  --name postgres \
  --rm \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=admin \
  -v /docker/gitlab/data/postgresql/data:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:9.6.5

Step 3: Check postgres access

Due to the modified configuration, we checked the login with pgAdmin from the host to the container:

  • Host: localhost
  • Port: 5432
  • DB: postgres
  • USer gitlab-psql
  • Passwort: NONE

This works as well. Great.

Step 4: Check mysql access

We checked, that we can access the mysql container from the host:

mysql -h 127.0.0.1 -P 3307 -u root -p<PASSWORD> gitlab_production

Checking it with:

SHOW TABLES;

This works as well. Great.

Step 5: Migrate from postgresql to pgloader

Now, since we can access both containers from the host, we started the migration process as described in the docs, by creating an adjusted commands.load:

LOAD DATABASE
     FROM mysql://root:<PASSWORD>@127.0.0.1:3307/gitlab_production
     INTO postgresql://gitlab-psql@localhost:5432/gitlabhq_production

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

ALTER SCHEMA 'gitlab_production' RENAME TO 'public'
;

Finally, we run the migration with pgloader commands.load . This returns the following error:

2018-02-09T21:38:45.709000Z ERROR Database error 42601: syntax error at or near ";"
QUERY: TRUNCATE ;
2018-02-09T21:38:45.709000Z FATAL Failed to create the schema, see above.
2018-02-09T21:38:45.910000Z LOG report summary reset
       table name       read   imported     errors      total time
-----------------  ---------  ---------  ---------  --------------
  fetch meta data          0          0          0          0.234s 
         Truncate          0          0          0          0.000s 
-----------------  ---------  ---------  ---------  --------------

[EDIT] 2017-02-22: Tiny typos fixed

Edited by Dennis Boldt