Protecting the source of a PostgreSQL CDC mirror: a step-by-step walkthrough in Hydra Studio

This walkthrough shows what Hydra ETL does on a real PostgreSQL 16 to protect the source of CDC mirror, in four scenes:

1. A normal mirror.
2. The mirror breaks and the WAL piles up: Hydra ETL warns, and a workflow sends an alert.
3. max_slot_wal_keep_size protects the disk and PostgreSQL invalidates the slot: Hydra ETL stops instead of going on with a gap.
4. Rebuilding the mirror.

Every screenshot below comes from this exact sequence, run end to end.

0. Prerequisites

• Docker
• Python 3.10+
• The Hydra ETL repository: https://github.com/bejaouibechir/Hydra

Start PostgreSQL with logical replication:

docker run --name hydra-pg-cdc -e POSTGRES_PASSWORD=hydra -p 5433:5432 -d postgres:16 -c wal_level=logical 

Go to the example, create its .env, and create the two databases: shop (the application) and analytics (the mirror):

git clone https://github.com/bejaouibechir/hydra-tuto-cdc-slot-protection.git cd examples/postgres-cdc-mirror printf 'PG_HOST=127.0.0.1\nPG_PORT=5433\nPG_USER=postgres\nPG_PASSWORD=hydra\n' > .env docker exec -i hydra-pg-cdc psql -U postgres < db/initdb.sql docker exec -i hydra-pg-cdc psql -U postgres < db/seed.sql 

