Skip to content

Migrating_MySQL_Postgres.md

Latest commit

 

History

History
321 lines (230 loc) · 8.89 KB

Migrating_MySQL_Postgres.md

File metadata and controls

321 lines (230 loc) · 8.89 KB

Alternative Solutions for MySQL to PostgreSQL Migration: Updated to show Alternative Approach to Migrate MySQL to Postgres

What Azure DMS Actually Supports:

  • MySQL → Azure Database for MySQL
  • PostgreSQL → Azure Database for PostgreSQL
  • SQL Server → Azure SQL Database/MI
  • MongoDB → Azure Cosmos DB

What it DOESN’T Support:

  • MySQL → PostgreSQL ❌
  • Oracle → PostgreSQL ❌
  • Any cross-engine migrations ❌

Alternative Solutions for MySQL to PostgreSQL Migration

Since Azure DMS won’t work for your use case, here are the best alternatives for zero-downtime migration:

1. AWS Database Migration Service (DMS) For a comprehensive zero-downtime migration using AWS DMS, see our detailed guide.

AWS DMS actually DOES support MySQL to PostgreSQL migrations with continuous replication:

# AWS DMS supports this migration path
Source: MySQL (on Azure) → Target: PostgreSQL (on Azure)

Use AWS DMS even when both databases are on Azure - it’s just a migration tool.

2. Third-Party Tools

pgloader - Excellent for MySQL to PostgreSQL:

# One-time migration
pgloader mysql://user:pass@mysql-host/db postgresql://user:pass@pg-host/db

# With transformation rules
pgloader --verbose --debug mysql://source postgresql://target

Debezium + Kafka for real-time replication:

# Debezium MySQL Source Connector
{
  "connector.class": "io.debezium.connector.mysql.MySqlConnector",
  "database.hostname": "your-mysql-host",
  "transforms": "unwrap",
  "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState"
}

3. Manual Logical Replication Setup

Phase 1: Schema Migration

# Export MySQL schema and convert to PostgreSQL
mysqldump --no-data --routines --triggers source_db > mysql_schema.sql
# Use conversion tools or manual conversion

Phase 2: Initial Data Load

# Use pgloader or custom ETL
pgloader --with "prefetch rows = 10000" \
         --with "batch size = 25MB" \
         mysql://source postgresql://target

Phase 3: Change Data Capture

  • Set up binlog reading from MySQL
  • Stream changes to PostgreSQL via custom application
  • Use tools like Maxwell’s daemon or Debezium

4. Application-Level Dual Write

During migration period:

def write_data(data):
    # Write to both databases during transition
    mysql_connection.execute(mysql_query, data)
    postgresql_connection.execute(pg_query, converted_data)
    
    # Validate consistency
    if validate_enabled:
        validate_data_consistency(data)

Recommended Approach for Zero Downtime

Option 1: AWS DMS (Easiest)

  1. Set up AWS DMS instance
  2. Configure MySQL (Azure) as source
  3. Configure PostgreSQL (Azure) as target
  4. Run continuous replication
  5. Perform cutover when synchronized

Option 2: Debezium + Custom Pipeline

  1. Deploy Kafka cluster
  2. Set up Debezium MySQL connector
  3. Create transformation logic for MySQL→PostgreSQL data types
  4. Stream to PostgreSQL via Kafka Connect PostgreSQL sink
  5. Monitor lag and perform cutover

Would you like me to provide detailed implementation steps for either of these approaches? AWS DMS would be the quickest solution since it handles the cross-engine conversion automatically.

UDATE: Migrating from MySQL to PostgreSQL (not supported)?

On Azure without downtime requires a carefully planned approach using logical replication and synchronized cutover strategies:

Azure Database Migration Service (DMS) Approach

Azure DMS is the recommended tool for this migration as it supports online (minimal downtime) migrations:

  1. Setup Azure DMS
  • Create an Azure Database Migration Service instance
  • Configure network connectivity between source MySQL and target PostgreSQL
  • Ensure proper firewall rules and VNet peering if needed
  1. Configure Continuous Sync
  • Enable binary logging on MySQL source
  • Create migration project in DMS with “Online migration” selected
  • DMS will perform initial data load and then continuously sync changes

Alternative Logical Replication Strategy

If Azure DMS doesn’t meet your needs, use a custom logical replication approach:

Phase 1: Initial Setup

-- On MySQL source
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_row_image = 'FULL';

-- Create replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

