DBeaver를 사용하여 Mysql to Postgresql 데이터 이관

개요

스프링 부트로 프로젝트를 진행하면서 Mysql로 만든 데이터베이스를 Postgresql로 바꿔야 했다.

문제는 이미 존재하는 데이터들인데 이 데이터들을 버릴 수는 없기 때문에 DBeaver를 이용하여 데이터를 이전했다.

여러 방법이 존재했는데 쿼리문을 작성하여 직접 데이터를 옮기는 방법과 데이터베이스 관리 도구를 이용하는 방법이 있었는데 직관적이고 확실하게 하기 위해서 DBeaver를 사용했다.

 

2. DBeaver

2-1. DBeaver 설치

 DBeaver 23.3.1 버전을 사용하였다.

 

https://dbeaver.io/download/

Download | DBeaver Community

 

위 사이트에서 os에 맞는 버전으로 설치한다.

 

2-2. DBeaver에 Mysql, Postgresql 연동

1) 데이터베이스 연결

 

 

2) 원하는 데이터베이스 종류 선택

 

3) 대상 db이름과 비밀번호 등 정보를 입력하고 Test Connection으로 연결 상태 확인

 

 

4) 같은 방식으로 옮길 db와 대상 db 모두 연결

 

5) 이관을 원하는 테이블 선택 후 내보내기 선택

 

6) 대상 db 선택

 

7) 쭉 다음 선택 후 진행 선택

 

3. 주의

데이터베이스의 데이터를 이관할 때 대상 데이터베이스의 미리 테이블을 생성해두는 것이 편하다.

DBeaver를 사용하여 이관할 때 PK와 Foreign Key, Constraints, DB의 Sequence들은 복사되지 않기 때문이다.

따라서 미리 데이터베이스의 테이블들을 생성(같은 조건의 테이블이라면)해두고 데이터만 빼오는 것이 나중에 위의 값들을 테이블별로 하나하나 설정해주는 것보다 편하다.

 

물론 데이터베이스 테이블을 설정해두고 값만 옮길 때에도 문제는 발생한다.

• 데이터베이스의 데이터를 이관할 때 모든 테이블을 한번에 선택한다면 위부터 순서대로 진행하기 때문에 foreign key 에서 문제가 생긴다. foreign key의 값이 존재하려면 해당 값을 pk로 가지고 있는 값이 먼저 존재해야하기 때문이다. 이를 해결하기 위해서 모든 테이블을 먼저 옮기고 foreign key 문제가 발생하는 테이블을 한번 더 이관하여 해결했다.(정확한 방법은 아닌 것 같다..)
• 데이터베이스의 종류가 다른 만큼 타입 문제가 발생한다. 이 쪽에서는 좌표 문제가 발생했다. Mysql에서 제공하는 Point 타입과 Postgresql의 geometry가 서로 타입이 달라 데이터가 이관되지 않았다. 일단은 좌표 데이터가 많지 않기 때문에 좌표 데이터는 모두 직접 쿼리문으로 옮기는 것으로 해결하였다...(찜찜하다...)

좌표 타입 문제 해결:

Mysql의 POINT 값을
       ST_X(ST_GeomFromText(ST_AsText(coordinate))) AS lon, 
       ST_Y(ST_GeomFromText(ST_AsText(coordinate))) AS lat
을 통해서 lon, lat으로 분리해주고 이 값을 csv파일로 꺼낸다.

csv파일을 열어 lon, lat형태의 csv파일을 POINT(lon lat)형태로 바꾼다.

 

해당 명령어를 통해 Mysql에서 Postgresql로 데이터를 수동으로 이관한다.

psql -U postgres -d test2 -c "\COPY outdoor_map(outdoor_map_id, name, department_id, coordinate) FROM 'outdoor_map_data.csv' WITH CSV HEADER;"

 

해결

mysql -u root -p -D mydb -e "SELECT outdoor_map_id, CONCAT('SRID=4326;POINT(', ST_X(coordinate), ' ', ST_Y(coordinate), ')') AS coordinate, name, department_id FROM outdoor_map;" > C:\Users\lych0\Documents\dumps\Dump20240102/outdoor_map_data.csv

바로 Geometry 형식으로 csv 파일 추출

 

psql -U postgres -d test2 -c "\COPY outdoor_map(outdoor_map_id, coordinate, name, department_id) FROM 'outdoor_map_data.csv' WITH CSV HEADER DELIMITER E'\t';"

\t로 탭 형식으로 값을 파싱

4. 마무리

데이터베이스 이관이 생각보다는 DBeaver 덕분에 복잡하지 않아서 다행이다. 하지만 PK, FK 등 여러 문제가 발생하여 처음하는 입장에서는 막막했었다.. 해결한 방법이 확실하지도 않기도 하고.. 일단은 급한 불은 끈거 같아서 다행이다. 진행하면서 더 확실한 방법이 있다면 다시 작성해보겠다!!

 

출처

https://wylee-developer.tistory.com/39

https://kwomy.tistory.com/41