18.2 MySQL → PostgreSQL

MySQL/MariaDB에서 PostgreSQL로의 마이그레이션은 Oracle보다 단순하지만 데이터 모델 차이·정확성·트랜잭션의 PostgreSQL 표준에 맞춰 코드 검토가 필요합니다. 차이점과 표준 절차를 정리합니다.

주요 차이점

영역	MySQL	PostgreSQL
트랜잭션	InnoDB만 ACID, MyISAM은 X	모든 테이블 ACID
격리 수준 기본	Repeatable Read	Read Committed
식별자	backtick `	표준 더블따옴표 `"`
대소문자	일반적으로 대소문자 무시 (collation)	구분 (citext 또는 LOWER)
`AUTO_INCREMENT`	컬럼 옵션	`bigserial` 또는 `GENERATED ALWAYS AS IDENTITY`
`UNSIGNED int`	가능	없음 — `bigint`로
`TINYINT(1)` boolean	관행	`boolean` 타입
`DATETIME`	timezone 없음	`timestamptz`
`0000-00-00` 같은 invalid date	가능	불가 — sql_mode 검토
ENUM	컬럼 옵션	`CREATE TYPE ... AS ENUM`
spatial	기본	PostGIS extension
JSON / JSONB	JSON 타입	JSONB (더 빠름·인덱싱 강력)
Replication	binlog	WAL

도구 — `pgloader`

가장 표준입니다. Common Lisp 작성, 매우 빠릅니다.

sudo dnf install -y pgloader
# 또는 Docker
docker run --rm dimitri/pgloader pgloader ...

기본 사용

pgloader mysql://root:secret@mysql.example.com/app_db \
         postgresql://pgadmin:secret@pg.example.com/app_db

한 줄로 스키마·데이터 모두 마이그레이션. 작은~중간 DB는 즉시.

상세 설정 — `.load` 파일

LOAD DATABASE
   FROM mysql://root:secret@mysql.example.com/app_db
   INTO postgresql://pgadmin:secret@pg.example.com/app_db

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,
     multiple readers per thread, rows per range = 50000

CAST type tinyint when (= precision 1) to boolean using tinyint-to-boolean,
     type datetime to timestamptz drop default drop not null,
     type date drop not null drop default using zero-dates-to-null,
     type int with unsigned to bigint

INCLUDING ONLY TABLE NAMES MATCHING ~/^orders/, ~/^users/

ALTER SCHEMA 'app_db' RENAME TO 'public'

BEFORE LOAD DO
   $$ SET search_path TO public; $$,
   $$ CREATE EXTENSION IF NOT EXISTS citext; $$;

CAST 절로 타입 변환 규칙 명시합니다. BEFORE LOAD DO로 확장 활성화합니다.

ora2pg도 MySQL 지원

ORACLE_DSN dbi:mysql:database=app_db;host=mysql.example.com

ora2pg가 MySQL도 source로 지원합니다. 평가 report 동일하게 활용합니다.

CDC — 무중단

대용량 마이그레이션:

    flowchart LR
  MY["MySQL primary"]
  DBZ["Debezium (Kafka Connect)"]
  KAFKA["Kafka"]
  PG["PostgreSQL"]

  MY --> DBZ --> KAFKA --> PG

  classDef my fill:#fed7aa,stroke:#c2410c,color:#7c2d12
  classDef mid fill:#dbeafe,stroke:#1d4ed8,color:#1e3a8a
  classDef pg fill:#d1fae5,stroke:#047857,color:#064e3b
  class MY my
  class DBZ,KAFKA mid
  class PG pg

Debezium MySQL connector → Kafka → JDBC sink. 또는 AWS DMS·Google DMS도 동일.

자주 마주치는 타입 변환

TINYINT(1) → boolean

-- MySQL
is_active TINYINT(1) DEFAULT 0

-- PostgreSQL
is_active boolean DEFAULT false

pgloader가 자동 변환합니다.

`0000-00-00` 잘못된 날짜

MySQL의 허용된 잘못된 날짜가 PG에서는 INSERT 실패. pgloader가 NULL로 변환합니다.

