convert database from Mysql to Postgresql

Readme.md

How to migrate the data from MySQL (MariaDB) to Postglesql

First of all you should copy MySQL.sql.gz file to the app/tmp directory.

Next execute docker-compose up and log into the application container using the command docker-compose run app bash.

All the following commands you should run inside the container:

1. Restore MySQL dump to MySQL database:

$ gunzip < /app/tmp/MySQL.sql.gz | mysql -u root -h mysql pals

2. Run the data migration:

$ pgloader mysql://root@mysql/pals postgresql://postgres@postgres/pals

3. Copy pgloader.log file to the app/tmp directory for further analysis:

$ cp /tmp/pgloader/pgloader.log /app/tmp/

4. Create Postgresql dump:

$ pg_dump -h postgres -U postgres -w pals | gzip -c > /app/tmp/postgresql.sql.gz

connect to mysql

$ mysql -u root -h mysql

Whow to convert from latin1 to utf-8

DB="icc_ffs_development"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql -u root -h mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql -u root -h mysql "$DB"

docker-compose.yml

version: '3'

services:
  mysql:
    image: mariadb:5
    container_name: pals_mariadb
    environment:
      MYSQL_DATABASE: pals
      MYSQL_USER: pals
      MYSQL_PASSWORD: password
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
    restart: always
    volumes:
      - ./tmp/mysql-data:/var/lib/mysql:rw

  postgres:
    image: postgres:10.1-alpine
    container_name: pals_postgres
    environment:
      POSTGRES_DB: pals
    restart: always
    volumes:
      - ./tmp/postgresql-data:/var/lib/postgresql/data

  app:
    container_name: pals_app
    image: pals_app
    build: .
    command:
      - /bin/bash
      - -c
      - |
          tail -f /dev/null
    volumes:
      - .:/app
    ports:
      - 3000:3000
    depends_on:
      - mysql
      - postgres
    links:
      - mysql
      - postgres

Dockerfile

FROM ubuntu:latest

MAINTAINER Evhenii Halenko 'Re4port@ukr.net'

RUN apt update -y \
    && apt install -y build-essential \
      netcat \
      curl \
      wget \
      tzdata \
      pgloader \
      mysql-client \
      libxslt-dev \
      libxml2-dev \
      imagemagick \
      libmagickcore-dev \
      libmagickwand-dev \
    && echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list \
    && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - \
    && apt update -y \
    && apt install -y postgresql

WORKDIR /app