Di chuyển MySQL sang PostgreSQL bằng pgLoader: Cẩm nang sinh tồn lúc 2 giờ sáng

Table of Contents

Cơn ác mộng di chuyển dữ liệu lúc 2 giờ sáng

Thông báo Slack bắt đầu dồn dập lúc 1:45 sáng. Instance MySQL 5.7 cũ kỹ của chúng tôi—một “con quái vật” mà chúng tôi đã dự định cho nghỉ hưu từ nhiều tháng trước—cuối cùng đã gục ngã. Sử dụng CPU luôn ở mức 99%, và connection pool trở thành một “nghĩa địa” của các luồng bị deadlock. Chúng tôi đã lên kế hoạch chuyển sang PostgreSQL để tận dụng khả năng xử lý đồng thời và đánh chỉ mục vượt trội, nhưng lịch trình vừa bị đẩy từ “tháng sau” thành “ngay bây giờ”.

Hãy quên mysqldump đi. Việc cố gắng tìm kiếm và thay thế thủ công trên một database 400GB trong tình trạng thiếu ngủ là con đường ngắn nhất dẫn đến hỏng dữ liệu. Bạn cần một công cụ hiểu được sự khác biệt về bản chất giữa hai hệ quản trị này. Đó là lúc pgLoader cứu nguy cho môi trường production của chúng tôi. Nó không chỉ là một đường ống dẫn dữ liệu; nó là một bộ máy chuyển đổi giúp tự động hóa việc chuyển đổi schema và xử lý ánh xạ kiểu dữ liệu phức tạp ngay lập tức.

Bắt đầu nhanh (5 phút)

If schema của bạn sạch sẽ, bạn có thể bắt đầu di chuyển dữ liệu chỉ với một câu lệnh duy nhất. Trên Ubuntu hoặc Debian, việc cài đặt rất đơn giản:

sudo apt-get install pgloader

Sau khi cài đặt, bạn có thể thử di chuyển trực tiếp bằng cách truyền chuỗi kết nối nguồn và đích. Nó trông như thế này:

pgloader mysql://user:password@localhost/source_db \
         postgresql:///target_db

Câu lệnh này sẽ tự khám phá schema, tạo bảng trong Postgres và truyền dữ liệu (stream). Nó hoạt động tốt cho các dự án nhỏ và đơn giản. Tuy nhiên, các database production thường có các mối quan hệ phức tạp và các ràng buộc “dễ dãi” của MySQL khiến câu lệnh một dòng này thất bại. Để xử lý những mớ hỗn độn trong thực tế, chúng ta cần sử dụng các tệp lệnh load.

Tìm hiểu sâu: Tệp lệnh Load

Sự chính xác là yếu tố sống còn khi bạn di chuyển hàng triệu bản ghi. Khi lần thử đầu tiên của chúng tôi bị sập ở một bảng có 50 triệu hàng do lỗi bảng mã (encoding mismatch), tôi đã chuyển sang dùng tệp .load. Định dạng này cho phép bạn kiểm soát chi tiết các quy tắc ép kiểu (casting rules) và dọn dẹp trước khi di chuyển.

Tạo một tệp tên là migrate.load:

LOAD DATABASE
     FROM mysql://db_user:db_pass@source_host/old_db
     INTO postgresql://pg_user:pg_pass@target_host/new_db

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1

 CAST type tinyint to boolean drop typemod,
      type datetime to timestamptz,
      type double to precision

 BEFORE LOAD DO
   $$ drop schema if exists public cascade; $$,
   $$ create schema public; $$

 AFTER LOAD DO
   $$ ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email); $$;

Hãy chú ý kỹ phần CAST. MySQL nổi tiếng là “thoải mái” với các kiểu dữ liệu, nhưng PostgreSQL thì rất khắt khe. Ví dụ, MySQL thường dùng tinyint(1) cho kiểu boolean. Nếu không ép kiểu rõ ràng, ứng dụng của bạn sẽ bị sập ngay khi cố gắng chèn giá trị true vào một cột mà Postgres nghĩ là số nguyên nhỏ.

