Search docs.molo17.com
    Operational guide Pipelines

    Start a snapshot and CDC replication from the Control Plane

    A new entity does nothing until you start it. This guide shows every way the Control Plane offers to start replication: a snapshot followed by CDC, CDC only, and a one-time snapshot without CDC. It also shows how to pause an entity, resume it, restart it from zero, and read the status of each entity. Every result is proved with SQL on the target.

    In this guide

    You will learn how to start one entity or several entities with an initial snapshot and then CDC, tell when the snapshot has finished and CDC has taken over, prove CDC with an insert, an update and a delete, pause and resume an entity, start CDC without a snapshot, run a one-time snapshot, and restart an entity from zero.

    Quick information

    Goal

    Start, pause, resume and restart replication for one or more entities and verify the data on the target

    Audience

    Gluesync users and administrators

    Level

    Intermediate

    Time required

    About 40 minutes

    Area

    Control Plane → Data pipelines → entity list

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You can sign in to the Gluesync Control Plane with a role that can start and stop replication;

    2. A pipeline with a source and a target connector and at least one entity exists. This guide uses a pipeline named Shop orders with two entities, customers and orders, that have never been started;

    3. The source tables hold the data to replicate. In the example, shop.customers has 200 rows and shop.orders has 1000 rows, and the target tables are empty;

    4. You can run SQL on the source and on the target database, for example with your own SQL client.

    The SQL in this guide uses the example tables. The statements run on the source (to change data) or on the target (to check data), as each step says. If your account cannot start entities, ask a Gluesync administrator to review your role.

    Guide steps

    Open the pipeline and check that the entities are on hold

    UI

    1. In the left menu, select Data pipelines;

    2. In the pipeline list, select Shop orders;

    3. Read the entity table: an entity that has never run is shown in a Hold group with a yellow pause icon.

    Entity table of the Shop orders pipeline with the entities customers and orders in the Hold group
    Figure 1. Both entities are on hold. Annotation 1 is Start sync, 2 is Run one-time snapshot, 3 is the status icon.
    Expected result

    The Hold (2) group lists customers and orders. The header of the pipeline reads 0 out of 2 operational because nothing is running yet.

    Before you start anything, check that the target is empty:

    -- run on the target
SELECT (SELECT count(*) FROM shop.customers) AS customers,
       (SELECT count(*) FROM shop.orders)    AS orders;
    Expected result

    Both counts are 0.

    Start one entity with a snapshot and then CDC

    UI

    1. In the customers row, select Start sync (the play icon);

    2. In Start from, keep Full copy. On an entity that has never run, Where it stopped is not available ("No checkpoint yet") and Full copy is preselected with the tag Recommended for the first start;

    3. In Data already in the target, keep Keep and merge;

    4. Leave Speed on Standard and read the What will happen panel on the right: it lists the steps in order (copy all rows, then follow live changes);

    5. Select Start entity.

    Sync changes (CDC) dialog with the Start from tiles marked 1
    Figure 2. Choose how to start the entity, then select Start entity.
    Expected result

    The dialog closes and customers moves to the Snapshot group while the copy runs, then to the Active group when it finishes. A table of 200 rows takes a few seconds.

    Save without starting stores the settings you chose in the dialog and leaves the entity on hold. Use it to prepare an entity and start it later.

    Check the target:

    -- run on the target
SELECT count(*) FROM shop.customers;
    Expected result

    The count is 200, the same as the source.

    Start several entities at once

    UI

    1. Select the checkbox of every entity to start, here only orders. The checkbox in the table header selects all entities;

    2. In the blue bar that appears (for example 1 item selected), select Start CDC;

    3. In the dialog, keep Full copy and Keep and merge, then select Start entity.

    Entity table with two entities selected
    Figure 3. Select the entities (1), then select Start CDC (2). The screenshot shows both entities selected, which is how you start all of them with one action.

    The dialog opens once for all the selected entities. When more than one entity is selected, the Settings for list at the bottom of the What will happen panel switches between them, because each entity keeps its own Speed settings.

    Two more buttons start every entity of a section at once. In the Hold section header, Start Section CDC opens the same dialog for all the entities of the section and Section Snapshot opens the one-time snapshot dialog for all of them (see Run a one-time snapshot without CDC). Pause Section appears in the header of a section whose entities are running.
    Expected result

    Every selected entity leaves the Hold group. The entities of one start action run the same Start from choice.

    Check the target:

    -- run on the target
