Zero-Downtime MariaDB PostgreSQL Migration: Why the Usual Tools Fell Short, and How Debezium CDC Solved It

Sooner or later, every team that grew up on MySQL/MariaDB faces the question of moving to PostgreSQL: licensing, the extension ecosystem, more predictable behavior under concurrent load - there are plenty of reasons. The hard part is different: how do you move a production database that never stops, without hours of downtime and without risking data written during the cutover?

This post covers how we solved that for an e-commerce backend running on MariaDB, why the usual one-shot conversion tools don't work for a "live" migration, and what a working setup looks like on Debezium + Kafka Connect, including an Ansible role for a repeatable run.

All hostnames, database names, topics, and credentials in the examples are placeholders.

Why the off-the-shelf tools didn't work

The first instinct when you hear "MySQL to PostgreSQL migration" is to grab one of the well-known converters and run a dump through it. We tried a few, and they all shared the same fundamental limitation: they're one-shot transfer tools, not replication. They take a snapshot at the moment they run and have no way to catch up on changes written to the source afterward. For a database that never stops, that means a downtime window for the transfer - not acceptable for us.

On top of that, each tool had its own specific problems:

• pgloader - the most popular option, but its issue tracker regularly shows heap exhaustion on large tables, parser errors (ESRAP-PARSE-ERROR), trouble with MySQL "zero dates," naming conflicts once identifiers exceed PostgreSQL's 63-character limit, and duplicate index names that MySQL allows implicitly but PostgreSQL doesn't. On several of our tables, the migration simply hung partway through.
• pg_chameleon - closer to what we needed (actual replication by reading the binlog), but it requires binlog_format=ROW, a mandatory primary key on every table, and when a row fails to load it just drops the conflicting table from replication - meaning part of your data silently stops syncing, and that's easy to miss. Its "PostgreSQL → MySQL" direction is also experimental and heavily limited, so realistically only one-way migration is viable.
• py-mysql2pgsql - effectively an abandoned project: no releases in years, maintenance inactive, not something we considered for a production workload.

None of these tools gave us what we actually needed: continuous synchronization between source and target, so we could migrate the bulk of the data ahead of time, let tables "catch up," and cut the application over from MariaDB to PostgreSQL with a gap measured in seconds.

The fix: Debezium as a CDC platform

Debezium is a set of Kafka Connect connectors implementing Change Data Capture (CDC). Unlike one-shot converters, Debezium:

1. takes a consistent snapshot of the current data (snapshot.mode: initial);
2. then reads the MariaDB binlog row by row and streams every change (insert/update/delete) into Kafka;
3. on the other end, a sink connector consumes the Kafka stream and applies the changes to PostgreSQL via upsert.

The result is a PostgreSQL replica that continuously "catches up" to the source, letting you cut the application over whenever the replication lag is effectively zero - with no downtime window on MariaDB during the transfer itself.

The cost is infrastructure complexity (you need Kafka with ZooKeeper, Kafka Connect, and disk for the queue) and the fact that all changes are temporarily materialized in Kafka as JSON - in our test run, the transfer used roughly 50 GB of disk space purely because of that format. It's best to run the stack on a separate machine rather than on the production database server.

Component versions

Component	Version
MariaDB	v11.7.2
PostgreSQL	v15.12 (Debian 15.12-0+deb12u2)
Debezium ZooKeeper	quay.io/debezium/zookeeper:3.1.1.Final
Debezium Kafka	quay.io/debezium/kafka:3.1.1.Final
Debezium Connect	quay.io/debezium/connect:3.1.1.Final

Below is an example of moving data from db-source-01 (MariaDB) to db-target-01 (PostgreSQL).

Step 1. Infrastructure: ZooKeeper, Kafka, Kafka Connect

docker network create debezium-net

docker run -d --name zookeeper \
  --network debezium-net \
  -p 2181:2181 -p 2888:2888 -p 3888:3888 \
  quay.io/debezium/zookeeper:3.1.1.Final

docker run -d --name kafka \
  --network debezium-net \
  -p 9092:9092 \
  -e KAFKA_LISTENERS=PLAINTEXT://0.0.0.0:9092 \
  -e KAFKA_ADVERTISED_LISTENERS=PLAINTEXT://kafka:9092 \
  -e ZOOKEEPER_CONNECT=zookeeper:2181 \
  -e KAFKA_AUTO_CREATE_TOPICS_ENABLE=true \
  quay.io/debezium/kafka:3.1.1.Final

docker run -d --name connect \
  --network debezium-net \
  -p 8083:8083 \
  -e BOOTSTRAP_SERVERS=kafka:9092 \
  -e GROUP_ID=1 \
  -e CONFIG_STORAGE_TOPIC=my_connect_configs \
  -e OFFSET_STORAGE_TOPIC=my_connect_offsets \
  -e STATUS_STORAGE_TOPIC=my_connect_statuses \
  quay.io/debezium/connect:3.1.1.Final

