When you’re moving from MySQL to PostgreSQL, the transition can feel like a leap across two different worlds. Each database engine has its own quirks, data types, and performance characteristics. pgloader is a powerful, open‑source tool that bridges this gap with minimal fuss. In this guide we’ll walk through every step of a typical migration, from setting up your environment to verifying that everything is working as expected. By the end, you’ll have a clean, fully‑functional PostgreSQL database ready to serve your applications.
What You’ll Need
- Access to the MySQL source database (root or a user with SELECT, SHOW VIEW, and LOCK TABLES privileges)
- Access to the PostgreSQL target database (superuser or a role with CREATE privileges)
- pgloader installed on the migration host (available via apt, brew, or compiled from source)
- Basic command‑line experience (bash, zsh, or PowerShell)
- A backup strategy for both source and target (mysqldump, pg_dump, or logical backups)
Step 1: Install and Verify Your Tools
Before you can migrate, make sure you have the right versions of the databases and pgloader. The following commands assume a Linux or macOS environment, but the concepts apply on Windows as well.
Install PostgreSQL (replace 15 with your preferred major version):
sudo apt-get update sudo apt-get install postgresql-15
Install MySQL (example for MySQL 8.0):
sudo apt-get install mysql-server
Install pgloader (via Homebrew on macOS or compile from source on Linux):
brew install pgloader # or sudo apt-get install pgloader
Verify the installations:
psql --version mysql --version pgloader --version
All three should report a version number. If pgloader is missing, check your PATH or reinstall.
Step 2: Prepare the Source Database
Data integrity starts with a clean source. Log into MySQL and run a quick health check.
mysql -u root -p mysql> SHOW STATUS LIKE "Threads_running"; mysql> SHOW VARIABLES LIKE "max_allowed_packet";
Make sure you have a recent logical backup. Even if you plan to migrate live data, a snapshot gives you a fallback point.
mysqldump -u root -p --single-transaction --quick --lock-tables=false --all-databases > mysql_all.sql
For a single database:
mysqldump -u root -p --single-transaction --quick --lock-tables=false db_name > db_name.sql
Store these files in a safe location and verify their integrity with md5sum.
Step 3: Create the Target PostgreSQL Database
Open a psql session and create a new database and user that will own the migrated schema.
sudo -u postgres psql postgres=# CREATE USER mig_user WITH PASSWORD 'strongpass'; postgres=# CREATE DATABASE mig_db OWNER mig_user; postgres=# GRANT ALL PRIVILEGES ON DATABASE mig_db TO mig_user; postgres=# q
Make sure the new database is empty and that you have a clean environment. If you need to migrate schemas from multiple databases, you can create them all here.
Step 4: Run a Dry‑Run Migration
pgloader offers a --dry-run option that parses the source and target, builds the transformation plan, and reports potential issues without touching the data. This is your safety net.
pgloader --dry-run mysql://root:rootpass@localhost/db_name postgresql://mig_user:strongpass@localhost/mig_db
Read the output carefully. Look for warnings about data type mismatches, unsupported features, or missing indexes. Resolve any issues before proceeding. Common adjustments include:
- Adding
--schemaor--no-foreign-keysflags to control schema generation. - Specifying
--createor--no-createto toggle table creation. - Using
--type-mappingto override default type conversions.
Step 5: Execute the Full Migration
Once you’re satisfied with the dry‑run, run the actual migration. Include the --clean flag if you want pgloader to drop and recreate tables, and --verbose for detailed logs.
pgloader --clean --verbose mysql://root:rootpass@localhost/db_name postgresql://mig_user:strongpass@localhost/mig_db
pgloader will:
- Connect to MySQL, read table schemas, and map data types.
- Create tables, indexes, and constraints in PostgreSQL.
- Stream rows using COPY for speed.
- Generate and apply triggers, sequences, and foreign keys.
Monitor the output for any errors. If a table fails, pgloader will skip it and continue. You can re‑run the migration after fixing the issue.
Step 6: Verify and Post‑Migration Cleanup
After the migration completes, validate the data integrity.
psql -U mig_user -d mig_db -c "SELECT COUNT(*) FROM some_table;" mysql -u root -p -e "SELECT COUNT(*) FROM some_table;" db_name
Compare row counts, sample data, and checksums. You can also run pg_dump and mysqldump and compare the dumps.
Once you’re confident, consider:
- Rebuilding indexes:
REINDEX DATABASE mig_db; - Updating statistics:
ANALYZE; - Testing application connectivity and performance.
Common Mistakes to Avoid
1. Ignoring Character Sets – MySQL’s utf8mb4 maps to PostgreSQL’s UTF8. If you skip this, you’ll get data corruption on multibyte characters.
2. Overlooking Indexes – pgloader creates indexes by default, but if you use --no-indexes inadvertently, performance suffers.
3. Missing Foreign Keys – Dropping --no-foreign-keys can lead to orphaned rows if you forget to re‑enable constraints.
4. Large Data Volumes Without Vacuuming – After a massive import, run VACUUM FREEZE to prevent transaction ID wrap‑around.
5. Skipping Backup of Target – Always backup the PostgreSQL database before running --clean.
Tips and Tricks
• Use --jobs to parallelize the migration if you have many tables. Example: --jobs 4.
• For very large tables, consider splitting the migration: first migrate schema, then use pg_dump --column-inserts for a fallback.
• If you need to preserve stored procedures or triggers, export them from MySQL and manually port them to PostgreSQL. pgloader does not convert procedural code.
• Leverage the --pre and --post hooks to run custom SQL before or after migration.
Frequently Asked Questions
How do I handle very large tables (hundreds of GB) efficiently?
pgloader uses COPY, which is fast, but the source lock time can be significant. Use --single-transaction on MySQL to avoid locking, and set max_allowed_packet high enough. Consider staging the data in smaller chunks or using pg_dump --data-only as a fallback.
Can I migrate only a subset of tables?
Yes. In the pgloader command, specify the tables you want: pgloader mysql://root:pass@localhost/db_name:table1,table2 postgresql://user:pass@localhost/db_name. You can also use a pgloader.conf file with include-table directives.
What about views, stored procedures, and triggers?
pgloader does not automatically migrate MySQL views or stored procedures. Export them with mysqldump --routines --triggers --no-data and manually translate the SQL to PostgreSQL syntax. For triggers, recreate them using PostgreSQL’s CREATE TRIGGER statements.
Conclusion
Migrating from MySQL to PostgreSQL with pgloader is a systematic process that, when done carefully, yields a reliable, performant database. By preparing your environment, running a dry‑run, executing the migration, and validating the results, you can avoid common pitfalls and ensure a smooth transition. Remember that every database has its nuances—stay vigilant, test thoroughly, and leverage pgloader’s flexibility to tailor the migration to your specific needs. Happy migrating!
