如何使用pgloader将MySQL数据库迁移至PostgreSQL？

Great question! Migrating from MySQL to PostgreSQL with pgloader is a smart move—it’s designed to handle schema and data transfers smoothly, taking care of most of the tedious conversion work for you. Let’s walk through the process step by step:

First, get pgloader set up on the machine you’ll run the migration from (it can be your local machine, a server, or even the PostgreSQL server itself—just make sure it can connect to both databases):

• On Debian/Ubuntu-based systems:

  sudo apt update && sudo apt install pgloader
  

• On macOS (using Homebrew):

  brew install pgloader
  

• For the absolute latest version, you can build from source, but the package manager versions work perfectly for most standard migrations.

Don’t skip this step—small oversights here can cause migration failures:

• MySQL Setup:
  • Create a dedicated MySQL user with SELECT, SHOW VIEW, LOCK TABLES permissions on your target database (pgloader needs these to read schema details and safely extract data).
  • Ensure MySQL accepts connections from the machine running pgloader: adjust bind-address in my.cnf/my.ini if it’s restricted to localhost, and grant remote access to your migration user if needed.
• PostgreSQL Setup:
  • Create an empty PostgreSQL database (pgloader won’t create this for you automatically).
  • Make sure your PostgreSQL user has CREATEDB, CREATE TABLE permissions on the new database to let pgloader build the schema.

You have two options here—pick the one that fits your needs:

Option 1: Quick Command Line Migration #

For straightforward, small-to-medium databases, you can run everything in a single command. The syntax is:

pgloader mysql://[mysql-username]:[mysql-password]@[mysql-host]/[mysql-db-name] pgsql://[pg-username]:[pg-password]@[pg-host]/[pg-db-name]

Example:

pgloader mysql://johndoe:mypassword123@localhost/mysql_store pgsql://janedoe:pgpass456@localhost/postgres_store

Note: If your password has special characters (like @, #, or !), you’ll need to URL-encode them (e.g., @ becomes %40).

Option 2: Configuration File (For Complex Migrations) #

If you need fine-grained control (like custom data type conversions, excluding certain tables, or adjusting performance settings), use a configuration file. Create a file (e.g., mysql-to-pg.load) with this structure:

LOAD DATABASE
     FROM mysql://johndoe:mypassword123@localhost/mysql_store
     INTO pgsql://janedoe:pgpass456@localhost/postgres_store

 WITH include drop, create tables, create indexes, reset sequences

 SET maintenance_work_mem to '64MB', work_mem to '4MB'

 CAST type datetime to timestamptz
      drop default drop not null,
 type date drop not null,
 enum type to text;

Then run the migration with:

pgloader mysql-to-pg.load

Let’s break down the key config options:

• include drop: Drops existing tables in PostgreSQL before migrating (use this only if the target DB is empty—be careful!)
• create tables: Replicates MySQL’s schema structure in PostgreSQL
• create indexes: Copies over all indexes from MySQL
• reset sequences: Sets PostgreSQL sequence values to match the highest ID in your migrated data (so new inserts don’t clash)
• The CAST section lets you override default data type conversions—pgloader handles most automatically, but this is useful for edge cases like MySQL ENUMs (converted to text here) or datetime types.

Never assume the migration worked perfectly—verify these things:

• Row count check: Run SELECT COUNT(*) FROM [table_name]; in both databases to ensure all data was transferred.
• Schema verification: Double-check data types, indexes, and constraints (foreign keys, unique constraints) to make sure they match your expectations.
• Application testing: Run your app’s core queries against the PostgreSQL database to catch MySQL-specific functions that don’t translate (e.g., GROUP_CONCAT() in MySQL becomes STRING_AGG() in PostgreSQL, NOW() works but CURRENT_TIMESTAMP() is more idiomatic in PG).

• Case Sensitivity: PostgreSQL is case-sensitive for table/column names (unless quoted), while MySQL isn’t by default. pgloader usually handles this by quoting identifiers, but double-check any mixed-case names.
• Foreign Key Order: pgloader might try to load tables out of order, causing foreign key errors. If this happens, temporarily disable triggers in PostgreSQL before migration:

  -- Run this for each table with foreign keys
  ALTER TABLE [table_name] DISABLE TRIGGER ALL;
  -- After migration, re-enable them:
  ALTER TABLE [table_name] ENABLE TRIGGER ALL;
  

• Large Databases: For big datasets, bump up work_mem and maintenance_work_mem in your pgloader config to avoid memory bottlenecks and speed up the migration.

内容的提问来源于stack exchange，提问作者helsont