Initial Load + CDC walkthrough

This page shows a complete example of a CDC stream with initial snapshot enabled. The same schema is exercised in both directions so you can see what each engine produces at the source-log boundary and how live changes land on the target after bootstrap.

For the conceptual overview and configuration reference, read Initial Load + CDC first.

What you will build

Two related tables on the source side:

• categories — 10 rows, used as a parent table
• products — 1,000 rows, each referencing a category

The stream bootstraps all 1,010 rows, then a small set of live INSERT / UPDATE / DELETE operations proves that CDC picks up from the handoff position and applies later changes to the target. You will also stop the stream, write more source rows while it is offline, start it again, and verify that the target catches up from the saved CDC checkpoint without re-running the snapshot.

Prerequisites

• Both source and target connections are configured in DBConvert Streams
• Source database is MySQL (ROW binlog format, binlog_row_image=FULL) or PostgreSQL (wal_level=logical, at least one free replication slot)
• Target tables are empty before the first data copy. For database targets, either let DBConvert Streams create missing tables with Create missing only or pre-create compatible empty tables with Validate existing.
• Source user has CDC privileges (PostgreSQL replication role, or MySQL REPLICATION SLAVE/REPLICATION CLIENT)

Direction 1: PostgreSQL to MySQL

Create and populate source tables

On the PostgreSQL source:

CREATE TABLE public.categories (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE public.products (
    id INT PRIMARY KEY,
    category_id INT NOT NULL REFERENCES public.categories(id),
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50),
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO public.categories (id, name)
SELECT i, 'Category_' || i FROM generate_series(1, 10) AS i;

INSERT INTO public.products (id, category_id, name, sku, price, stock)
SELECT i, ((i - 1) % 10) + 1,
       'Product_' || i,
       'SKU-' || LPAD(i::text, 6, '0'),
       ROUND((random() * 999 + 1)::numeric, 2),
       (random() * 500)::int
FROM generate_series(1, 1000) AS i;

Prepare target structure

For a new empty target, use Create structure with Schema policy: Create missing only. DBConvert Streams creates the target tables before the snapshot copy, then verifies they are empty.

If you use Validate existing instead, pre-create compatible empty tables on the MySQL target. Match the source column types and primary keys. Do not add ON UPDATE CURRENT_TIMESTAMP to datetime columns — it will rewrite the timestamps CDC is trying to replicate.

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50),
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME,
    INDEX idx_category (category_id)
);

FK constraints on the target are not required and can cause failures if bootstrap copies rows in an unexpected order. Keep them off on the target.

Create the stream configuration

{
  "name": "pg-to-mysql-with-snapshot",
  "mode": "cdc",
  "source": {
    "connections": [
      {
        "connectionId": "conn_pg_source",
        "database": "postgres",
        "schema": "public",
        "tables": [
          { "name": "categories" },
          { "name": "products" }
        ]
      }
    ],
    "options": {
      "dataBundleSize": 100,
      "replicationSlot": "example_slot",
      "publicationName": "example_pub",
      "initialSnapshot": {
        "enabled": true,
        "restartPolicy": "fail_until_reset"
      },
      "operations": ["insert", "update", "delete"]
    }
  },
  "target": {
    "id": "conn_mysql_target",
    "spec": {
      "db": {
        "database": "target",
        "schemaPolicy": "create_missing_only",
        "writeMode": "upsert"
      }
    }
  }
}

Start the stream and read the monitor

Click Start on the stream config. The Monitor tab shows a CDC Continuity card for the snapshot-to-CDC boundary:

• Snapshot: copying -> handoff -> complete
• Snapshot Ended At: the source-log handoff position, for example PostgreSQL LSN 97/CC93E408
• CDC Apply: the latest acknowledged CDC position
• Status: Healthy after the writer acknowledges CDC events
• Footer: PostgreSQL LSN · Slot example_slot · Retry-safe apply · Ordered per table · Last update ...

With 1,000 products plus 10 categories, bootstrap typically completes in a few seconds on a local test setup.

Verify the bootstrap

On MySQL target:

SELECT (SELECT COUNT(*) FROM categories) AS cats,
       (SELECT COUNT(*) FROM products)   AS prods;
-- cats=10, prods=1000

Run live CDC operations

Back on the PostgreSQL source:

