pgLoader: Database Migration Tool - AI-Optimized Reference

Core Function

pgLoader migrates data from MySQL, SQLite, Oracle, and MSSQL databases to PostgreSQL with automatic schema conversion and parallel bulk loading.

Critical Success Factors

Performance Architecture

• Uses PostgreSQL COPY protocol (not row-by-row INSERTs)
• Parallel processing: Start with 2-4 workers, increase if network/hardware supports
• Bulk loading capability: Handles millions of rows efficiently
• Memory requirement: Minimum 1GB maintenance_work_mem for large databases

Error Handling Design

• Rejection files: Bad data saved to .reject.dat and .reject.log files instead of crashing
• Transactional: Migration either completes fully or rolls back completely
• No resume capability: Failed migrations restart from beginning

Installation Reality

Recommended Approach

docker pull ghcr.io/dimitri/pgloader:latest

Why Docker wins: Package manager versions are 2-3 versions behind, source compilation requires Common Lisp dependency nightmare.

Package Manager Issues

• Ubuntu packages: Typically outdated
• Homebrew: Better but has macOS compatibility issues
• Source compilation: Common Lisp dependency hell

Configuration Requirements

Basic Migration

pgloader mysql://user:pass@source-host/dbname postgresql://user:pass@target-host/dbname

Success rate: ~60% for simple cases

Production Configuration

LOAD DATABASE
    FROM mysql://source-user:password@source-host/source-db
    INTO postgresql://target-user:password@target-host/target-db

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

SET PostgreSQL PARAMETERS
    maintenance_work_mem to '1GB',
    work_mem to '256MB'

SET MySQL PARAMETERS
    net_read_timeout  = '31536000',
    net_write_timeout = '31536000',
    mysql_charset = 'utf8mb4';

Critical Failure Modes

Memory Exhaustion

• Symptom: pgLoader consumes all available RAM
• Solution: Set maintenance_work_mem appropriately (1GB minimum for large DBs)
• Monitoring: Use pg_stat_activity

Network Timeouts

• Cause: Remote migrations timeout frequently
• Solution: Set MySQL timeout parameters to 31536000 seconds (1 year)
• Additional: Check TCP keepalive parameters

Character Encoding Corruption

• High-risk scenario: MySQL latin1 charset storing 'UTF-8' data
• Detection: Use hexdump -C yourfile.csv | head to identify encoding issues
• Prevention: Always set mysql_charset = 'utf8mb4'

Data Validation Failures

• MySQL invalid data: 0000-00-00 dates, empty strings in NOT NULL columns
• PostgreSQL rejection: Rightfully rejects invalid data
• Hidden danger: 'Successful' migrations with silently dropped data
• Validation required: Always check .reject.dat files

Performance Expectations vs Reality

Advertised vs Actual Performance

• Marketing claim: Fast bulk loading
• Reality factors: Network latency, source DB performance, data quality
• Time estimation rule: Plan for 2x estimated time minimum
• Large database example: 100GB database = 6-12 hours (not 2 hours estimated)

Resource Requirements

• RAM: 1GB+ maintenance_work_mem for large databases
• Network: Stable connection critical for remote migrations
• Storage: Additional space for reject files and temporary data
• Expertise: Technical knowledge required, not point-and-click

Migration Scope Limitations

What pgLoader Handles

• Data migration: Tables, indexes, foreign keys, sequences
• Schema conversion: Automatic type mapping
• Supported sources: MySQL, SQLite, Oracle, MSSQL, CSV, compressed archives
• Bulk operations: Efficient large-scale data transfer

What pgLoader Cannot Handle

• Business logic: Stored procedures, triggers, functions require manual conversion
• Custom data types: Database-specific extensions need manual handling
• Incremental updates: No built-in change data capture
• Resume capability: No checkpoint/restart functionality

Comparative Tool Analysis

Tool	Strength	Weakness	Best Use Case
pgLoader	Fast bulk loading, handles bad data gracefully	Config syntax, no stored procedures	MySQL/Oracle/MSSQL → PostgreSQL
pg_dump/pg_restore	Bulletproof reliability	PostgreSQL-only, no transformation	PostgreSQL → PostgreSQL exact replication
ora2pg	Oracle complexity handling	Perl-based, scattered documentation	Complex Oracle with stored procedures
AWS DMS	Enterprise support	Vendor lock-in, cost	Enterprise with deep pockets

Production Readiness Assessment

Risk Factors

• Data corruption risk: Silent data loss from unvalidated reject files
• Downtime risk: No resume capability means restart on failure
• Expertise requirement: Technical knowledge essential for troubleshooting
• Testing requirement: Extensive validation necessary before production use

Success Factors

• Continuous testing: Run migrations repeatedly during development
• Validation procedures: Row counts, checksums, application testing
• Rollback planning: Full backup and restore procedures
• Monitoring setup: PostgreSQL stats, reject file monitoring

Real-World Failure Scenarios

Case Study: Silent Data Corruption

• Scenario: 'Successful' migration with 18,000 rejected records
• Root cause: MySQL latin1 charset storing UTF-8 data
• Impact: 3 months chasing 'mysterious' application bugs
• Resolution: Database rebuild from backups
• Prevention: Mandatory reject file validation

Case Study: Performance Degradation

• Scenario: 200GB migration, estimated 4 hours, actual 11 hours
• Root cause: MySQL timeouts on large tables, 15 different character encodings
• Result: 47,000 rejected rows requiring manual cleanup
• Lesson: Plan for 2-3x estimated time, validate source data quality

Time and Resource Investment

Development Phase

• Learning curve: 1-2 days for basic competency
• Testing setup: 2-5 days for comprehensive validation
• Configuration tuning: 1-3 days depending on complexity

Migration Execution

• Small databases (<10GB): 2-6 hours including validation
• Medium databases (10-100GB): 6-24 hours
• Large databases (>100GB): Days to weeks, consider chunked approach

Post-Migration

• Validation time: 25-50% of migration time
• Issue resolution: Highly variable based on data quality
• Application testing: Plan for extensive QA cycle

Critical Success Checklist

Pre-Migration

• Source data quality assessment
• Character encoding verification
• Network timeout configuration
• PostgreSQL memory settings
• Test migration on copy database

During Migration

• Monitor reject file generation
• Track memory usage
• Monitor network stability
• Log migration progress

Post-Migration

• Validate row counts
• Check referential integrity
• Review all reject files
• Test application functionality
• Performance baseline comparison

Support Resources

Primary Documentation

• pgLoader ReadTheDocs: Official documentation with practical examples
• MySQL Casting Rules: Essential for debugging rejected data
• GitHub Issues: Real-world problem solutions

Community Knowledge

• Percona Migration Blog: Production-tested procedures
• Stack Overflow: Theoretical answers (limited practical value)
• Vendor forums: Database-specific migration experiences

Decision Criteria

Choose pgLoader When

• Migrating from MySQL/Oracle/MSSQL to PostgreSQL
• Need automatic schema conversion
• Have technical expertise available
• Budget constraints favor open source
• Can tolerate learning curve and manual troubleshooting

Choose Commercial Alternative When

• Limited technical expertise
• Critical migration with tight deadlines
• Need vendor support and SLA
• Budget allows for commercial tools
• Risk tolerance is low

Choose Manual Migration When

• Complex business logic requirements
• Unique data transformation needs
• Small dataset size
• Full control over migration process required