SELECT count(*) FROM shop.orders;
    Expected result

    The count is 1000, the same as the source.

    Tell when the snapshot has finished and CDC has taken over

    UI

    The snapshot of the example tables ends in seconds. On a larger table you can watch it. The screenshots in this step show a table of 2 million rows (events_big) so that the progress is visible.

    1. In the entity table, find the entity. While it copies it sits in the Snapshot group and its status icon is an empty ring;

    2. Select the chevron at the end of the row (Open entity detail);

    3. Read Replication Status: the progress bar shows the percentage and the rows copied out of the total, for example 15% and 299.6K / 2.0M rows, and the badge at the top right reads Snapshot in progress;

    4. Read Snapshot metrics in Performance Metrics: Snapshot Progress and Snapshot Row Count grow until they reach 100%.

    Entity detail page of a large table with the Snapshot in progress badge marked 1
    Figure 4. The entity detail page shows the snapshot percentage (2), the badge (1) and the snapshot metrics (3).
    Entity table with one entity in the Snapshot group and a ring status icon
    Figure 5. In the entity list the entity is in the Snapshot group (2) with a ring as status icon (1).

    When the snapshot is complete, CDC takes over automatically. You do not start CDC a second time.

    1. Go back to the pipeline and look at the entity table: the entity is now in the Active group and its status icon is the green CDC icon;

    2. Read Last rows: it shows the size of the last batch, which is the snapshot batch (200 and 1000 in the example) until a change arrives;

    3. Open the entity detail page and select Count both: Source and Target show the same count and the difference reads 0, with Match under it. The yellow banner "Replication is running: counts keep moving while CDC delivers changes" is expected while CDC runs.

    Entity table with both entities in the Active group
    Figure 6. Both entities are active (1) and the last batch matches the snapshot (2).
    Entity detail page of customers with the Active badge marked 1
    Figure 7. Count both compares the source and the target; the badge reads Active (1) and Snapshot Progress shows 100 percent (3).
    Expected result

    The snapshot is finished and CDC is running when the entity is in the Active group with the CDC icon, the detail page shows Snapshot Progress at 100.0%, and Count both gives the same number on both sides.

    The status icon of an entity in the list stays an empty ring during a snapshot and its tooltip can read 0% | 0 / 0 items moved. Read the percentage on the entity detail page, as shown above.
    Prove that CDC delivers an insert, an update and a delete

    SQL

    Run each statement on the source, wait a few seconds, then run the check on the target.

    1. Insert a customer on the source:

      INSERT INTO shop.customers (id, full_name, email, country, tier, status, credit_limit, created_at)
VALUES (201, 'Ada Lovelace', 'ada@example.com', 'GB', 'gold', 'active', 5000.00, now());

      Check the target:

      SELECT id, full_name, tier FROM shop.customers WHERE id = 201;
SELECT count(*) FROM shop.customers;

      The row is there with gold, and the count is 201.

    2. Update the row on the source:

      UPDATE shop.customers SET tier = 'platinum' WHERE id = 201;

      Check the target: SELECT id, tier FROM shop.customers WHERE id = 201; returns platinum.

    3. Delete the row on the source:

      DELETE FROM shop.customers WHERE id = 201;

      Check the target: SELECT count(*) FROM shop.customers WHERE id = 201; returns 0 and the total is back to 200.

    4. Repeat the three changes on shop.orders, for example INSERT INTO shop.orders (id, customer_id, amount, currency, status, order_date) VALUES (1001, 1, 99.90, 'EUR', 'new', current_date);, then UPDATE shop.orders SET status = 'paid', amount = 109.90 WHERE id = 1001;, then DELETE FROM shop.orders WHERE id = 1001;. The target shows the row as new, then paid with 109.90, then gone, and the total is 1000 again.

    Expected result

    The target follows every change within a few seconds: the new row appears, the update changes it, and the delete removes it. Replication runs without any further action in the Control Plane.

    Pause an entity

    UI

    1. In the customers row, select Stop sync (the pause icon);

    2. Read the dialog: pausing keeps the last checkpoint and does not delete any data from the connected databases;

    3. Select Pause entity.

    Pause customers dialog explaining that the checkpoint is preserved and that no data is deleted
    Figure 8. Confirm the pause with Pause entity.
    Expected result

    customers returns to the Hold group with its last values (Last rows, Timestamp) still visible. Changes made on the source while the entity is paused are picked up when you resume it with Where it stopped.

    You can also pause from the blue pause button on the entity detail page, with Pause Section in the header of a running section, or by selecting several running entities and choosing Pause in the blue bar. A snapshot in progress is paused in the same way.
    Resume from where the entity stopped

    UI + SQL

    1. On the source, insert a customer while customers is on hold:

      INSERT INTO shop.customers (id, full_name, email, country, tier, status, credit_limit, created_at)
