PostgreSQL Schema를 MySQL로 마이그레이션 시켜보자.

Note : 이 글은 지극히 주관적인 생각을 토대로 작성된 글입니다. 혹시나 잘못된 부분이 있다면 메일 또는 코멘트를 통해 알려주시면 감사하겠습니다. 😄 제 메일은 About 탭에서 확인하실 수 있습니다. 📧

시작하며

오랜만에 포스팅을 작성하게 되었다. 이번에 작성해볼 포스팅은 PostgreSQL DB의 스키마를 MySQL DB 스키마로 이전하는 방법에 대한 내용을 간단하게 작성해보고자 한다. 최근에 사용하던 RDBMS 제품을 MySQL로 변경하게 되면서 기존 스키마를 이전하는 방법을 찾아보게 되었고, 이를 포스팅으로 남겨두면 좋겠다는 생각이 들어서 이렇게 작성하게 되었다. 긴 말 필요없이 바로 본론으로 들어가보자.

PostgreSQL dump 파일을 만들어보자.

• 먼저 기존에 사용하던 PostgreSQL 스키마 파일에 대한 dump 파일을 만들어야 한다.

• pg_dump를 사용해서 만들 수 있다.

• 로컬에서 PostgreSQL이 설치되어 있어야 하므로, 설치되어 있지 않다면 PostgreSQL을 설치한다.

    $ brew install postgresql
  

• pg_dump를 이용해서 postgreSQL의 현재 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을 이용해서 pg dump 파일을 MySQL 형식으로 변환하자.

• pg_dump를 이용해서 PostgreSQL 스키마의 백업 본을 생성했다면, 그 다음은 이 백업본을 MySQL이 인식할 수 있는 형식으로 변환 시켜야 한다.

• 이 작업을 pg2mysql이라는 스크립트를 이용해서 진행하고자 한다.

• 해당 Github 저장소에 있는 pg2mysql 스크립트를 사용한다.

  https://github.com/ChrisLundquist/pg2mysql

• 위 스크립트를 보면 알겠지만, 해당 스크립트는 php로 작성되어 있다.

• 위 저장소를 clone하고, clone 한 위치에 위에서 백업한 postgreSQL dump 파일을 위치시키면 된다.

• 위 스크립트를 실행 시키기 위해서는 php가 필요하다. php가 설치되어 있지 않다면, php를 설치하자.

    $ brew install php
  

• php가 설치되었다면, 다음 명령어를 이용해서 postgreSQL dump 파일을 MySQL 형식으로 변환하자.

    $ php pg2mysql_cli.php publicBackup.sql mysqlfile.sql innodb
  

  • 첫 번째 플래그인 pg2mysql_cli.php가 우리가 실행시킬 스크립트다.

  • 두 번째 플래그인 publicBackup.sql가 방금전에 pg_dump를 이용해서 백업한 postgreSQL dump 파일이다.

  • 세 번째 플래그인 mysqlfile은 변환되어 생성될 MySQL dump 파일의 이름이다. 원하는 이름으로 지정하면 된다.

  • 네 번째 플래그인 innodb는 MySQL로 Migration 시에 MySQL 내 생성되는 테이블에서 사용할 테이블 엔진을 나타낸다. innodb를 사용하도록 하자.

• 위 명령어가 정상적으로 수행되었다면, mysqlfile.sql이 정상적으로 생성되었을 것이다.

mysqlfile.sql 의 내용 일부를 수정하자.

• 이제 변환된 sql 파일을 그대로 사용해서 MySQL에 기존 스키마를 복원하면 될 것 같지만, 그대로 적용해본 결과 제대로 적용되지 않는 부분에 대한 문제가 있었다.

• 그래서 아래 내용에 대해서 수정 작업을 하고 난 이후에 적용을 해야 원하는대로 적용이 되었다. 아래 내용을 살펴보며 적용이 필요한 부분은 적용하도록 하자.

• 위 pg2mysql 스크립트를 그대로 실행해서 파일을 변환하면, MySQL 테이블 생성 및 데이터 삽입 쿼리에 public 키워드가 붙어있는데, 이 키워드를 일괄 삭제해줘야 한다. 이게 첫 번째 수정 작업이다.

• 일단 위의 첫 번째 사항만 적용하면 스키마를 MySQL 데이터베이스에 적용하는 부분에선 에러가 나지 않는다. 하지만 적용하고 난 이후에 누락된 부분을 발견할 수 있다. 아래 내용을 조금 더 살펴보자.

