PostgreSQL to MySQL Migration

MySQL을 PostgreSQL 로 이관하는 방법은 PostgreSQL에서 제공하는 기능인 pgloader 를 사용하면 오브젝트부터 데이터까지 쉽게 이관할 수 있지만
반대인 PostgreSQL -> MySQL 은 적당한 툴도 없고, 두 DB의 schema 개념도 달라 사전에 알아둬야 할 점이 많습니다.
이번에 이관했던 서비스를 예시로 PostgreSQL => MySQL 이관 방법을 정리해보았습니다.

PostgreSQL 과 MySQL schema 차이

PostgreSQL에서는 테이블의 집합이 schema ( 위 그림에서 public, myschema )
schema 의 집합이 database ( 위 그림에서 postgres , mydb )
테이블의 집합 = schema 가 곧 논리 database 인 MySQL과는 개념이 다름
단 아래와 같이 DB 내에 사용하는 schema 가 단 하나일 땐 PostgreSQL database -> MySQL database 1:1 매핑이 가능함

amon=# \dn
          List of schemas
        Name        |    Owner
--------------------+--------------
 information_schema | testuser
 pg_catalog         | testuser
 pg_toast           | testuser
 pg_toast_temp_1    | testuser
 public             | testuser
(5 rows)

=> public 을 제외한 나머지 schema는 시스템 관련 스키마, pulic은 모든 schema가 접근 가능한 default schema  

test=# select count(*) from information_schema.tables where table_schema='public';
 count
-------
   100
(1 row)

=> 해당 서비스에서는 다른 서비스용 schema 를 생성하지 않고 기본 public schema에 오브젝트를 생성하였음

이관대상 확인

database

postgres=# \l+
                                                                  List of databases
   Name    |    Owner     | Encoding | Collation  |   Ctype    |         Access privileges         |  Size   | Tablespace |        Description
-----------+--------------+----------+------------+------------+-----------------------------------+---------+------------+---------------------------
 test1      | testuser      | UTF8     | en_US.UTF8 | en_US.UTF8 |                                   | 13 MB   | pg_default |
 test2      | testuser      | UTF8     | en_US.UTF8 | en_US.UTF8 |                                   | 172 MB  | pg_default |
                                                               : 

 template0 | postres        | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/"testuser"                 | 5408 kB | pg_default |
                                                               : "testuser"=CTc/"testuser"
 template1 | postgres       | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/"testuser"                 | 5408 kB | pg_default | default template database
                                                               : "testuser"=CTc/"testuser"

이관 대상 PostgreSQL DB 서버 내에 여러 database 들이 존재하는 상황임
원래 개념대로라면 위의 database 하나하나가 MySQL의 DB instance 로 매핑되지만 아래와 같이 각 database 내에서 schema 를 public schema 하나만 사용하고 있어 PostgreSQL 과 MySQL 1:1 매핑이 가능함

schema

test1=# \dn
          List of schemas
        Name        |    Owner
--------------------+--------------
 information_schema | testuser
 pg_catalog         | testuser
 pg_toast           | testuser
 pg_toast_temp_1    | testuser
 public             | testuser
(5 rows)

test1=# select count(*) from information_schema.tables where table_schema='public';
 count
-------
    100
(1 row)

test1=# \dn
          List of schemas
        Name        |    Owner
--------------------+--------------
 information_schema | testuser
 pg_catalog         | testuser
 pg_toast           | testuser
 pg_toast_temp_1    | testuser
 public             | testuser
(5 rows)


test2=# select count(*) from information_schema.tables where table_schema='public';
 count
-------
    84
(1 row)

user

postgres=# select * from pg_catalog.pg_roles;
   rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid
--------------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+-----------+-------
 testuser     | t        | t          | t             | t           | t            | t           |           -1 | ********    |               |           |    10
.
.

pg2mysql

PostgreSQL dump 파일을 MySQL 쿼리로 변환해주는 툴로 더이상 개발되지 않아 실사용 하기위해 몇가지 부분을 추가하였음

pg2mysql 이슈 및 수정사항

