MySQL에서 PostgreSQL으로 마이그레이션 (Shell 환경)

이기종 마이그레이션을 실습해 보고 간단하게 어떠한 과정을 거치는 알아보려고 한다.

환경은 MySQL 8버전 PostgreSQL 15 버전이다.

 

MySQL 데이터베이스 접속

mysql -u root -p

 

MySQL 데이터베이스 생성

먼저 MySQL에서 데이터베이스를 생성한다.

CREATE DATABASE testdb;

 

MySQL 테이블 생성

생성한 데이터베이스에 접속한 후 테이블을 생성한다.

USE testdb;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(15) NOT NULL,
    position VARCHAR(15),
    hire_date DATE
);

 

샘플 데이터 삽입

INSERT INTO employees (name, position, hire_date) VALUES 
('John Doe', 'Manager', '2020-01-15'),
('Jane Smith', 'Developer', '2019-03-23'),
('Emily Davis', 'Analyst', '2021-06-11');

 

데이터베이스 및 데이터 백업

이 과정에서 데이터와 스키마를 따로 덤프 하려고 한다.

즉 지금은 테이블 구조와 인덱스, 트리거 등의 정의만 포함된다.

MySQL Shell에서 나와서 밑의 명령어들을 입력한다.

mysqldump -u root -p --no-data testdb > schema.sql

 

테이블 구조 없이 데이터만 덤프 한다.

mysqldump -u root -p --no-create-info testdb > data.sql

 

PostgreSQL 데이터베이스 접속

psql -U postgres -W

 

PostgreSQL 데이터베이스 생성

CREATE DATABASE testdb;

 

PostgreSQL 스키마 변환 및 적용

pgloader 과정과 수동으로 마이그레이션 하는 2가지 과정을 해보려고 한다.

 

1. pgloader를 사용하여 MySQL 데이터를 PostgreSQL로 마이그레이션

MySQL과 PostgreSQL 간의 데이터 타입을 자동으로 변환해 주기에 따로 덤프파일을 써서 고칠 필요가 없다.

하지만 변환된 데이터 타입이 적절한지 확인해야 한다.

PostgreSQL Shell에서 나와서 밑의 명령어를 실행한다.

pgloader mysql://root:[password]@localhost/testdb postgresql://postgres:[password]@localhost/testdb

 

만약 인증 에러가 나온다면 MySQL의 기본 인증 방식을 변경한다.

ERROR mysql: Failed to connect to mysql at "localhost" (port 3306) as user "root": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

즉 기존에 caching_sha2_password로 되어있다면 mysql_native_password로 변경해야 한다. 참고 페이지

이후에 꼭 MySQL 서버 재부팅은 필수!

 

마이그레이션을 성공하게 되면 이런 문구들과 함께 출력이 된다.

다시 PostgreSQL에 접속해서 testdb에 employees 스키마가 잘 옮겨졌는지 확인한다.

 

2. pgloader를 사용하지 않고 마이그레이션

MySQL에서 덤프 한 스키마 파일을 PostgreSQL에 맞게 변환하여 적용해야 한다.

 

먼저 schema.sql을 수정한다.

덤프파일에 들어갔을 때 나와있는 Create문을 수정해야 한다.

 

기존 MySQL CREATE문을 PostgreSQL CREATE문으로 변환해 준다.

--MYSQL
DROP TABLE IF EXISTS employees;
CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(15) NOT NULL,
  `position` varchar(15) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--PostgreSQL
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(15) NOT NULL,
  position VARCHAR(15),
  hire_date DATE
);

변경 사항

1. /*! 40101 SET... */와 같은 명령어는 PostgreSQL에서 필요 없기에 설정 명령어와 주석을 제거한다.

2. AUTO_INCREMENT -> SERIAL

3. 테이블 옵션 제거 (ENGINE=InnoDB, DEFAULT CHARSET, COLLATE)

 

덤프파일 수정이 완료되었다면 이제 PostgreSQL에 적용시킨다.

psql -U postgres -d testdb -f schema.sql

--PostgreSQL 접속
psql -U postgres -W

--testdb 사용
\c testdb

--테이블 확인
\d employees

 

다음 data.sqld을 수정한다.

덤프파일에 들어갔을 때 나와있는 Insert문을 수정해야 한다.

 

기존 MySQL Insert문을 PostgreSQL Insert문으로 변환해 준다.

--MySQL
LOCK TABLES `employees` WRITE;
INSERT INTO `employees` VALUES (1,'John Doe','Manager','2020-01-15'),(2,'Jane Smith','Developer','2019-03-23'),(3,'Emily Davis','Analyst','2021-06-11');
UNLOCK TABLES;

--PostgreSQL
INSERT INTO employees (id, name, position, hire_date) VALUES
(1, 'John Doe', 'Manager', '2020-01-15'),
(2, 'Jane Smith', 'Developer', '2019-03-23'),
(3, 'Emily Davis', 'Analyst', '2021-06-11');

변경 사항

1. /*! 40101 SET... */와 같은 명령어는 PostgreSQL에서 필요 없기에 설정 명령어와 주석을 제거한다.

2. LOCK TABLES와 UNLOCK TABLES 명령어를 제거

3. ALTER TABLE... DISABLE KEYS와 ALTER TABLE ... ENABLE KEYS 명령어를 제거

 

덤프파일 수정이 완료되었다면 이제 PostgreSQL에 적용시킨다.

psql -U postgres -d testdb -f data.sql

--PostgreSQL 접속
psql -U postgres -W

--testdb 사용
\c testdb

--데이터 확인
select * from employees