• 몇 가지 누락 사항이 있어서 sql 파일을 수정함으로써 해결할 수 있다.

  • 첫 번째로 테이블 생성되는 쿼리에서 날짜 데이터 형식이 timestamp로 지정되어 있는데, 이를 datetime으로 변경해줘야 한다.

    • 예를 들어 created_at, updated_at 와 같은 컬럼들이 있다.

    • 이를 변경해주지 않으면, 날짜 데이터 삽입이 제대로 적용되지 않았다. 변경해주었더니 기존 스키마에 있던 데이터가 제대로 적용이 되었다.

  • 그리고 날짜 데이터를 표현하는 경우 default 값이 지정된 경우가 있는데, NOT NULL 제약이 없는 컬럼(ex. updated_at)의 경우는 해당 default 값을 지워주면 되고, NOT NULL 제약이 있는 컬럼(ex. created_at)의 경우에는 default 값이 0으로 지정되어 있는 것을 CURRENT_TIMESTAMP로 변경해주면 된다.

  • sql 파일 맨 하단에 테이블 별로 PK를 지정해주는 쿼리가 있는데, 해당 쿼리 하단에 AUTO_INCREMENT 제약도 걸어주는 쿼리를 추가해줘야 한다.

    • 이 부분이 누락되면, 이 데이터베이스를 사용하는 스프링 부트 애플리케이션에서 데이터 삽입 시에 에러를 내뱉는다.

      • 원인은 AUTO_INCREMENT 설정이 되어 있지 않아서다.

    • 또 누락된 게 있는데, PK를 sql 파일 제일 하단에서 지정하다 보니, FK를 지정되는 부분이 빠져있었다. 이 부분도 sql 파일을 수정해서 PK 지정 이후에 FK를 지정해두었던 테이블에 FK 설정을 해주어야 한다.

  • 테이블 생성 관련, 제약 설정 관련 수정이 끝난다면 이제 기존 row를 insert하는 쪽을 수정해주어야 한다.

  • 날짜 데이터를 삽입하는 insert 부분에 날짜 데이터 맨 뒤에 +00이 붙어있을 텐데, 이를 제거해줘야 한다.

  • 이를 제거해주지 않을 경우, MySQL에서 날짜 데이터로 인식을 못해서 실제로 dump가 끝난 뒤에 데이터를 살펴보면 모두 0000-00-00 00:00:00으로 저장된 것을 볼 수 있다.

    • 물론 이건 필자만 겪은 케이스일 수도 있다. 날짜 데이터에 zone 관련 내용이 적용되어 있다면 기존 데이터가 저런 형식으로 변환될 수 있기에 위의 내용에 해당된다면 위와 같이 수정하니, 제대로 삽입이 되었던 내용을 공유한다.

    • 해당 부분은 날짜 컬럼이 포함된 모든 row에 해당되는 경우기 때문에 사용하는 에디터의 일괄 수정 같은 기능을 사용해서 수정하면 번거로움을 줄일 수 있다. +00을 empty string으로 바꾸는 등의 작업을 진행하면 된다.

    • 위 작업에서 일괄 수정을 끝내게 되면 아마 sql 파일 제일 상단 쪽에 time_zone을 지정하는 쪽도 수정되었을 텐데, 해당 부분은 원복을 해두어야 한다. (SET time_zone="+00:00";)

      • 이 부분도 필자만 겪은 케이스일 수 있다. 필자의 PostgreSQL 스키마의 경우 타임존이 따로 설정되어 있었다. 이부분도 기호에 맞게 적용하면 된다.

    • 해당 명령어 안에도 +00이 붙어서 그 부분이 날아가서 문제가 생길 수 있다. 이 부분만 수정해주면 된다.

• 위 작업이 끝나면 mysqlfile.sql 에서 수정해야 할 사항이 끝난다.

MySQL 데이터베이스에 dump 파일로 복원하자.

• 이제 위에서 수정된 mysqlfile.sql을 이용해서 MySQL 데이터베이스에 복원을 하도록 하자.

• 일단 마찬가지겠지만, MySQL 클라이언트가 필요하다. MySQL이 설치되어 있지 않다면 설치를 하자.

    $ brew install MySQL
  

• 이 문서에서는 로컬 DB에 복원하는 내용을 다루겠지만, 전체적인 방식은 동일하다.

• 일단 MySQL 인스턴스 내에 dump 파일을 적용할 데이터베이스를 생성해야 한다.

    $ mysql -u developer -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 45
    Server version: 8.0.29 Homebrew
      
    Copyright (c) 2000, 2022, Oracle and/or its affiliates.
      
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
      
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
      
    mysql> CREATE DATABASE test_db default CHARACTER SET UTF8;
    Query OK, 1 row affected, 1 warning (0.00 sec)
      
    mysql> exit
    Bye
  

  • mysql -u {username} -p 명령어를 통해 MySQL 인스턴스 내에 접속을 한다.

  • password를 정상적으로 입력했다면 정상적인 접속이 되었을 것이고, 다음 명령문 입력을 통해 DataBase를 생성할 수 있다.

    • CREATE DATABASE {database_name} default CHARACTER SET UTF8;

• MySQL 인스턴스 내에 Database 까지 생성하였다면 이제 아까 변환하고 수정까지 마친 mysqlfile.sql을 이용해서 데이터베이스 내에 테이블 및 데이터를 생성하면 된다.

    ➜ mysql -u developer -p test_db < mysqlfile.sql
    Enter password:
  

• 위 명령어를 입력하고 난 이후, password를 정상적으로 입력했다면 일정 시간이 흐르고 난 후 복원이 마무리 될 것이다.

• 그 이후에 데이터베이스 내부를 살펴보면 테이블 및 데이터가 정상적으로 들어간 것을 확인할 수 있다.

• 위의 경우는 로컬 DB에 복원을 해서 저렇게 입력한 것이고 외부에 있는 DB 인스턴스에 시도를 할 경우에는 명령어 입력 시에 -h와 -p 옵션을 추가해서 작성하면 된다.

  • -h는 host 정보를 나타내고, -p는 당연하겠지만 포트 정보를 나타낸다고 생각하면 된다.

마치며

이번 포스팅에선 PostgreSQL에서 MySQL로 스키마 이전에 대한 내용을 작성해보았다. 최근에 포스팅을 작성하는 일이 뜸했는데, 오랜만에 포스팅을 작성해서 공유하게 되어서 좋았다. 다음에도 공유할만한 내용이나 작성해보고 싶은 내용이 생긴다면 포스팅으로 작성해서 공유해보도록 하겠다. 이번 포스팅은 여기서 마무리 하도록 하겠다.

마지막으로 이 글을 읽어주신 모든 분에게 감사드립니다.

Reference

• PostgreSQL Documentation

• MySQL Documentation

• pg2mysql Github Repository