테이블 정의 중 Constraint 부분을 의도적으로 생략하는 이슈
- foreign , unique constraint 생성 구문 변환되도록 수정
Unique key가 일반인덱스로 생성 되는 이슈
- Unique key로 생성되도록 수정
engine 변환 구문 오류
- default로 InnoDB engine 으로 생성하도록 수정
메모리 제한 이슈
- pg2mysql 프로그램이 사용할 수 있는 최대 메모리 limit 을 512MB로 제한
이관했던 서비스가 테이블, 컬럼을 모두 대문자로 사용하여 생성구문을 모두 대문자로 생성하도록 strtoupper 함수를 추가했음 소문자로 사용하는 경우 strtoupper 함수부분을 제거해야함
cidr, inet 컬럼 미지원
- PostgreSQL에는 IP를 표현하는 cidr, inet 컬럼 타입이 있으나 MySQL에는 없음, 이부분은 MySQL에서 big int + inet_aton, inet_ation으로 구현할지 varchar 타입으로 전환할지 결정이 필요하여 수정하지 않았음. 참고로, varchar 로 전환시 order by 결과값이 inet 과는 다름
FK option
- PostgreSQL에는 DEFERRABLE INITIALLY DEFERRED ( 제약조건을 트랜잭션 종료할 때 검사하는 설정) 같은 MySQL에서는 지원하지 않는 기능이 있음. 경우에 따라 fk 조건을 삭제하거나 트랜잭션 시작시에 set foreign_key_checks = 0 ; 설정을 주도록 개발팀과 협의가 필요하기 때문에 변환과정에서 생략하지 않고 포함하도록함

사용방법

php 설치

$ php --version
PHP 5.3.3 (cli) (built: Mar 22 2017 12:27:09)

첨부파일의 pg2mysql tar 파일을 품

$ ls -ltr
total 44
-rw-r--r-- 1 irteam irteam 15145 May 26  2006 gpl.txt
-rw-r--r-- 1 irteam irteam  2370 Jul 21  2011 pg2mysql.php
-rw-r--r-- 1 irteam irteam  1534 Jul 21  2011 pg2mysql_cli.php
-rw-r--r-- 1 irteam irteam  2985 Jul 21  2011 README
-rw-rw-r-- 1 irteam irteam 13204 Aug 10 20:44 pg2mysql.inc.php.org
-rw-r--r-- 1 irteam irteam 13235 Aug 10 20:44 pg2mysql.inc.php

이관 대상 PostgreSQL dump 파일 준비

### schema only
pg_dump -p port -U username -S dbname  --format p -f dbname_schema.sql

### data only, insert 구문으로
pg_dump -p port -U username -a dbname --inserts --format p -f dbname_data.sql

### schema+ data
pg_dump -p port -U username  dbname --inserts --format p -f dbname_full.sql

MySQL 쿼리로 변환

php pg2mysql_cli.php dbname_full.sql mysql_dbname.sql

데이터 검증 (count 확인)

PostgreSQL

#!/bin/bash
database=database_name
user=postgres
echo "Database : $database"
PGCOMMAND=" psql -U $user -d $database -At -c \"
            SELECT   table_name
            FROM     information_schema.tables
            WHERE    table_type='BASE TABLE'
            AND      table_schema='public'
            \""

TABLENAMES=$(export PGPASSWORD=패스워드; eval "$PGCOMMAND")

for TABLENAME in $TABLENAMES; do
    PGCOMMAND=" psql -U $user -d $database -At -c \"  
                SELECT   '$TABLENAME',  
                         count(*)   
                FROM     $TABLENAME  
                \""
    eval "$PGCOMMAND"
done

MySQL

#!/bin/bash
database=database_name
for name in `mys $database -B -e "show tables"`;
do
   mys -B --vertical $database -e "select count(*) as $name from $name" | grep -v '^\*' | replace ": " "|"
done

index count

# MySQL
select table_name,count(distinct index_name) from information_schema.STATISTICS where index_schema='amon' group by 1 order by 1;

# PostgreSQL
select tablename,count(*) from pg_indexes where schemaname='public' group by 1 order by 1;

=> MySQL로 변환되면서 Auto-increment 컬럼이 PK 가 되기 때문에 MySQL 이 인덱스가 더 많을 수 있음

PostgreSQL 과 MySQL schema 차이#

이관대상 확인#

database#

schema#

user#

pg2mysql#

pg2mysql 이슈 및 수정사항#

사용방법#

데이터 검증 (count 확인)#