On Windows PowerShell, < does not redirect: use Get-Content db/initdb.sql | docker exec -i hydra-pg-cdc psql -U postgres, and the same for every db/*.sql script below.

 Get-Content db/initdb.sql | docker exec -i hydra-pg-cdc psql -U postgres 

 Get-Content db/seed.sql | docker exec -i hydra-pg-cdc psql -U postgres 

Start Studio, then open the folder examples/postgres-cdc-mirror (Open a project):

hdrctl serve --open 

1. A normal mirror

The job

Open the sync workflow, then the job orders_mirror. It has two nodes:

• the source order_changes, of type PostgreSQL CDC: table public.orders, slot hydra_orders, publication hydra_orders;
• the destination mirror: PostgreSQL, table analytics.orders, mode upsert on order_id, atomic load.

Double-click the source to see its settings:

Lower the warning threshold for the demo

By default, Hydra ETL warns when the slot keeps more than 1 GB of WAL. For the demo we lower it to 5 MB. Add one line under connection in jobs/orders_mirror/sources.yaml:

 slot: hydra_orders publication: hydra_orders lag_warning_mb: 5 # demo: warn from 5 MB of WAL kept 

The node form does not show this key. Studio reloads the file and keeps the key when it saves the job. Hydra DSL shows

First run

Go to the Workflow configuration tab and clic Run Workflow. On this first run, Hydra ETL creates the publication and the slot, then copies the whole table.

Check both tables. Studio has a terminal (the >_ button of the canvas, tab TERMINAL):

docker exec -i hydra-pg-cdc psql -U postgres < db/check.sql 

 Get-Content db/check.sql | docker exec -i hydra-pg-cdc psql -U postgres 

1. The mirror breaks, the WAL piles up

Break the mirror

The mirror refuses negative amounts. The shop updates 500 orders and records one order at -15.00:

docker exec -i hydra-pg-cdc psql -U postgres < db/break.sql 

 Get-Content db/break.sql | docker exec -i hydra-pg-cdc psql -U postgres 

Run the project. The run fails on the negative amount, the mirror does not change, and the slot does not move: the 500 updates will be read again at the next run.

The source keeps working

Meanwhile, the application keeps writing. db/wal.sql writes about 30 MB of WAL into a table that is not mirrored, then shows the slot:

docker exec -i hydra-pg-cdc psql -U postgres < db/wal.sql 

 Get-Content db/wal.sql | docker exec -i hydra-pg-cdc psql -U postgres 

What Hydra ETL does before each run

Run the project again. Before reading anything, the CDC source reads the state of its slot in pg_replication_slots. The slot keeps more than lag_warning_mb, and the run says so:

A warning in a run log is not an alert, though: nobody reads logs on a Saturday. The next step sends it somewhere.

2 bis. Send an alert with a fallback

A fallback replaces a workflow when a condition is true, checked just before the workflow starts. Two conditions read the slot:

1) The alert URL as a secret

To try it, take a free URL on https://webhook.site: it shows every request it receives. Any chat webhook (Slack, Teams…) works the same way.

In Project settings › Secrets, add ALERT_WEBHOOK_URL:

2) The replacement workflow

Create workflows/sync_with_alert.yaml. It sends the alert, then runs the same job:

version: "1.0" workflow: name: sync_with_alert steps: - name: alert type: action action: webhook params: url: ${SECRET:ALERT_WEBHOOK_URL} body: text: "CDC slot of order_changes is falling behind: the mirror has not loaded for a while." on_failure: continue # an alert that fails must not block the load  - name: orders type: job job: ../jobs/orders_mirror depends_on: [alert] 

Studio picks it up:

The replacement runs the job too: the alert does not stop the mirror from catching up once it is repaired.

3) The fallback on sync

In Project settings › Execution order, click the fallback button of sync:

• replace with: sync_with_alert;
• when: build the condition with the assistant (insert a function, its source, the comparison, the value);
• if a check cannot answer: keep sync.

lostis tested first: on an invalidated slotcdc.slot_lagcannot answer, andorstops at the first true condition. The savedworkflows/thread.yaml`:

`yaml
thread:

• mode: sequential workflows:
  • name: sync fallback: use: [sync_with_alert] when: "cdc.slot_status('order_changes') == 'lost' or cdc.slot_status('order_changes') == 'unreserved' or cdc.slot_lag('order_changes') > 5MB" `

4) Run

Run the project. The run says which workflow ran, and why: the value of each condition.

The alert was received (here by a local test receiver; webhook.site shows the same body):

3. Protect the disk: max_slot_wal_keep_size

The alert tells someone. If nobody fixes the mirror over the weekend, the disk still fills up. PostgreSQL 13+ can cap the WAL a slot holds:

sql
ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';
SELECT pg_reload_conf();


This is a trade-off. Past the limit, PostgreSQL invalidates the slot (wal_status = lost): the WAL is removed, and the changes made since the last success can no longer be read. The disk is safe, but the change stream now has a gap.

For the demo, db/limit.sql sets the limit to 1 MB, and the source writes some more:

bash
docker exec -i hydra-pg-cdc psql -U postgres < db/limit.sql


powershell
Get-Content db/limit.sql | docker exec -i hydra-pg-cdc psql -U postgres


What Hydra ETL does

Run the project:

• the fallback switches on cdc.slot_status('order_changes') = lost, and the alert is sent;
• the job fails on purpose, before reading anything, and says how to rebuild the mirror.

Resuming after the gap would have made a mirror that looks fine and is wrong. Hydra ETL refuses to do that.

4. Rebuild the mirror

Fix the wrong order at the source, then follow the three steps of the message. db/rebuild.sql drops the slot, empties the mirror and gives the server back its default limit:

bash
docker exec -i hydra-pg-cdc psql -U postgres < db/repair.sql
docker exec -i hydra-pg-cdc psql -U postgres < db/rebuild.sql

powershell
Get-Content db/repair.sql | docker exec -i hydra-pg-cdc psql -U postgres
Get-Content db/rebuild.sql | docker exec -i hydra-pg-cdc psql -U postgres


Run the project. The slot is gone, so cdc.slot_status is missing and the fallback is not needed. sync creates a new slot and copies the table again.

bash
docker exec -i hydra-pg-cdc psql -U postgres < db/check.sql

powershell
Get-Content db/check.sql | docker exec -i hydra-pg-cdc psql -U postgres


Summary

To watch the slot from your own monitoring:

sql
SELECT slot_name, active, wal_status, safe_wal_size,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;


Clean up:

bash
docker rm -f hydra-pg-cdc