[DataBase] RDBMS 이관 PostgreSQL to MySQL, 여러가지 방법과 에러 정리

 

이번 포스트에는 회사업무중에 필요에의해 기존에 있던 PostgreSQL DB를 MySQL로 이관시켜야 하는 요구사항이 있었습니다.

보통 PostgreSQL이 더 관대한 RDBMS로써 이기종 DBMS간 이관을 할 때, 찾아보니 MySQL → PostgreSQL은 많이 사례가 있고 자료가 있었지만, 우리의 경우 반대의 경우 였기 때문에 많은 리서칭이 필요했습니다.

 

이관 대상

대상 구분	대상 여부
스키마(Schemas)	Y(MySQL에서는 Databases)
테이블(Tables)	Y
제약사항(Constraints)	Y
Index	Y
Sequence	Y(Auto Increments)
User / Role	-

---

이관 방법

1. PostgreSQL dump file

https://www.postgresql.org/docs/current/app-pgdump.html

pg_dump

• $ pg_dump -U {username} -h {host} -p {port} {db_schema_name} -n public --format=p > publicBackup.sql
  • U : 해당 데이터베이스에 접속할 유저의 이름을 입력.
  • h : 어떤 DB 인스턴스에서 작업할 지에 대한 host 정보 입력
  • p : 해당 인스턴스의 port 정보 입력
  • 그 다음에 입력할 내용은 DB 인스턴스 내, Database 정보(이름) 입력.
  • n : 해당 플래그를 넣고 입력한 스키마만 백업을 만들도록 설정.
    • 위의 경우, public 스키마만 가져오도록 설정.
  • -format : 백업 포맷 설정.
    • 위의 경우는 p(plain) 이고, 평문으로 설정하는 기본 옵션
• 위 방식대로 명령어를 수행하게 되면, password를 입력하라는 내용이 한번 더 나오고, U 에 입력한 유저네임에 맞는 패스워드를 입력할 경우, dump가 정상적으로 수행
• dump가 정상적으로 수행되었다면, 해당 명령어를 수행한 위치에 publicBackup.sql이라는 dump된 파일이 생성
• pg2mysql 이슈사항
  • 테이블 정의 중 Constraint 부분을 의도적으로 생략하는 이슈
  • Unique key가 일반인덱스로 생성 되는 이슈
  • engine 변환 구문 오류
  • 메모리 제한 이슈
  • 이관했던 서비스가 테이블, 컬럼을 모두 대문자로 사용하여 생성구문을 모두 대문자로 생성하도록 strtoupper 함수를 추가했음 소문자로 사용하는 경우 strtoupper 함수부분을 제거해야함
  • cidr, inet 컬럼 미지원
  • FK option

---

2. DM Toolkit(DBsoft)-무료버전

Migrating data from PostgreSQL to MySQL | DBSofts

Migrating data from PostgreSQL to MySQL | DBSofts

 

• Trial version으로 스키마를 이전 시킬 때, PK, FK, Data type, Index, Sequence(Auto increments)로 전부 이전 됨

무료버전이라 생기는 trial컬럼(테이블마다 뒤의 일정하지 않은 3자리 이상의 숫자가 부여되어 생성됨)

테이블의 코맨트에 TRIAL이라고 자동으로 달림

컬럼의 코맨트에 자동으로 TRIAL로 달림

 

Sequence →Auto Increment

---

3. Datagrip (Table copy)

Datagrip 내에서 copy table기능을 자체적으로 지원함

 

장점: 테이블 내 모든 data를 한번에 import되는 기능이 있음

단점: 하지만 varchar가 tinytext로 변환된다거나, index, PK 지정, comment등은 수기로 입력해야 한다.(일일히 매핑, 벌크 변환이 불가능하고 table하나하나 copy해야 됨)

---

4. DDL 변환 사이트

SQLines - Online SQL Conversion - SQL Scripts, DDL, Queries, Views, Stored Procedures, Triggers

SQLines - Online SQL Conversion - SQL Scripts, DDL, Queries, Views, Stored Procedures, Triggers

해당 테이블에 대한 DDL을 복사하여 mysql용 DDL로 변환하여 스키마 생성

 

장점: data type, PK, index, comment등 전부 이전 됨

단점: 몇몇 mysql version에 따른 문법을 수동으로 작업해야 됨(index 생성 같은)

---

최종 선택 방안

가장 손이 덜가고 스키마를 사용자가 정의할 필요없다고 생각이되는 DM Toolkit을 사용했었습니다.

 

1. 이관 순서

1. DM ToolKit을 사용하여 local MySQL로 스키마 이관
2. DM Toolkit으로 부터 생성된 TRIAL컬럼 및 TRIAL comment 원래의 COMMENT로 대체
3. 각 테이블로 부터 DDL을 추출하여 형상관리
4. local MySQL테이블을 전부 지워보고 DDL로 다시 생성 테스트
5. 데이터 로드

---

에러 내용

1. 데이터 타입

대부분의 에러는 데이터타입 호환성에 대해서 나온 것 같습니다.

postgreSQL에서는 JSON, JSONB였던 데이터 타입이 MySQL에서는 JSON으로 변환이 되는데, TEXT로 변환해야 데이터 이관이 되는 경우가 있었습니다.

 

2. 제약 사항

또한 RDBMS의 특징중 하나인 PK와 FK 제약 사항 때문에 테이블의 생성 순서 및 데이터 삽입, 데이터 삭제에 대한 순서가 중요했습니다.

우리 같은 경우는 테이블 생성은 DDL로, 데이터 삽입과 삭제는 스크립트를 만들어 실행시켰는데, DDL에 대한 순서를 제약사항에 맞게 정렬시켜서 형상관리를 하였고, 데이터 삽입순서에 맞게 데이터 삽입을 한 후에 이관대상이 되는 테이블의 역순으로 만들어 데이터 삭제를 하는 스크립트를 만들어 실행하였습니다.