USING zero-dates-to-null

UNSIGNED int

-- MySQL
user_id INT UNSIGNED

-- PostgreSQL
user_id bigint   -- unsigned int 없음, 더 큰 타입으로

ENUM

-- MySQL
status ENUM('active','inactive')

-- PostgreSQL
CREATE TYPE status_enum AS ENUM ('active', 'inactive');
status status_enum

또는 lookup table(14.1 참고)로 변환합니다.

JSON → JSONB

-- MySQL
data JSON

-- PostgreSQL
data JSONB    -- 권장. JSON도 가능하지만 JSONB가 거의 항상 답

식별자 quoting

MySQL의 `column`은 PG의 "column". application 코드 점검합니다.

-- MySQL
SELECT `user_id` FROM `users`;

-- PostgreSQL
SELECT user_id FROM users;     -- 일반적으로 quoting 불필요
SELECT "user_id" FROM "users"; -- 필요시

case-sensitivity 함정

MySQL은 기본적으로 WHERE email = 'A@B.com'이 a@b.com과 매칭. PG는 다름.

-- MySQL 동작 → PG에서
WHERE LOWER(email) = LOWER('A@B.com')

-- 또는 citext 컬럼
ALTER TABLE users ALTER COLUMN email TYPE citext;

AUTO_INCREMENT → IDENTITY

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL (구식)
id SERIAL PRIMARY KEY

-- PostgreSQL (PG 10+, SQL 표준, 권장)
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

pgloader가 자동 변환합니다. IDENTITY가 더 표준·안전합니다.

REPLACE INTO → ON CONFLICT

-- MySQL
REPLACE INTO users (id, name) VALUES (1, 'alice');
INSERT IGNORE INTO users ...;
INSERT ... ON DUPLICATE KEY UPDATE ...;

-- PostgreSQL
INSERT INTO users (id, name) VALUES (1, 'alice')
  ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

INSERT ... ON CONFLICT DO NOTHING;

LIMIT 문법

같습니다. MySQL의 LIMIT offset, count 형식은 PG에서 안 됩니다.

-- MySQL
LIMIT 10, 20

-- PostgreSQL
LIMIT 20 OFFSET 10

검증

# row count
mysql -e "SELECT 'orders', COUNT(*) FROM orders" -B
psql -c "SELECT 'orders', COUNT(*) FROM orders"

# checksum
mysql -e "SELECT MD5(GROUP_CONCAT(...)) FROM ..." -B

큰 테이블은 MD5 합계·sum·min/max으로 비교합니다.

cutover 절차

Oracle과 동일 (18.1):

초기 데이터 마이그레이션 (pgloader 또는 CDC)
CDC 활성화
application read 점진 PG로
write cut-over
MySQL는 read-only 관찰 기간
decommission

ORM 측면

ORM	PG·MySQL 차이
Django	`engine` 변경. 일부 쿼리 (`__icontains` 등) 검증
Rails (ActiveRecord)	`adapter` 변경. `like`/`ilike` 차이
Sequelize	`dialect` 변경. ENUM·JSON 처리 검증
TypeORM	같음
Hibernate	dialect 변경
SQLAlchemy	URL 변경. 일부 reflection 차이

코드 변경은 ORM이 잘 추상화하지만 raw SQL 부분만 수동.

정리

pgloader가 MySQL → PG 표준 도구입니다. 자동 변환 강력
작은~중간 DB는 한 줄로 끝
대용량은 Debezium·DMS로 CDC
자주 변환: TINYINT(1)→bool, UNSIGNED→bigint, ENUM→TYPE/lookup, JSON→JSONB
case-sensitivity, REPLACE INTO 등 application 코드 점검
ORM은 대부분 추상화, raw SQL만 수동 확인

다음 절(18.3)에서는 같은 PostgreSQL의 메이저 버전 업그레이드 — pg_upgrade를 봅니다.

18.1 Oracle → PostgreSQL (ora2pg)18.3 메이저 버전 업그레이드 (pg_upgrade)