# Log-Based CDC: Setup for PostgreSQL, MySQL, Oracle and More

> How log-based CDC works and what PostgreSQL, MySQL, SQL Server, Oracle and MongoDB need to capture every insert, update and delete.

Source: https://www.erathos.com/en/blog/log-based-cdc-databases
Em português: https://www.erathos.com/blog/log-based-cdc-databases
Published: 2026-10-23
Category: Platform Updates

![CDC](https://cms-media.erathos.com/CDC.png)

Consider an hourly sync that requests the orders table for all rows with `updated_at` greater than the previous run’s timestamp. It then loads the response and reports success. But what about an order that was deleted by a customer this afternoon? It never returns because that row does not exist. Hence, it’s still in the warehouse and is counted in the revenue dashboard.

Log-based change data capture (CDC), on the other hand, reads the same log that the database already creates for each insert, update, and delete. Erathos now provides log-based CDC on PostgreSQL, MySQL, SQL Server, Oracle, and MongoDB. Each database has its own log format, which requires different configurations to read. Each of the five also has its own failure modes when the log runs out.

[Erathos Change Data Capture: Every Insert, Update and Delete, Captured](https://youtu.be/OwJwZwwIP9Q)

## Why cursor-based syncs miss data

A cursor-based sync (Erathos calls it a partial sync) creates a cursor (or bookmark) of the most recent updated\_at value it has fetched, and only fetches updates that occur after this cursor. This makes it cheap, but it leaves three things out of reach:

- **Deletes**: rows that have been deleted will not be returned in any query, and therefore will never be updated in your warehouse until someone reloads the table.
- **In-between states**: any row that has been updated multiple times between syncs will be returned only once, with the latest update, so if you are creating a history of updates, you will miss intermediate states.
- **Missing or unreliable timestamps**: many production tables don’t have an updated\_at column, or if they do, some types of updates don’t update it. If you use a creation date as the cursor, you will [miss all updates](https://docs.erathos.com/platform/connections/sync-types).

We reproduced the first two in PostgreSQL with a three-row orders table. After the last run, order 1 changed twice (to `paid`, then to `shipped`) and order 2 was deleted. The cursor query found one row:

`SELECT id, status FROM orders WHERE updated_at > :'last_run';`

`id | status`
`----+---------`
`  1 | shipped`

Notice that the `paid` state is no longer present and the output doesn’t indicate that order 2 was deleted. For a closer look at the differences between each approach, please refer to this [cursor-based sync vs CDC comparison](https://www.erathos.com/en/blog/cursor-based-sync-vs-change-data-capture?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=log-based-cdc-databases) blog post.

What follows is an example of what that delete would look like depending on which type of sync you use:

![Why cursor sync misses hard deletes](https://cms-media.erathos.com/Why cursor sync misses hard deletes)

## How does log-based CDC work?

Log-based CDC reads the existing change log that the database is already writing for crash recovery or replication, and passes on all inserts, updates and deletes to the warehouse in the order they occurred. Because log-based CDC reads the change log, not the tables, it sees deletes and in-between states, and never needs to scan the source tables for changes.

The change log is called different things in different databases. For PostgreSQL it’s the write-ahead log (WAL), for MySQL the binary log, for SQL Server the transaction log, for Oracle redo logs and for MongoDB the oplog. The reader also remembers how far it got so each sync starts off from where the last one finished.

Here are the same three changes from the test above, read from the PostgreSQL WAL through a logical replication slot:

`SELECT data FROM pg_logical_slot_get_changes('demo_slot', NULL, NULL);`

`BEGIN 755`
` table public.orders: UPDATE: id[integer]:1 status[text]:'paid' amount[numeric]:100.00 updated_at[timestamp with time zone]:'2026-10-21 17:43:07.121892+00'`
` COMMIT 755`
` BEGIN 756`
` table public.orders: UPDATE: id[integer]:1 status[text]:'shipped' amount[numeric]:100.00 updated_at[timestamp with time zone]:'2026-10-21 17:43:07.12316+00'`
` COMMIT 756`
` BEGIN 757`
` table public.orders: DELETE: id[integer]:2`
` COMMIT 757`

The updates will arrive in the correct order, and the same goes for the delete. The delete contains only the primary key, which is why the PostgreSQL setup below asks for the full old row to be logged for a table.

Erathos will write the changes it extracted during a sync into temporary files in a cloud bucket (S3, Google Cloud Storage, or Azure Blob Storage) within the Erathos infrastructure. The warehouse will then [load those files in bulk](https://docs.erathos.com/platform/how-we-move-data) using `COPY` or its own load command, and Erathos will delete those temporary files when it’s done. The production database is spared the heavy lifting. This makes sense, because a transactional database is meant for many small writes, and a warehouse for large reads ([OLTP vs OLAP](https://www.erathos.com/en/blog/olap-vs-oltp-analytics-vs-transaction-databases?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=log-based-cdc-databases)).

This is the whole flow, from the database log to the warehouse:

![How log-based CDC reaches the warehouse](https://cms-media.erathos.com/How log-based CDC reaches the warehouse)

## PostgreSQL: reading the WAL

All the changes to the table are written to PostgreSQL’s WAL (write-ahead log) before the table files themselves are modified. If the `wal_level` is set to `logical`, then the WAL [contains enough information to recreate the row-level changes](https://www.postgresql.org/docs/current/runtime-config-wal.html) (this process is called logical decoding). A replication slot tracks the reader’s position in the WAL.

The [Erathos PostgreSQL connector](https://docs.erathos.com/connectors/databases/postgresql) requires:

1. Setting `wal_level` to `logical`. The default value is `replica`.
2. Allowing for at least 10 replication slots and 10 WAL senders. The default value for both of those is 10, so many servers will already meet this requirement. [See the PostgreSQL documentation](https://www.postgresql.org/docs/current/runtime-config-replication.html).
3. Setting `max_slot_wal_keep_size` to a value greater than 0. The default value of -1 will allow the slot to keep an unlimited amount of WAL (and can fill up the disk if the reader is stopped). [See the PostgreSQL documentation](https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE).
4. `REPLICA IDENTITY FULL` on each table you want to capture. PostgreSQL defaults to [logging only the old primary key values](https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY) on update/delete.
5. A user in PostgreSQL with the `REPLICATION` privilege.

These statements cover all five. The WAL limit applies on a reload, and the other three server settings need a restart:

`ALTER SYSTEM SET wal_level = 'logical';`
`ALTER SYSTEM SET max_replication_slots = 10;`
`ALTER SYSTEM SET max_wal_senders = 10;`
`ALTER SYSTEM SET max_slot_wal_keep_size = '5GB';`

`-- after restarting PostgreSQL:`
`ALTER TABLE orders REPLICA IDENTITY FULL;`
`ALTER ROLE some_user WITH REPLICATION;`

After the restart, this check shows the new values. PostgreSQL reports the WAL limit in megabytes, so 5120 is the 5 GB set above:

`SELECT name, setting FROM pg_settings`
`WHERE name IN ('wal_level', 'max_replication_slots', 'max_wal_senders', 'max_slot_wal_keep_size')`
`ORDER BY name;`

`name          | setting`
`------------------------+---------`
` max_replication_slots  | 10`
` max_slot_wal_keep_size | 5120`
` max_wal_senders        | 10`
` wal_level              | logical`

With `REPLICA IDENTITY FULL` on, deleting order 3 in the test table produced a change with the whole old row instead of just the id:

` table public.orders: DELETE: id[integer]:3 status[text]:'pending' amount[numeric]:300.00 updated_at[timestamp with time zone]:'2026-10-21 17:43:07.104481+00'`

On the downside, more WAL is generated. The `logical` level of replication [increases the amount of WAL](https://www.postgresql.org/docs/current/runtime-config-wal.html) written, particularly if many tables are configured to use `REPLICA IDENTITY FULL` and have a lot of UPDATEs and DELETEs.

## MySQL: reading the binlog

The [Erathos MySQL connector](https://docs.erathos.com/connectors/databases/mysql) captures data changes in MySQL databases using the MySQL binary log (binlog) - the log used for MySQL replication. This log must be configured to be in the `ROW` format with `FULL` row images, so that updates and deletes contain all columns in the event.

From MySQL 8.0, [binary logging is on by default](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html) and both settings already default to what CDC needs. A MySQL server with untouched settings returned this:

`SHOW VARIABLES WHERE Variable_name IN (`
`    'log_bin',`
`    'binlog_format',`
`    'binlog_row_image',`
`    'binlog_expire_logs_seconds',`
`    'binlog_row_value_options'`
`);`

`+----------------------------+---------+`
`| Variable_name              | Value   |`
`+----------------------------+---------+`
`| binlog_expire_logs_seconds | 2592000 |`
`| binlog_format              | ROW     |`
`| binlog_row_image           | FULL    |`
`| binlog_row_value_options   |         |`
`| log_bin                    | ON      |`
`+----------------------------+---------+`

A server configured with the `minimal` row image will only include the primary key in the before image (and thus no other values in the deleted row). These settings require modifying `my.cnf` and restarting MySQL.

The CDC user needs these grants. The first three are used only while taking a snapshot:

`GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'your_existing_user';`

Check out this blog post to see how to enable binlog, and load the first set of data into BigQuery: [MySQL CDC to BigQuery](https://www.erathos.com/en/blog/mysql-cdc-to-bigquery?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=log-based-cdc-databases)

## SQL Server: CDC change tables

SQL Server has its own CDC feature. When enabled, a capture job reads the transaction log and then populates any changes to change tables in the cdc schema, which Erathos can then read. This means that the [Erathos SQL Server connector](https://docs.erathos.com/connectors/databases/sql-server) has four requirements:

1. CDC must be enabled on the database using [`sys.sp_cdc_enable_db`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-db-transact-sql), which must be called by a user with a `sysadmin` login or a member of the `db_owner` role on Azure SQL Database. If you are on AWS RDS then the equivalent call is `msdb.dbo.rds_cdc_enable_db`.
2. CDC must be enabled on tables using [`sys.sp_cdc_enable_table`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql). This means that you cannot capture any changes to tables that don’t have CDC enabled on them.
3. SQL Server Agent must be enabled, since it is used to run the capture and cleanup jobs. Note that on Azure SQL Database, there is no Agent and instead changes are captured by a built-in scheduler.
4. A user must be created that has read access to the captured tables, as well as the `cdc` schema.

Here is the setup for one database and one table:

`SELECT name, is_cdc_enabled FROM sys.databases WHERE name = 'shop';`

`USE shop;`
`EXEC sys.sp_cdc_enable_db;`

`EXEC sys.sp_cdc_enable_table`
`    @source_schema = N'dbo',`
`    @source_name = N'orders',`
`    @role_name = NULL,`
`    @supports_net_changes = 0;`

`GRANT SELECT ON SCHEMA::dbo TO erathos;`
`GRANT SELECT ON SCHEMA::cdc TO erathos;`

There are two important defaults with CDC:

- **Changes expire after 3 days**: [the cleanup job keeps change rows for 4320 minutes before deleting them](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server). If those rows haven’t been read by Erathos, we need to take a fresh snapshot of the connection.
- **New columns are ignored**: the capture instance will only use the columns it was configured with when it was created. To add columns to a table with CDC enabled, you must create a second capture instance with the new columns and then remove the old one (SQL Server only allows up to [two capture instances per table](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/administer-and-monitor-change-data-capture-sql-server)).

And CDC even has some effect on the transaction log itself. Even in simple recovery mode, SQL Server can’t truncate the transaction log until [the capture job has read the changes](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server). This means that if the Agent job is stopped, the transaction log will continue to grow.

## Oracle: redo and archive logs through LogMiner

Online redo logs are the files Oracle uses to store the changes made to the database, and they are written in a circular fashion. When the database is in ARCHIVELOG mode, each full redo log file is copied to an archive log before being overwritten. Erathos uses LogMiner, a tool provided by Oracle to [read redo logs using SQL](https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html), to read both online and archived redo logs.

The following are the prereqs for the [Erathos Oracle connector](https://docs.erathos.com/connectors/databases/oracle):

1. **ARCHIVELOG mode:** This is disabled [by default for newly created databases](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-archived-redo-log-files.html) and enabling it requires restarting the database.
2. **Supplemental logging enabled:** Database-level supplemental logging must be enabled, and supplemental logging on all columns must be enabled on every captured table. Oracle does not enable supplemental logging by default and LogMiner cannot be used unless supplemental logging is enabled. Enabling database-level supplemental logging with minimal logging does not add significant overhead.
3. **CDC user:** A database user with privileges required to use LogMiner (e.g. `LOGMINING` and `EXECUTE_CATALOG_ROLE`) is needed to use the Oracle CDC connector. This must be a common user (e.g. `c##erathos_cdc`) if you are using a multitenant database. Refer to the Erathos documentation for a list of required privileges.

These statements check and turn on the first two, run as SYSDBA:

`SELECT LOG_MODE FROM V$DATABASE;`
`ALTER DATABASE ARCHIVELOG;`

`ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;`
`ALTER TABLE shop.orders ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;`
`SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;`

If archive logs required by CDC are deleted, the connection requires a new initial snapshot. This can happen if, for example, a backup policy is set up in Oracle’s backup tool RMAN, or if the recovery area is full. It is also possible that a managed service provider places restrictions on archive logging, supplemental logging or LogMiner (such as Oracle’s Autonomous Database), in which case CDC may not work at all.

## MongoDB: the oplog and change streams

MongoDB’s oplog is a fixed-size collection that stores all the write operations. Erathos uses [change streams](https://www.mongodb.com/docs/manual/changestreams/) to watch for changes in the oplog. Change streams can only be used on replica sets and sharded clusters, and the [Erathos MongoDB connector](https://docs.erathos.com/connectors/databases/mongodb) requires:

- **A replica set or sharded cluster**. The `db.hello().setName` returns the replica set name, or nothing if the MongoDB instance is a standalone server. MongoDB Atlas clusters are always replica sets.
- **A user with the built-in **`readAnyDatabase`** role**. This allows the user to read databases, list them, and open change streams.
- **An oplog window longer than the time between syncs**. The oplog drops oldest entries as its size reaches the limit. The [default size for oplog is 5% of the available disk space](https://www.mongodb.com/docs/manual/core/replica-set-oplog/) (990 MB to 50 GB).

Deletes need one more decision. A delete event carries only the document's `_id` by default. On MongoDB 6.0 and later, turning on pre-images for a collection adds the full document as it was before the delete:

`db.runCommand({ collMod: "orders", changeStreamPreAndPostImages: { enabled: true } })`

On a test replica set, deleting one document before that command and one after it produced these two events (trimmed):

`{`
`  operationType: 'delete',`
`  ns: { db: 'shop', coll: 'orders' },`
`  documentKey: { _id: 1 }`
`}`
`{`
`  operationType: 'delete',`
`  ns: { db: 'shop', coll: 'orders' },`
`  documentKey: { _id: 2 },`
`  fullDocumentBeforeChange: { _id: 2, status: 'pending', amount: 200 }`
`}`

Keep in mind that pre-images [take storage space and processing time](https://www.mongodb.com/docs/manual/changestreams/) and make the most sense for those collections where you need the fields of the deleted document in the warehouse.

## The five databases side by side

Database

Change log read

Main setup step

Retention risk

How deletes arrive

PostgreSQL

WAL, through a logical replication slot

`wal_level = logical`, plus `REPLICA IDENTITY FULL` per table

A slot keeps WAL with no limit by default and can fill the disk

Primary key only, full old row with `REPLICA IDENTITY FULL`

MySQL

Binary log

`ROW` format with `FULL` row images

Binlog files expire after 30 days by default

Full old row with `FULL` row images

SQL Server

CDC change tables, filled from the transaction log

`sp_cdc_enable_db` and `sp_cdc_enable_table`, Agent running

Cleanup job deletes changes after 3 days by default

A change row with the deleted values

Oracle

Redo and archive logs, through LogMiner

ARCHIVELOG mode plus supplemental logging

Archive logs deleted by a backup policy or a full recovery area

Full old row with all-column supplemental logging

MongoDB

Oplog, through change streams

Replica set or sharded cluster

Oldest oplog entries drop when it hits its size limit

`_id` only, full document with pre-images (6.0+)

The same five, at a glance:

![What CDC reads on each database](https://cms-media.erathos.com/What CDC reads on each database)

## Snapshot modes: initial or no\_data

When a new CDC connection is set up, a decision has to be made whether to include any existing rows in the tables to be captured. This setting is defined by Erathos as `cdc_snapshot_mode`, which works the same way for all five databases.

- `initial`: Copy each captured table once, then stream any changes after that from the log.
- `no_data`: Don't copy any data. Just stream the remaining changes in the log. If there are any rows that don't change, they won't show up.

`initial` is a good choice for almost all new connections since the warehouse starts empty and we want to capture the current set of rows. Since the initial snapshot is the only time CDC reads full tables, this is the most critical operation to schedule around low-traffic hours.

`no_data` is useful when we want only the changes from a certain point forward, such as an audit trail of edits. It can also be used if the existing rows are already in the warehouse and copying them again would just be redundant. In any case, we won't get any changes before the oldest available entry in the log, so the warehouse must have a current copy.

Another use for a fresh `initial` snapshot is to recover from lost log entries that CDC hasn't already processed.

## Log retention: the risk all five share

Each of the 5 logs has an end. When the oldest changes are removed from the log before CDC can read them, there is nothing that can replay them. The connection needs to perform a fresh initial snapshot to recover.

![When the log runs out before CDC reads it](https://cms-media.erathos.com/When the log runs out before CDC reads it)

PostgreSQL fails the other way around. A replication slot keeps WAL until its reader confirms it, so an idle slot grows until the disk is full. With a `max_slot_wal_keep_size` limit, the server removes WAL past that size even if the slot still needs it, and you are back to a snapshot, with the disk intact. In the test database, an unread slot held on to 63 MB of WAL after one insert of 200,000 small rows:

`SELECT slot_name, active,`
`       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal`
`FROM pg_replication_slots`
`ORDER BY slot_name;`

`slot_name | active | retained_wal`
`-----------+--------+--------------`
` demo_slot | f      | 63 MB`
` idle_slot | f      | 63 MB`

Retention needs to exceed the longest imaginable interval between Erathos’ reads of CDC. For instance, consider a paused replication, a Friday night failure, or a warehouse outage.

- **PostgreSQL.** `max_slot_wal_keep_size` can be set according to the available free disk space (Erathos uses 5 GB as an example). The query above shows the retained WAL. Erathos’ replication slots are prefixed with `erathos-cdc`.
- **MySQL.** `binlog_expire_logs_seconds` defaults to 30 days and should work for most schedules. For servers that have a lower value, Erathos recommends changing the value in `my.cnf` to 864000 (10 days).
- **SQL Server.** Increase the retention of the cleanup job (defaults to 3 days) to accommodate long weekends. Any changes to the job [take effect only after stopping and restarting the job](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql).
- **Oracle.** Ensure archive logs are retained longer than the maximum possible time between two syncs. Check the RMAN deletion policy. Ensure the recovery area has enough space to not fill up.
- **MongoDB.** Specify a minimum retention period for the oplog (Erathos uses 24 hours as an example). The oplog can be [resized without restarting MongoDB](https://www.mongodb.com/docs/manual/core/replica-set-oplog/). The size of the oplog is filled up faster by busy collections. Check the window of the oplog with `rs.printReplicationInfo()`.

For SQL Server, this sets retention to 7 days (10080 minutes):

`EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;`

## When is CDC worth it?

CDC asks more of the source database than a cursor sync: settings changes, sometimes a restart, and a log to keep an eye on. That cost pays off on some tables and not on others. In Erathos the update type is chosen per table, so one connection can mix them:

The table

Good fit

Rows get hard-deleted

CDC

No reliable `updated_at` column

CDC, or full refresh if it is small

You need every change kept as history

CDC

Large, with a small share of rows changing

CDC

Small lookup or settings table

Full refresh

Reliable `updated_at`, rows never deleted, only the latest state matters

Cursor-based (partial) sync

For tables that are very small, the Full Refresh (reloading the table completely) will capture deletes. If there is a primary key and a cursor column, the Cursor Sync is optimal for tables that are append-only or have updates stamped on them.

The CDC feature is available on the Erathos [Pro plan](https://www.erathos.com/en/pricing?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=log-based-cdc-databases) (update every 5 minutes).

## FAQ

### Does CDC load my production database?

Subsequent to the first snapshot, CDC only looks at the log and not at any tables. However, the first snapshot reads all captured tables once. The ongoing cost is in logging on the database server, as PostgreSQL writes more WAL, a SQL Server capture job runs on the server, MongoDB pre-images take storage space, and each engine keeps more log.

### Do I need CDC for real time?

No. CDC (Change Data Capture) is also able to capture deletes and intermediate states, which are missed by cursor syncs. Erathos runs CDC on a scheduled basis (updated every 5 minutes on the Pro plan), and cursor sync can run as frequently.

### What happens to deletes in the warehouse?

With CDC, each delete event is sent to the pipeline as its own change, with the key and the full old row data (with the settings mentioned above: REPLICA IDENTITY FULL, FULL row images, all-column supplemental logging, MongoDB pre-images). With cursor sync, deleted rows are not removed from the warehouse until a full reload is performed.

### Do I need to restart my database?

This depends on your database and its previous configuration:

- PostgreSQL: yes, to turn on wal\_level, replication slots, and WAL senders. The WAL limit requires only a reload.
- MySQL: yes, only if you change binlog settings; MySQL 8.0 defaults to ROW and FULL.
- SQL Server: stored procedures are used to enable CDC; no restart is listed in the Erathos docs.
- Oracle: yes, to enable ARCHIVELOG mode.
- MongoDB: no, to resize the oplog. If you are converting a standalone server to a replica set, that requires a restart.

All new accounts created on Erathos start on the Pro plan, which includes CDC, free for 14 days, with no credit card required. [Start your free trial](https://app.erathos.com/signup?utm_source=blog&utm_medium=organic&utm_content=bydefault&utm_campaign=log-based-cdc-databases) and hook up the database that you’ve configured above.