Migrasi Database: PostgreSQL ke MySQL – Perbedaan Tipe Data & Script Export/Import

Migrasi Database: PostgreSQL ke MySQL – Perbedaan Tipe Data & Script Export/Import

Tim migrasi proyek mungkin memutuskan untuk beralih dari PostgreSQL ke MySQL karena biaya, kompatibilitas cloud provider, atau kebutuhan legacy. Artikel ini menyajikan panduan end-to-end mulai dari audit tipe data, transformasi skema, hingga import data dengan validasi pasca-migrasi.

Perbandingan Tipe Data

Fungsi & Sintaks yang Berbeda

• now() → NOW() / CURRENT_TIMESTAMP
• current_date → CURDATE()
• generate_series() → recursive CTE atau tabel angka
• ILIKE → LOWER(col) LIKE LOWER(pattern)
• string_agg() → GROUP_CONCAT()
• array_agg() → JSON_ARRAYAGG() (MySQL 8)

Persiapan Sebelum Migrasi

1. Audit skema: pg_dump --schema-only → review table, index, FK, trigger, view.
2. Hapus fitur non-standar: array, enum custom, interval, jsonb GIN index.
3. Normalisasi data array → tabel relasi.
4. Backup penuh: pg_dump -Fc -f backup.dump dbname.
5. Siapkan MySQL target: CREATE DATABASE target CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Export Data dari PostgreSQL

pg_dump -h localhost -U postgres -d sourcedb --schema-only -f schema.sql
pg_dump -h localhost -U postgres -d sourcedb --data-only --column-inserts -f data.sql
# Untuk volume besar: COPY TO CSV
psql -h localhost -U postgres -d sourcedb -c "\COPY users TO 'users.csv' WITH CSV HEADER"

Transformasi Skema & Data (Python)

import re, sys
def convert(sql):
    sql = re.sub(r'\bserial\b', 'INT AUTO_INCREMENT', sql, flags=re.I)
    sql = re.sub(r'\bbigserial\b', 'BIGINT AUTO_INCREMENT', sql, flags=re.I)
    sql = re.sub(r'\buuid\b', 'CHAR(36)', sql, flags=re.I)
    sql = re.sub(r'\bjson[b]?\b', 'JSON', sql, flags=re.I)
    sql = re.sub(r'\bbytea\b', 'LONGBLOB', sql, flags=re.I)
    sql = re.sub(r'\btimestamp with time zone\b', 'TIMESTAMP', sql, flags=re.I)
    sql = re.sub(r'\btimestamp without time zone\b', 'DATETIME', sql, flags=re.I)
    sql = re.sub(r'\binterval\b', 'INT', sql, flags=re.I)
    sql = re.sub(r'\binet\b|\bcidr\b', 'VARCHAR(43)', sql, flags=re.I)
    sql = re.sub(r'\bboolean\b', 'TINYINT(1)', sql, flags=re.I)
    sql = re.sub(r'\bmoney\b', 'DECIMAL(19,4)', sql, flags=re.I)
    sql = re.sub(r'\bnow\(\)', 'NOW()', sql, flags=re.I)
    return sql
with open(sys.argv[1]) as f: print(convert(f.read()))

Import ke MySQL

mysql -u root -p targetdb < schema_mysql.sql
# Load data via CSV
mysql -u root -p targetdb -e "
LOAD DATA LOCAL INFILE 'users.csv' INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;"

Validasi Pasca-Migrasi

• Jumlah baris: SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='targetdb';
• Integrity FK: SELECT * FROM information_schema.table_constraints WHERE constraint_type='FOREIGN KEY';
• Encoding: SHOW VARIABLES LIKE 'character_set%';

Troubleshooting Umum

Checklist Produksi

• ✅ Semua tabel termigrasi, row count match
• ✅ FK & index dibuat ulang
• ✅ Aplikasi diupdate: connection string & timezone handling
• ✅ Load test staging
• ✅ Rollback plan & snapshot siap

Tag: #PostgreSQL #MySQL #DatabaseMigration #DevOps #DataEngineering