-- new category + three new products referencing it
INSERT INTO public.categories (id, name) VALUES (11, 'Category_LIVE_11');
INSERT INTO public.products (id, category_id, name, sku, price, stock) VALUES
    (1001, 11, 'LIVE_Product_1001', 'SKU-001001', 19.99, 100),
    (1002, 11, 'LIVE_Product_1002', 'SKU-001002', 29.99,  50),
    (1003, 11, 'LIVE_Product_1003', 'SKU-001003', 39.99,  25);

-- update existing rows
UPDATE public.products SET price = 999.99, updated_at = now() WHERE id IN (1, 2, 3);
UPDATE public.categories SET name = 'Category_1_UPDATED' WHERE id = 1;

-- delete a row
DELETE FROM public.products WHERE id = 1002;

Within seconds the Monitor shows Produced and Consumed growing and Acked Seq advancing. On the MySQL target:

SELECT id, name FROM categories WHERE id IN (1, 11);
-- 1  Category_1_UPDATED
-- 11 Category_LIVE_11

SELECT id, name, price FROM products WHERE id IN (1, 2, 3, 1001, 1002, 1003);
-- 1     Product_1           999.99
-- 2     Product_2           999.99
-- 3     Product_3           999.99
-- 1001  LIVE_Product_1001    19.99
-- 1003  LIVE_Product_1003    39.99
-- (1002 is gone — it was deleted)

CDC apply is ordered per table, not globally across all tables. Keep target-side FK constraints off or deferred for replication workloads so a child-table event cannot fail simply because the related parent-table event is still in another target worker.

Direction 2: MySQL to PostgreSQL

The flow is identical — only the engine, source log type, and target setup change.

Create and populate source tables (MySQL)

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50),
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    CONSTRAINT fk_cat FOREIGN KEY (category_id) REFERENCES categories(id)
);