Trong quá trình di chuyển, chúng tôi cũng phải xử lý các dữ liệu phụ trợ cũ nằm trong các tệp CSV lộn xộn. Để chuẩn bị cho việc nhập dữ liệu, tôi đã sử dụng toolcraft.app/vi/tools/data/csv-to-json. Nó chạy hoàn toàn trong trình duyệt, nghĩa là các đoạn dữ liệu production nhạy cảm của chúng tôi không bao giờ rời khỏi máy cục bộ. Tôi tin dùng ToolCraft cho những tác vụ định dạng nhanh này vì nó riêng tư, nhanh chóng và không yêu cầu đăng nhập.

Xử lý cơn ác mộng “Zero Date”

Có một lỗi cụ thể là nỗi ám ảnh của tôi lúc 3 giờ sáng: giá trị timestamp 0000-00-00 00:00:00 của MySQL. PostgreSQL từ chối giá trị này vì đó là ngày không hợp lệ. Nếu cột của bạn được đánh dấu là NOT NULL, toàn bộ quá trình di chuyển sẽ dừng lại. Bạn không thể chỉ đơn giản là lờ nó đi.

Cách khắc phục sạch sẽ nhất là xử lý việc chuyển đổi trực tiếp trong quy tắc casting của pgLoader:

CAST type datetime when default "0000-00-00 00:00:00" to timestamptz drop default drop not null

Nếu bạn đang xử lý các bảng dữ liệu khổng lồ, hãy sử dụng mệnh đề INCLUDING ONLY TABLE NAMES MATCHING để di chuyển theo từng phần. Điều này ngăn việc lỗi ở một bảng log không quan trọng làm đảo ngược (roll back) quá trình di chuyển của các bảng cốt lõi như users hoặc transactions.

INCLUDING ONLY TABLE NAMES MATCHING 'users', 'orders', 'products'

Trong khi gỡ lỗi các chuyển đổi này, tôi đã sử dụng https://toolcraft.app/vi/tools/developer/json-formatter để xác minh payload của API. Tôi cần đảm bảo cấu trúc Postgres mới không làm thay đổi định dạng JSON mà frontend mong đợi. Vì nó chạy ở phía client, tôi không phải lo lắng về việc API key hay dữ liệu khách hàng bị gửi đến máy chủ của bên thứ ba.

Mẹo thực tế để về đích

Sau ba giờ thử sai, tôi đã xây dựng một danh sách kiểm tra giúp giải cứu buổi sáng của chúng tôi:

Chạy ANALYZE: MySQL và Postgres xử lý số liệu thống kê khác nhau. Ngay sau khi load xong, hãy chạy ANALYZE; trong Postgres. Việc này cập nhật bộ lập kế hoạch truy vấn (query planner) để các lệnh join không tốn cả thanh xuân để thực hiện.
Kiểm tra Sequence: Đảm bảo các ID tự động tăng (auto-increment) được đồng bộ hóa. Chạy SELECT last_value FROM your_table_id_seq; để chắc chắn lần chèn dữ liệu tiếp theo không gây lỗi vi phạm khóa chính.
Chuẩn hóa bảng mã: Nếu dữ liệu MySQL của bạn đang kẹt ở latin1, hãy chuyển nó sang utf8mb4 trước khi bắt đầu. pgLoader có thể chuyển mã, nhưng sẽ an toàn hơn nhiều nếu bắt đầu với nguồn UTF-8 sạch sẽ.
Hiện đại hóa với UUID: Nếu bạn đang cập nhật schema trong quá trình di chuyển, hãy cân nhắc chuyển sang UUID cho các khóa chính. Tôi đã sử dụng https://toolcraft.app/vi/tools/developer/uuid-generator để nhanh chóng tạo ID test cho môi trường staging.

Đến 5 giờ sáng, dữ liệu đã được đồng bộ và ứng dụng đã chạy thực tế trên Postgres. Độ trễ API của chúng tôi giảm 30%, và thời gian truy vấn cho các lệnh join nặng nhất giảm từ 800ms xuống còn 45ms mượt mà. Sử dụng pgLoader cho phép chúng tôi tự động hóa các phần tẻ nhạt để tập trung vào các trường hợp ngoại lệ. Nếu bạn đang đối mặt với một cuộc di chuyển tương tự, hãy đầu tư thời gian vào một tệp .load chuẩn chỉnh—đó là sự khác biệt giữa một lần chuyển đổi thành công và một đêm dài mệt mỏi để sửa lỗi SQL thủ công.