docker exec -it kafka /kafka/bin/kafka-topics.sh \
  --bootstrap-server kafka:9092 --create \
  --topic schemachanges-example \
  --partitions 1 --replication-factor 1

The dedicated schemachanges-example topic is where Debezium stores the source schema-change history - the source connector won't start without it.

Step 2. Source connector (MariaDB)

curl -i -X POST -H "Content-Type:application/json" http://localhost:8083/connectors/ -d '{
  "name": "mariadb-connector",
  "config": {
    "connector.class": "io.debezium.connector.mariadb.MariaDbConnector",
    "database.hostname": "db-source-01.example.int",
    "database.port": "3306",
    "database.user": "debezium",
    "database.password": "<MARIADB_PASSWORD>",
    "database.server.id": "1",
    "database.include.list": "example_shop",
    "database.connectionTimeZone": "America/New_York",
    "topic.prefix": "db-source-01-example-int",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schemachanges-example",
    "include.schema.changes": "true",
    "snapshot.mode": "initial",
    "transforms": "unwrap",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.unwrap.delete.handling.mode": "rewrite",
    "transforms.unwrap.drop.tombstones": "false",
    "max.batch.size": "100",
    "max.queue.size": "500"
  }
}'

curl -s http://localhost:8083/connectors/mariadb-connector/status | jq

Both connector.state and every entry in tasks[].state should read RUNNING.

Step 3. Sink connector (PostgreSQL)

curl -i -X POST -H "Content-Type:application/json" http://localhost:8083/connectors/ -d '{
  "name": "postgres-sink-connector",
  "config": {
    "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",
    "tasks.max": "1",
    "topics.regex": "db-source-01-example-int\\.example_shop\\.(?!(device_fingerprints|viewed_products|migration_versions|region_zone|store_cell)$).*",
    "connection.url": "jdbc:postgresql://db-target-01:5432/example_shop?sslmode=disable",
    "connection.username": "postgres",
    "connection.password": "<POSTGRES_PASSWORD>",
    "insert.mode": "upsert",
    "primary.key.mode": "record_key",
    "primary.key.fields": "id",
    "auto.create": "true",
    "auto.evolve": "true",
    "delete.enabled": "true",
    "quote.identifiers": "true",
    "schema.evolution": "basic",
    "transforms": "unwrap,route",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
    "transforms.route.regex": "db-source-01-example-int\\.example_shop\\.(.*)",
    "transforms.route.replacement": "$1"
  }
}'

curl -s http://localhost:8083/connectors/postgres-sink-connector/status | jq

The problem of tables without an id key

Debezium expects a record's primary key to also be the Kafka message key - by default, the id column. But real-world schemas almost always have a dozen tables with a composite or non-standard unique key: many-to-many join tables, lookup tables keyed by code or number, and so on. The regex filter on the general sink connector above explicitly excludes them - otherwise Debezium would try to write against a nonexistent id and the upsert would fail.

In our case there were around 15 such tables: for example email_blocklist (keyed on email), store_zone (store_id, zone_id), regions (number), and similar join/lookup tables.

Important. Add such a table to its own connector and to the general connector's exclude list (the (?!(...)$) part of topics.regex from Step 3) - otherwise both connectors pick it up and the general one fails into FAILED.

Each of these gets its own sink connector with its own primary.key.fields:

#!/bin/bash

tables=(
  '{"table": "email_blocklist", "keys": "email"}'
  '{"table": "store_zone", "keys": "store_id,zone_id"}'
  '{"table": "regions", "keys": "number"}'
  # ... the rest of the non-standard-key tables
)

for t in "${tables[@]}"; do
  table=$(echo "$t" | jq -r '.table')
  keys=$(echo "$t" | jq -r '.keys')
  connector_name="postgres-sink-${table}-connector"
  topics_regex="db-source-01-example-int\\.example_shop\\.${table}$"

  config=$(cat <<EOF
{
  "name": "${connector_name}",
  "config": {
    "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",
    "tasks.max": "1",
    "topics.regex": "${topics_regex}",
    "connection.url": "jdbc:postgresql://db-target-01:5432/example_shop?sslmode=disable",
    "connection.username": "postgres",
    "connection.password": "<POSTGRES_PASSWORD>",
    "insert.mode": "upsert",
    "primary.key.mode": "record_key",
    "primary.key.fields": "${keys}",
    "auto.create": "true",
    "auto.evolve": "true",
    "delete.enabled": "true",
    "quote.identifiers": "true",
    "schema.evolution": "basic",
    "transforms": "unwrap,route",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
    "transforms.route.regex": "db-source-01-example-int\\.example_shop\\.(.*)",
    "transforms.route.replacement": "\$1"
  }
}
EOF
)

  curl -i -X POST -H "Content-Type: application/json" \
    http://localhost:8083/connectors/ -d "${config}"
