easy migration from mysql to postrgesql?

[devinrsmith]

Is there an easy way to migrate an existing setup from mysql to postgresql?

[sameersbn]

Gitlab has some info on the subject here https://gitlab.com/gitlab-org/gitlab-ce/blob/master/doc/update/mysql_to_postgresql.md

[sameersbn]

did you manage to migrate to postgresql?

[devinrsmith]

Gave up after a while, seems like a headache to do this within the docker container.

git@4e744eb0becc:~/gitlab$ mysqldump --compatible=postgresql --default-character-set=utf8 -r gitlabhq_production.mysql -u root gitlabhq_production -p
Enter password:
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

Probably doable if I spent more time banging against it until it worked. Will probably just stick with mysql :/

[sameersbn]

okay.. i will give it a shot and let you know the instructions if i am successfull.

[sameersbn]

Please try these instructions:

Step 1: Start the posqtgresql container for our GitLab instance

docker run -it --rm --name postgresql \
  -v /srv/docker/gitlab/posqtgresql \
  -e DB_USER=<POSTGRESQL_DB_USER> -e DB_PASS=<POSTGRESQL_DB_PASS> \
  -e DB_NAME=<POSTGRESQL_DB_NAME> \
  sameersbn/postgresql

Substitute <POSTGRESQL_DB_USER>, <POSTGRESQL_DB_PASS> and <POSTGRESQL_DB_NAME> in the above command to your choosing.

Step 2: Create another postgresql container for the migration

Now we start a new postgresql container for the migration, linking it with the existing mysql container and the postgresql container created in the previous step.

Assuming that the current mysql container is named mysql

docker run -it --rm --name postgresql-migrate \
  --link mysql:mysql --link postgresql:postgresql \
  sameersbn/postgresql bash

You will be dropped into the containers shell.

Step 3: Install the required packages:

apt-get update && apt-get install -y ed git python mysql-client

Step 4: Perform the migration

Replace the parameters in <> as per your database connection settings.

git clone https://github.com/gitlabhq/mysql-postgresql-converter.git -b gitlab
cd mysql-postgresql-converter
mysqldump --compatible=postgresql --default-character-set=utf8 -r gitlabhq_production.mysql -h mysql -u <MYSQL_DB_USER> <MYSQL_DB_NAME> -p
python db_converter.py gitlabhq_production.mysql gitlabhq_production.psql
psql -h postgresql -U <POSTGRESQL_DB_USER> -f gitlabhq_production.psql -d <POSTGRESQL_DB_NAME>

The above commands will dump the existing mysql database, and migrate it to the new postgresql container. Once done you can exit this container and you should have your gitlab database migrated to the postgresql instance.

Now all you need to do is update the docker run command for the gitlab instance, linking it with the postgresql container and update the database connection parameters as required.

reference: https://gitlab.com/gitlab-org/gitlab-ce/blob/master/doc/update/mysql_to_postgresql.md

[sameersbn]

Please let me know if this resolves the issue. Thanks.

[sameersbn]

did it work?

[sameersbn]

closing issue.

[devinrsmith]

I'll make sure to give this a whirl when the time opens up. Thanks for your quick reply!

[85siva]

Hi ,

We are trying to convert mysql to pgsql - the following error occurred - please help ASAP .

root@9bdac2ad37d5:/var/lib/postgresql/mysql-postgresql-converter# python db_converter.py gitlabhq_production.mysql gitlabhq_production.psql

Line 23803 (of 24970: 95.33%) [76 tables] [21058 inserts] [ETA: 0 min 54 sec]Traceback (most recent call last):

File "db_converter.py", line 256, in

parse(sys.argv[1], sys.argv[2])

File "db_converter.py", line 73, in parse

line = line.decode("utf8").strip().replace(r"\\", "WUBWUBREALSLASHWUB").replace(r"\0", "").replace(r"\'", "''").replace("WUBWUBREALSLASHWUB", r"\\")

File "/usr/lib/python2.7/encodings/utf_8.py", line 16, in decode

return codecs.utf_8_decode(input, errors, True)

UnicodeDecodeError: 'utf8' codec can't decode byte 0xa9 in position 56: invalid start byte

[billfig]