VALUES (202, 'Grace Hopper', 'grace@example.com', 'US', 'silver', 'active', 3000.00, now());

    2. In the customers row, select Start sync;

    3. In Start from, check that Where it stopped is selected. It is the default once the entity has run;

    4. Select Start entity.

    Sync changes (CDC) dialog with Where it stopped selected and marked 1 and the Start entity button marked 2
    Figure 9. Where it stopped continues from the last checkpoint, so nothing is lost (1).
    Expected result

    No copy runs. The target receives the change made while the entity was paused: SELECT id, full_name FROM shop.customers WHERE id > 200; returns customer 202.

    Start CDC only and skip what changed while the entity was stopped

    UI + SQL

    1. Pause customers as before;

    2. On the source, insert customer 203 (Alan Turing, GB, bronze) while it is on hold;

    3. Select Start sync in the customers row;

    4. In Start from, select Now. The warning "Changes made since …​ will not reach the target" appears and the What will happen panel shows Forget the checkpoint with the tag Skips changes;

    5. Select Start entity;

    6. On the source, insert customer 204 (Edsger Dijkstra, DE, bronze).

    Sync changes (CDC) dialog with the Now tile marked 1
    Figure 10. Now ignores the past (1) and warns you about it (2).
    Expected result

    No snapshot runs and CDC starts from the current position. On the target, SELECT id FROM shop.customers WHERE id > 200 ORDER BY 1; returns 202 and 204. Customer 203, changed while the entity was stopped, is missing and stays missing until you copy it with a snapshot.

    Now forgets the checkpoint. Use it only when you do not need the changes made while the entity was stopped, or when you realign the table afterwards with a snapshot.
    Run a one-time snapshot without CDC

    UI + SQL

    A one-time snapshot copies the current rows and stops. It does not start CDC. The entity must be on hold.

    1. Pause customers;

    2. In the customers row, select Run one-time snapshot, or select the entity and choose Snapshot in the blue bar, or use Section Snapshot in the Hold section header;

    3. Read Copy progress. For an entity that never copied, or whose last copy finished, the dialog says that this copy reads every row;

    4. In Data already in the target, keep Keep and merge;

    5. Read the What will happen panel: the last step is No live changes;

    6. Select Start snapshot.

    Hold section of the entity table with the section buttons marked 1 and the buttons of the customers row marked 2
    Figure 11. Hold-section buttons (1) and entity buttons (2) that open the start dialogs.
    Snapshot (copy-only) dialog with the Data already in the target tiles marked 1
    Figure 12. A one-time snapshot copies the rows and then stops (2). Choose what happens to the existing rows (1).
    Expected result

    customers copies the rows and returns to the Hold group, with Last rows showing the copied batch. On the target, customer 203 is now present, so SELECT count(*) FROM shop.customers; returns 203 (the 200 original rows plus 202, 203 and 204).

    Now insert one more customer on the source, for example 205 (Barbara Liskov, US, gold), and wait ten seconds.

    Expected result

    Customer 205 is not on the target. A one-time snapshot never follows later changes. To follow them, start the entity with Start sync.

    If a one-time snapshot is paused or interrupted, the next Run one-time snapshot shows Copy progress with two choices. Continue an interrupted copy picks up from the saved progress and shows how much was copied, for example "1% copied · 23,046 of 2,000,000 rows". Start over forgets the saved progress and copies every row again, and it is the choice selected by default.

    Snapshot (copy-only) dialog for a large table with the Continue an interrupted copy tile marked 1
    Figure 13. After an interrupted copy, choose Continue (1) or Start over (2).

    After you continue an interrupted copy, compare the counts with Count both or with SQL before you rely on the target. Start over is the safer choice when the data must be complete.
    Restart an entity from zero

    UI + SQL

    Restarting from zero drops the checkpoint, copies every row again and then follows live changes. Use it to realign the target with the source, for example after you used Now or after rows were deleted on the source while the entity was stopped.

    1. Pause the entities to restart;

    2. On the source, remove the extra test rows: DELETE FROM shop.customers WHERE id > 200;;

    3. On the target, add a stale row that does not exist on the source, so you can see it disappear: INSERT INTO shop.customers VALUES (999, 'Stale Row', 'stale@example.com', 'IT', 'bronze', 'active', 1, now());;

    4. Select the checkboxes of customers and orders, then select Start CDC in the blue bar;

    5. In Start from, select Full copy;

    6. In Data already in the target, select Empty the table first;

    7. Select the box I understand that rows in the target will be deleted. Start entity is disabled until you do;

    8. Select Start entity.

    Sync changes (CDC) dialog with Full copy marked 1
    Figure 14. Full copy (1) with Empty the table first (2) restarts from zero. Confirm the deletion (3), then start (4).
    Expected result

    Both entities copy again and return to Active. On the target customers has 200 rows and orders has 1000 rows, and customer 999 is gone: Empty the table first deleted every row before the copy.

    With Keep and merge, rows that exist on the target but no longer on the source stay on the target, because the copy only inserts and overwrites rows with the same key. Delete only some rows first removes only the rows that match a filter you build (Delete the rows where a column, an operator and a value) before the copy. Use Empty the table first or the filter when the target must match the source exactly.

    Choose how to start an entity

    The start dialog and the snapshot dialog combine a start point with a rule for the data already in the target.

    Action What it does When to use it

    Start sync with Full copy

    Copies all rows first, then follows live changes (snapshot, then CDC). The checkpoint is dropped.

    The first start of an entity, or a restart from zero.

    Start sync with Where it stopped

    Continues from the last checkpoint and copies the changes made while the entity was stopped. No snapshot runs. Not available before the first start.

    Resuming after a pause, a maintenance window or a restart.

    Start sync with Now

    Forgets the checkpoint and follows only new changes. No snapshot runs and the changes made while the entity was stopped are skipped.

    The history is not needed, or you realign the data with a snapshot.

    Run one-time snapshot

    Copies the current rows and stops. No CDC.

    One-off loads and refreshes, or catching up rows that Now skipped.

    Pause (Stop sync)

    Stops the entity and keeps its checkpoint. Nothing is deleted.

    Maintenance, or before you change how the entity starts.
    Data already in the target Effect on the copy

    Keep and merge

    Rows with the same key are overwritten and the other rows stay. Marked Safe.

    Empty the table first

    Every row in the target table is deleted before the copy. Marked Deletes data; you must confirm.

    Delete only some rows first

    The rows matching your filter are deleted before the copy. Marked Deletes data; you must confirm and add at least one condition.

    These options appear only when a copy runs: with Full copy and in the snapshot dialog. Speed (Standard, Faster or Custom) sets how many reads and writers the copy uses.

    Read the status of an entity

    The first column of the entity table shows the status of each entity, and the entities are grouped by status.

    Group and icon Tooltip Meaning

    Hold, yellow pause icon

    Entity is on hold

    The entity does not run. It never started, or it was paused, or a one-time snapshot finished.

    Snapshot, empty ring

    The progress, written as `percent

    rows moved / total items moved`

    The initial copy or a one-time snapshot is running. The percentage is on the entity detail page.

    Active, green CDC icon

    CDC active

    The entity runs and delivers changes.

    Error, red icon

    The error message
    Entity table with customers in the Error group with a red status icon marked 1 and orders in the Active group
    Figure 15. An entity in error (1) stays in the list with its pause button, next to the entities that are active.

    In the example, a trigger on the target table rejected an insert, and the entity went to Error. After the trigger was removed, the entity delivered the change and returned to Active by itself, without a restart.

    Verify the result

    You have successfully started and managed replication if:

    • The target holds 200 customers and 1000 orders after the first snapshots;

    • An insert, an update and a delete on the source reach the target within seconds while the entities are Active;

    • Where it stopped delivered the change made while the entity was paused, and Now skipped it;

    • A one-time snapshot copied the missing row, returned the entity to Hold and did not follow later changes;

    • Full copy with Empty the table first removed the stale row and brought the target back to 200 and 1000 rows;

    • Count both on the entity detail page shows the same count for the source and the target.

    Troubleshooting

    Problem Possible cause What to do

    Where it stopped is greyed out

    The entity has never run, so it has no checkpoint

    Start with Full copy, which is preselected on the first start

    Start entity or Start snapshot is disabled

    Empty the table first or Delete only some rows first is selected and the confirmation box is not selected, or the filter has no condition

    Select I understand that rows in the target will be deleted and complete the filter, or choose Keep and merge

    Run one-time snapshot and Start sync are missing from the row

    The entity is running or copying, so the row shows Stop sync instead

    Pause the entity first

    A row changed while the entity was stopped is missing on the target

    The entity was started with Now

    Run a one-time snapshot with Keep and merge, or restart with Full copy

    A row deleted on the source is still on the target after a snapshot

    Keep and merge does not delete rows

    Use Empty the table first or Delete only some rows first

    The entity is in the Error group

    The target or the source rejected a change; hover over the status icon to read the message

    Fix the cause on the database side. In the lab the entity returned to Active by itself once the cause was removed

    The snapshot ring in the list stays empty

    The list does not draw the percentage

    Open the entity detail page and read the progress bar

    The counts differ right after Count both

    CDC is delivering changes while you count

    Put the entity on hold for an exact comparison, or count again after a few seconds

    Related guides

    Contact MOLO17 Support if you need assistance with starting or restarting replication.