done

Monitoring connector status

The simplest way to catch a connector before it silently drops into FAILED is to poll /status in a loop across all connectors (the general one and the per-table ones) and check both the connector state and every task's state:

while true; do
  error_found=false
  for connector in "${all_connectors[@]}"; do
    result=$(curl -s "http://localhost:8083/connectors/${connector}/status")
    [ -z "$result" ] && { echo "ERROR: no response from ${connector}"; error_found=true; continue; }

    state=$(echo "$result" | jq -r '.connector.state')
    [ "$state" != "RUNNING" ] && { echo "ERROR: ${connector} -> ${state}"; error_found=true; }

    for task_state in $(echo "$result" | jq -r '.tasks[].state'); do
      [ "$task_state" != "RUNNING" ] && { echo "ERROR: task ${connector} -> ${task_state}"; error_found=true; }
    done
  done
  sleep 5
done

In practice, a connector most often lands in FAILED because of a type mismatch (MySQL's zerodate, for instance) or a lost DB connection - both show up immediately in this loop without having to dig through Connect's logs.

Running it in production: an Ansible role

Manual curl calls are fine for debugging, but awkward for a production run: it's easy to miss a step, mistype a regex, or not notice a connector went to FAILED. So we wrapped the whole process in an Ansible role, which gives you three things a pile of bash scripts doesn't:

• One command for the whole cycle. ansible-playbook migrate.yml --tags debezium_up brings up the network, containers, topic, and every connector (the general one plus one per non-standard-key table) in a single run, instead of manually looping curl over a table list.
• Idempotency and safe re-runs. The role can be re-run safely: the uri module handles already-existing connectors gracefully (status_code: [201, 409]) instead of failing on a second run.
• Portability across environments. Hosts, credentials, the table list and their keys, exclusions - all of it lives in defaults/main.yml variables. Adapting the role to a new pair of databases means editing one file, not the code.

Separate tags cover the whole lifecycle: debezium_up (bring up and configure), debezium_monitor (poll every connector's status and print a summary), and debezium_down (tear down connectors, containers, and the network once the migration is done).

Here's what the variables for a specific migration look like:

# roles/database/migration/defaults/main.yml
source_db_host: source-db.example.internal
sink_db_host: sink-db.example.internal

tables:
  - table: "orders"
    keys: "id"
  - table: "regions"
    keys: "number"
  # ...

The role itself (the tasks, request templates, the monitoring loop) is really a matter of fitting it into your team's own Ansible project - what matters is the idea: wrap the sequence of curl calls into a declarative, idempotent, parameterized structure you can run and reuse with a single command.

This is also the natural place to close the gap from the previous section: instead of maintaining excluded_tables as a separate hand-written list, derive it from tables (for example, via map(attribute='table') in the Jinja template that builds the regex). That way the list of non-standard-key tables and the general connector's exclude list can't physically drift apart.

Things to watch for when you do this yourself

• Disk space. Kafka stores every change as JSON, and the volume grows fast - plan for headroom well beyond the size of the database itself. Our test run used about 50 GB on a database that wasn't even particularly large.
• A separate machine for the Debezium stack. Don't run Kafka/Connect on the production MariaDB server - the initial snapshot alone adds meaningful load to the source.
• Tables without id. Walk through the schema ahead of time and explicitly list every table with a composite or non-standard key - otherwise the general sink connector will either fail to create them in PostgreSQL or write to them incorrectly.
• Time zone. Set database.connectionTimeZone on the source connector explicitly to match the MariaDB server's time zone - otherwise timestamp fields will drift during the transfer.
• schema.evolution: basic. Good enough for adding new columns on the fly, but not for more complex schema changes (renames, type changes) - apply those manually before cutover.
• Cutting over the application. Only disconnect the app from MariaDB and point it at PostgreSQL once the replication lag (the gap between the latest binlog event and the latest event applied in Postgres) is effectively zero - otherwise data written in the final seconds risks being lost.

Takeaway

Off-the-shelf converters like pgloader or pg_chameleon work fine for a one-time transfer of a static dump, but poorly for migrating a live, constantly-written database: either there's no catch-up replication, or the tool silently drops problem tables from sync. Debezium + Kafka Connect solves exactly this problem: a snapshot plus a continuous stream of binlog changes, which lets you migrate the bulk of the data ahead of time and cut the application over with a sync gap measured in seconds rather than hours - at the cost of more infrastructure and a noticeable amount of disk used for staging in Kafka.