Phase 2: Schema Migration

  • Use tools like pgloader or custom scripts to convert schema
  • Handle data type differences (MySQL → PostgreSQL)
  • Migrate indexes, constraints, and triggers separately

Phase 3: Data Synchronization Tools

  • AWS DMS (works with Azure): Can replicate from MySQL to PostgreSQL
  • Debezium + Kafka: Stream changes from MySQL binlog to PostgreSQL
  • Custom ETL pipeline: Using tools like Apache Airflow

Step-by-Step Migration Process

1. Pre-Migration Preparation

# Schema assessment and conversion
mysqldump --no-data --routines --triggers source_db > schema.sql
# Convert MySQL schema to PostgreSQL format

2. Setup Target PostgreSQL

  • Create Azure Database for PostgreSQL
  • Configure performance tier matching source workload
  • Setup monitoring and backup policies

3. Initial Data Load

# Using pgloader for initial migration
pgloader mysql://user:pass@mysql-host/db postgresql://user:pass@pg-host/db

4. Continuous Replication Setup

Use Debezium for change data capture:

# Debezium connector configuration
{
  "name": "mysql-postgres-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "mysql-host",
    "database.port": "3306",
    "database.user": "repl_user",
    "database.password": "password",
    "database.server.id": "12345",
    "database.server.name": "mysql-server",
    "database.include.list": "your_database",
    "transforms": "route",
    "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
    "transforms.route.regex": "([^.]+)\\.([^.]+)\\.([^.]+)",
    "transforms.route.replacement": "$3"
  }
}

Application Migration Strategy

1. Dual-Write Pattern

  • Modify application to write to both MySQL and PostgreSQL
  • Read from MySQL initially
  • Validate data consistency between databases

2. Feature Flags

# Application code example
if feature_flag_enabled('use_postgresql'):
    result = postgresql_query(sql)
else:
    result = mysql_query(sql)

3. Database Abstraction Layer

  • Use ORM or database abstraction layer
  • Switch database connections via configuration
  • Handle SQL dialect differences

Testing and Validation

1. Data Validation

-- Compare row counts
SELECT COUNT(*) FROM mysql_table;
SELECT COUNT(*) FROM postgresql_table;

-- Checksum validation
SELECT MD5(CONCAT_WS('|', col1, col2, col3)) FROM mysql_table ORDER BY id;
SELECT MD5(string_agg(col1||'|'||col2||'|'||col3, '' ORDER BY id)) FROM postgresql_table;

2. Performance Testing

  • Run load tests against PostgreSQL
  • Compare query performance
  • Validate connection pooling and caching

Cutover Process

1. Synchronized Cutover

# 1. Stop application writes
# 2. Wait for replication lag to reach zero
# 3. Perform final data validation
# 4. Switch application configuration
# 5. Start application with PostgreSQL

2. Rollback Plan

  • Keep MySQL running for 24-48 hours
  • Monitor application metrics
  • Have immediate rollback procedure ready

Azure-Specific Considerations

1. Network Configuration

  • Use Azure Private Link for secure connectivity
  • Configure VNet integration for DMS
  • Setup NSG rules for database access

2. Monitoring and Alerting

# Azure CLI monitoring setup
az monitor metrics alert create \
  --name "PostgreSQL-CPU-Alert" \
  --resource-group myResourceGroup \
  --scopes "/subscriptions/.../resourceGroups/.../providers/Microsoft.DBforPostgreSQL/servers/myserver" \
  --condition "avg Percentage CPU > 80"

3. Backup Strategy

  • Configure automated backups for PostgreSQL
  • Test point-in-time recovery
  • Document backup retention policies

Common Challenges and Solutions

1. Data Type Mapping

-- MySQL to PostgreSQL mappings
TINYINT(1) → BOOLEAN
DATETIME → TIMESTAMP
ENUM → VARCHAR with CHECK constraint
AUTO_INCREMENT → SERIAL or IDENTITY

2. SQL Dialect Differences

  • Replace MySQL-specific functions
  • Handle LIMIT/OFFSET differences
  • Update stored procedures and triggers

3. Character Set Issues

  • Ensure UTF-8 encoding consistency
  • Handle collation differences
  • Test special characters thoroughly

This approach minimizes downtime to minutes during the final cutover while ensuring data consistency and providing rollback capabilities. The key is thorough testing and having multiple validation checkpoints throughout the process.​​​​​​​​​​​​​​​​