
How to Migrate MySQL Database to Postgres Using pgloader
Migrating from MySQL to PostgreSQL is a common challenge that many teams face when scaling applications or seeking better performance, advanced features, and more robust data integrity constraints. While manual migration can be tedious and error-prone, pgloader provides an automated solution that can handle the heavy lifting of data transfer, schema conversion, and type mapping between these two popular database systems. This guide will walk you through the complete process of using pgloader to migrate your MySQL database to PostgreSQL, covering everything from installation and configuration to troubleshooting common issues and optimizing performance.
Understanding pgloader and How It Works
pgloader is a powerful data loading tool specifically designed to migrate data into PostgreSQL from various source databases, including MySQL, SQLite, MS SQL Server, and CSV files. What makes pgloader particularly effective for MySQL-to-PostgreSQL migrations is its ability to automatically handle schema differences, data type conversions, and even rebuild indexes and constraints.
The tool works by connecting to both source and destination databases simultaneously, reading the MySQL schema structure, converting it to PostgreSQL-compatible DDL statements, and then streaming data in batches. It handles complex scenarios like foreign key relationships, triggers, and custom data types that would otherwise require manual intervention.
Key advantages of pgloader include:
- Automatic schema conversion with intelligent type mapping
- Parallel data loading for improved performance
- Built-in error handling and data validation
- Support for custom transformation rules
- Comprehensive logging and progress reporting
Unlike simple dump-and-restore approaches, pgloader can transform data on-the-fly, making it ideal for migrations where direct schema compatibility isn’t possible.
Installation and Initial Setup
Getting pgloader up and running depends on your operating system. Here are the most common installation methods:
Ubuntu/Debian:
sudo apt-get update
sudo apt-get install pgloader
CentOS/RHEL:
sudo yum install epel-release
sudo yum install pgloader
macOS (using Homebrew):
brew install pgloader
Docker (recommended for consistent environments):
docker pull dimitri/pgloader:latest
Before starting the migration, ensure both MySQL and PostgreSQL servers are accessible and you have the necessary permissions. You’ll need:
- READ access to the MySQL source database
- CREATE and INSERT permissions on the PostgreSQL target database
- Network connectivity between pgloader and both database servers
Test your connections first:
# Test MySQL connection
mysql -h source_host -u username -p source_database
# Test PostgreSQL connection
psql -h target_host -U username -d target_database
Step-by-Step Migration Process
The migration process with pgloader involves creating a command file that defines source and target connections, transformation rules, and migration parameters.
Basic Migration Command
For simple migrations, you can use pgloader’s command-line syntax:
pgloader mysql://user:password@mysql-host/source_db postgresql://user:password@pg-host/target_db
However, for production environments, using a configuration file provides better control:
Creating a Configuration File
Create a file named migration.load
:
LOAD DATABASE
FROM mysql://mysql_user:mysql_pass@mysql_host:3306/source_database
INTO postgresql://pg_user:pg_pass@pg_host:5432/target_database
WITH include drop, create tables, create indexes, reset sequences,
workers = 8, concurrency = 1,
multiple readers per thread, rows per range = 50000
SET MySQL PARAMETERS
net_read_timeout = '120s',
net_write_timeout = '120s'
SET PostgreSQL PARAMETERS
maintenance_work_mem to '1GB',
checkpoint_segments to 64
CAST type datetime to timestamptz
drop default drop not null using zero-dates-to-null,
type date drop not null drop default using zero-dates-to-null
BEFORE LOAD DO
$$ CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; $$
ALTER SCHEMA 'source_database' RENAME TO 'public';
Running the Migration
Execute the migration using your configuration file:
pgloader migration.load
For Docker users:
docker run --rm -v $(pwd):/data dimitri/pgloader:latest pgloader /data/migration.load
Advanced Configuration Options
For complex migrations, you might need additional configuration options:
LOAD DATABASE
FROM mysql://user:pass@mysql-host/source_db
INTO postgresql://user:pass@pg-host/target_db
WITH include drop, create tables, create indexes, reset sequences,
workers = 16, concurrency = 1,
multiple readers per thread, rows per range = 100000,
prefetch rows = 25000
SET MySQL PARAMETERS
net_read_timeout = '300s',
net_write_timeout = '300s'
-- Handle specific table transformations
LOAD FROM mysql_table
INTO postgresql_table (
id, name, created_at, updated_at
)
WITH truncate,
skip header = 1,
fields optionally enclosed by '"',
fields escaped by double-quote,
fields terminated by ','
-- Custom data transformations
CAST column "price" to numeric drop typemod,
column "status" to text,
column "created_date" to timestamp using zero-dates-to-null
Real-World Migration Examples
E-commerce Platform Migration
Here’s a practical example of migrating an e-commerce database with complex relationships:
LOAD DATABASE
FROM mysql://ecom_user:secure_pass@mysql.internal:3306/ecommerce_prod
INTO postgresql://pg_user:pg_pass@postgres.internal:5432/ecommerce_new
WITH include drop, create tables, create indexes, reset sequences,
workers = 12, concurrency = 1,
multiple readers per thread, rows per range = 25000
-- Handle decimal precision for prices
CAST type decimal(10,2) to numeric(10,2),
type tinyint(1) to boolean using tinyint-to-boolean,
type datetime to timestamptz drop default drop not null using zero-dates-to-null
-- Pre-migration setup
BEFORE LOAD DO
$$
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
$$
-- Handle specific tables with custom logic
EXCLUDING TABLE NAMES MATCHING ~/^temp_/, ~/^backup_/
-- Custom field mapping for user table
LOAD FROM users
INTO users (
user_id, email, password_hash,
created_at, updated_at, last_login
)
WITH batch rows = 10000,
batch size = 20MB
-- Post-migration tasks
AFTER LOAD DO
$$
UPDATE products SET search_vector = to_tsvector('english', name || ' ' || description);
CREATE INDEX CONCURRENTLY idx_products_search ON products USING gin(search_vector);
$$;
Multi-Tenant Application Migration
For applications with multiple schemas or tenant separation:
-- Migrate each tenant schema separately
LOAD DATABASE
FROM mysql://app_user:password@mysql-host/tenant_001
INTO postgresql://pg_user:password@pg-host/multi_tenant_app
WITH include drop, create tables, create indexes,
workers = 8
ALTER SCHEMA 'tenant_001' RENAME TO 'tenant_001'
-- Repeat for each tenant or use shell scripting
-- for tenant in tenant_001 tenant_002 tenant_003; do
-- sed "s/TENANT_NAME/$tenant/g" migration_template.load > ${tenant}_migration.load
-- pgloader ${tenant}_migration.load
-- done
Performance Optimization and Tuning
Migration performance can vary significantly based on data volume, network latency, and hardware resources. Here are key optimization strategies:
Parameter | Default Value | Recommended for Large DBs | Impact |
---|---|---|---|
workers | 4 | 8-16 | Parallel processing threads |
rows per range | 25000 | 50000-100000 | Batch size for data transfer |
prefetch rows | 10000 | 25000-50000 | Read-ahead buffer size |
multiple readers per thread | false | true | Concurrent reading capability |
PostgreSQL-specific optimizations during migration:
SET PostgreSQL PARAMETERS
maintenance_work_mem to '2GB',
checkpoint_segments to 128,
wal_buffers to '16MB',
shared_buffers to '4GB',
effective_cache_size to '12GB'
Performance monitoring during migration:
# Monitor pgloader progress
tail -f /tmp/pgloader.log
# Check PostgreSQL activity
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE datname = 'target_database';
# Monitor table sizes during migration
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
Common Issues and Troubleshooting
Data Type Conversion Problems
MySQL and PostgreSQL handle certain data types differently. Common issues include:
Zero dates and timestamps:
-- MySQL allows '0000-00-00' dates, PostgreSQL doesn't
CAST type datetime to timestamptz
drop default drop not null using zero-dates-to-null,
-- Alternative: use a specific default date
CAST type datetime to timestamptz
using zero-dates-to-epoch
Boolean values:
-- Convert MySQL tinyint(1) to PostgreSQL boolean
CAST type tinyint(1) to boolean using tinyint-to-boolean
Text encoding issues:
-- Handle character set conversion
SET MySQL PARAMETERS
character_set_client = 'utf8mb4',
character_set_connection = 'utf8mb4'
Memory and Performance Issues
Large tables can cause memory issues. Solutions include:
-- Reduce batch sizes for memory-constrained environments
WITH workers = 4, concurrency = 1,
rows per range = 10000,
prefetch rows = 5000
-- Process large tables separately
EXCLUDING TABLE NAMES MATCHING ~/^large_table_/
-- Then process large tables individually with custom settings
Connection and Network Problems
Timeout issues are common with large migrations:
SET MySQL PARAMETERS
net_read_timeout = '600s',
net_write_timeout = '600s',
interactive_timeout = '28800s',
wait_timeout = '28800s'
SET PostgreSQL PARAMETERS
statement_timeout to '0',
lock_timeout to '0'
Foreign Key and Constraint Issues
Handle referential integrity carefully:
-- Disable foreign key checks during migration
BEFORE LOAD DO
$$
SET session_replication_role = replica;
$$
AFTER LOAD DO
$$
SET session_replication_role = DEFAULT;
-- Validate all foreign key constraints
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT constraint_name, table_name
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY'
LOOP
EXECUTE 'ALTER TABLE ' || r.table_name || ' VALIDATE CONSTRAINT ' || r.constraint_name;
END LOOP;
END
$$;
$$
Comparing pgloader with Alternative Migration Tools
Tool | Pros | Cons | Best Use Case |
---|---|---|---|
pgloader | Automatic schema conversion, parallel processing, built-in error handling | Limited customization for complex transformations | Straightforward MySQL to PostgreSQL migrations |
AWS DMS | Cloud-native, continuous replication, minimal downtime | AWS ecosystem lock-in, complex pricing | Cloud migrations with ongoing replication needs |
mysqldump + custom scripts | Full control, free, works everywhere | Manual schema conversion, time-consuming, error-prone | Small databases or highly customized migrations |
Pentaho/Talend | GUI interface, complex transformations, enterprise features | Steep learning curve, licensing costs | Enterprise environments with complex data transformations |
Best Practices and Production Considerations
Pre-Migration Checklist
- Perform a complete backup of source MySQL database
- Test migration on a subset of data first
- Document all custom functions, procedures, and triggers
- Identify and plan for application-specific SQL query changes
- Set up monitoring for both source and target databases
- Plan for downtime and coordinate with stakeholders
Security Considerations
Never expose database credentials in plain text:
# Use environment variables
export MYSQL_URL="mysql://user:pass@host/db"
export PG_URL="postgresql://user:pass@host/db"
# Then reference in pgloader config
LOAD DATABASE FROM ${MYSQL_URL} INTO ${PG_URL}
Or use PostgreSQL’s password file:
# Create ~/.pgpass file
hostname:port:database:username:password
# Set restrictive permissions
chmod 600 ~/.pgpass
Post-Migration Validation
Always validate your migration results:
-- Compare row counts
SELECT 'users' as table_name, COUNT(*) as row_count FROM users
UNION ALL
SELECT 'orders' as table_name, COUNT(*) as row_count FROM orders;
-- Validate data integrity
SELECT COUNT(*) as orphaned_orders
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- Check for null values in required fields
SELECT COUNT(*) as null_emails
FROM users
WHERE email IS NULL;
-- Verify indexes are created
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
Application Migration Considerations
Remember that migrating data is only part of the process. Applications may need updates for:
- SQL syntax differences (LIMIT vs LIMIT/OFFSET)
- Date/time handling differences
- Case sensitivity in column names
- Boolean value representation
- Auto-increment vs sequences
For more detailed information about pgloader’s capabilities and advanced configuration options, refer to the official pgloader documentation. The PostgreSQL wiki also provides excellent resources on database migration best practices.
Migration projects can be complex, but pgloader significantly simplifies the process of moving from MySQL to PostgreSQL. With proper planning, testing, and the techniques outlined in this guide, you can achieve a successful migration with minimal downtime and data integrity issues. Remember to always test thoroughly in a staging environment before proceeding with production migrations.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.
This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.