INSERT INTO categories (id, name)
SELECT n, CONCAT('Category_', n) FROM (
    SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS t;

SET SESSION cte_max_recursion_depth = 1100;
INSERT INTO products (id, category_id, name, sku, price, stock)
WITH RECURSIVE seq(n) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT n, ((n - 1) % 10) + 1,
       CONCAT('Product_', n),
       CONCAT('SKU-', LPAD(n, 6, '0')),
       ROUND(RAND() * 999 + 1, 2),
       FLOOR(RAND() * 500)
FROM seq;

Prepare target structure (PostgreSQL)

For a new empty target, use the same Create structure and Schema policy: Create missing only setup. If you use Validate existing, pre-create compatible empty PostgreSQL tables:

CREATE TABLE public.categories (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP
);

CREATE TABLE public.products (
    id INT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50),
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE INDEX idx_products_category ON public.products(category_id);

Stream configuration

Same shape as direction 1. Keep target.spec.db.schemaPolicy set to create_missing_only for a new empty target, or switch to validate_existing if you pre-created the target tables. The CDC Continuity footer starts with MySQL binlog, and Snapshot Ended At shows a binlog-style handoff like mysql-bin.000176:79269.

Run live CDC operations

On the MySQL source:

INSERT INTO categories (id, name)
VALUES (11, 'Category_LIVE_11');

INSERT INTO products (id, category_id, name, sku, price, stock)
VALUES
    (1001, 11, 'LIVE_Product_1001', 'SKU-001001', 19.99, 100),
    (1002, 11, 'LIVE_Product_1002', 'SKU-001002', 29.99,  50),
    (1003, 11, 'LIVE_Product_1003', 'SKU-001003', 39.99,  25);

UPDATE products
SET price = 999.99,
    updated_at = NOW()
WHERE id IN (1, 2, 3);

UPDATE categories
SET name = 'Category_1_UPDATED'
WHERE id = 1;

DELETE FROM products WHERE id = 1002;

After CDC applies those changes, the PostgreSQL target reconciles to cats=11, prods=1002 with all updates and the delete applied:

SELECT (SELECT COUNT(*) FROM public.categories) AS cats,
       (SELECT COUNT(*) FROM public.products)   AS prods;
-- cats=11, prods=1002

SELECT id, name FROM public.categories WHERE id IN (1, 11);
-- 1  Category_1_UPDATED
-- 11 Category_LIVE_11

SELECT id, name, price FROM public.products WHERE id IN (1, 2, 3, 1001, 1002, 1003);
-- 1     Product_1           999.99
-- 2     Product_2           999.99
-- 3     Product_3           999.99
-- 1001  LIVE_Product_1001    19.99
-- 1003  LIVE_Product_1003    39.99
-- (1002 is gone because it was deleted)

Verify offline catch-up after restart

This exercise uses direction 2, where MySQL is the source and PostgreSQL is the target. The same behavior applies in the opposite direction as long as the source log still contains the stopped interval.

1. Stop the stream from the Monitor tab.
2. While the stream is stopped, run this on the MySQL source:

INSERT INTO categories (id, name)
VALUES (12, 'Category_OFFLINE_12');

INSERT INTO products (id, category_id, name, sku, price, stock)
VALUES
    (1004, 12, 'OFFLINE_Product_1004', 'SKU-001004', 49.99, 77),
    (1005, 12, 'OFFLINE_Product_1005', 'SKU-001005', 69.99, 55);

UPDATE products
SET price = 55.55,
    stock = 188,
    updated_at = NOW()
WHERE id = 1003;

3. Before restarting the stream, check the PostgreSQL target. It should still reflect the pre-stop state:

SELECT (SELECT COUNT(*) FROM public.categories) AS cats,
       (SELECT COUNT(*) FROM public.products)   AS prods;
-- cats=11, prods=1002

SELECT id, name FROM public.categories WHERE id = 12;
-- no rows

SELECT id, name, price, stock
FROM public.products
WHERE id IN (1003, 1004, 1005)
ORDER BY id;
-- 1003 is still the old live row
-- 1004 and 1005 are not present yet

4. Start the stream again.
5. After CDC catches up, run the same checks on the PostgreSQL target:

SELECT (SELECT COUNT(*) FROM public.categories) AS cats,
       (SELECT COUNT(*) FROM public.products)   AS prods;
-- cats=12, prods=1004

SELECT id, name FROM public.categories WHERE id = 12;
-- 12  Category_OFFLINE_12

SELECT id, category_id, name, price, stock
FROM public.products
WHERE id IN (1003, 1004, 1005)
ORDER BY id;
-- 1003  ... 55.55 188
-- 1004  12  OFFLINE_Product_1004 49.99 77
-- 1005  12  OFFLINE_Product_1005 69.99 55

The restart resumes from the saved CDC checkpoint. The initial snapshot is not repeated, and the target receives only the source changes that occurred after the last acknowledged checkpoint.

What each card actually shows

After bootstrap reaches complete, the snapshot state keeps showing the handoff position. This is expected: the stored bootstrap state is frozen at handoff time, and restarts resume CDC from the saved checkpoint instead of re-running the snapshot.

Stop and start behavior

After bootstrap is complete:

• Stopping the stream and starting it again resumes CDC from the saved checkpoint. The initial snapshot is not re-run.
• Source changes that happened while the stream was stopped are applied as long as the source log still contains that range.
• If you wipe the target manually after bootstrap, the stream will not re-seed it — only future CDC events are applied. The current fail_until_reset restart policy deliberately leaves repair to the operator. To re-seed, reset the same stream config and then start it again as a fresh first run.

Common pitfalls

• "missing target tables" on Start — the stream is using Validate existing, but target tables do not exist or their names do not match. Create them before starting, or use Create missing only for a new empty target.
• "all replication slots are in use" on Start (PostgreSQL) — max_replication_slots is full. Check Database Overview or the CDC wizard for slot usage such as 3 / 5 · 0 free, then drop unused slots from prior test runs with SELECT pg_drop_replication_slot('name') or raise the limit.
• Interrupted before bootstrap completes — partial bootstrap resume depends on the source and routing path. MySQL tables that routed to the resumable chunked path can continue from saved progress when the same config starts again. PostgreSQL bootstrap and MySQL plain-path tables still require reset before a fresh rerun.
• Schema "type diff" warning on the Compare tab — cross-engine numeric or timestamp types rarely match byte-for-byte. This is informational and does not affect CDC correctness.
• Rows out of order across tables — CDC is ordered per table, not globally across all tables. FK constraints on the target side can reject rows if the parent row is still in flight. Keep referential integrity enforced on the source; leave target-side FK constraints off or deferred for replication workloads.

Related docs

• Initial Load + CDC
• CDC Reliability and Resume
• Stream Configuration Reference
• CDC Mode