Hi, I'm having similar issue.

Line 1401 (of 4740: 29.56%) [33 tables] [185 inserts] [ETA: 0 min 13 sec]Traceback (most recent call last):
File "db_converter.py", line 264, in
parse(sys.argv[1], sys.argv[2])
File "db_converter.py", line 77, in parse
line = line.decode("utf8").strip().replace(r"\", "WUBWUBREALSLASHWUB").replace(r"\0", "").replace(r"'", "''").replace("WUBWUBREALSLASHWUB", r"\")

line 1401 of mysql dump is:
INSERT INTO "gpg_keys" VALUES
also happens on another line: INSERT INTO "gpg_signatures"

85siva , What is Line 23803 in your mysql dump?

[ye]

The official GitLab guide provides a easy guide to use pgloader to migrate the data from MySQL to PostgreSQL.

https://docs.gitlab.com/ce/update/mysql_to_postgresql.html

[lanixx]

Hi guys,

I would just like to briefly describe here my migration test steps. Right now I'm through with the tests and everything seems to be fine. That's just a report to consider. I've been using this setup for quite some time and am super satisfied.

Ok my steps. I have:

• created test environment with docker, docker-compose
• copied my data and prepared the environment: ssl keys, dns entrys, etc
• extended my docker-compose.yml and added postgresql container from example docker-compose.yml
• replaced mysql integration to postgresql integration
• started postgresql container
• started in gitlab container following rake task: sudo -u git -H bundle exec rake db:create db:migrate RAILS_ENV=production
  this will create main db structure and apply all migration tasks
• founded realy helpfull documentation about this migration tool - pgloader: https://pgloader.readthedocs.io/en/latest/ref/mysql.html#
• created a migration .load file

LOAD DATABASE
     FROM mysql://root:pass@mysql/gitlabhq_production
     INTO postgresql://postgres:pass@postgresql/gitlabhq_production

WITH include no drop, truncate, disable triggers, create no tables,
     create no indexes, preserve index names, no foreign keys,
     workers = 16, concurrency = 1,
     multiple readers per thread, rows per range = 100000,
     data only

  SET MySQL PARAMETERS
      net_read_timeout  = '86400',
      net_write_timeout = '86400'
  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '256MB',
      work_mem to '256MB'

 CAST type bigint when (= precision 20) to bigserial drop typemod,
      type date drop not null drop default using zero-dates-to-null,
      -- type tinyint to boolean using tinyint-to-boolean,
      type year to integer

ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'

; 

• found realy good description, what a need additionaly: https://github.com/dimitri/pgloader/wiki/Convert-from-MySQL-with-Docker-installation
  it is importand to give postgresql admin rights from your network.
• started with migration

docker run  --security-opt seccomp=unconfined -it --rm --name postgresql-migrate --link gitlab_mysql:mysql --link gitlab_postgresql:postgresql --network gitlab_backend -v $PWD/migration.load:/migration.load dimitri/pgloader:ccl.latest pgloader migration.load

(you need to replace container names and your path to your migration.load file)

• done it 2-3 times, because the first time you finish it with an error

this is just an example and not a finished manual. No guarantee that it will work 100%.

more information you can find here: https://gitlab.com/gitlab-org/gitlab-ee/issues/2799

I hope this information can help someone.

[jpledref]

I deal with "UnicodeDecodeError: 'utf8' codec can't decode byte 0xXY" error by using
"--hex-blob" when dumping from mysql and by replacing all hex_data with decode('<hex_data>','hex').

To Sum up:
git clone https://github.com/gitlabhq/mysql-postgresql-converter.git -b gitlab cd mysql-postgresql-converter mysqldump --compatible=postgresql --hex-blob --default-character-set=utf8 -r gitlabhq_production.mysql -h mysql -u <MYSQL_DB_USER> <MYSQL_DB_NAME> -p python db_converter.py gitlabhq_production.mysql gitlabhq_production.psql

Then:
Replace hexdata fields into gitlabhq_production.psql

To finish:
psql -h postgresql -U <POSTGRESQL_DB_USER> -f gitlabhq_production.psql -d <POSTGRESQL_DB_NAME> -v ON_ERROR_STOP=1