CDC replication

📘
CDC replication only for Bulk Syncs
CDC replication from PostgreSQL is only available for Bulk Syncs.

When you create a Bulk Sync from PostgreSQL into your data warehouse, we recommend (but don't require) CDC (change data capture) replication. With CDC, Polytomic captures changes in real time instead of running full table scans to find changes since the last sync.

Requirements

To enable this, Polytomic requires the following of your PostgreSQL instance:

PostgreSQL 10 or later (Polytomic uses the pgoutput plugin). PostgreSQL 16 or later required if CDC will be from a read replica.
wal_level = logical
The PostgreSQL wal_level setting must be set to logical. You can determine the current setting by running this query:
```
my_database=> show wal_level;
wal_level
---
logical  
(1 row)
```
If you're on Amazon RDS or Aurora, you can't set wal_level directly. Instead, set rds.logical_replication to 1 and reboot the database:
- Amazon RDS for PostgreSQL: set it in the instance's DB parameter group (see AWS's documentation).
- Amazon Aurora PostgreSQL: set it in the DB cluster parameter group (see AWS's documentation).
You can't modify the default parameter groups. If you're using a default parameter group, create a custom parameter group first.
Set wal_sender_timeout to five minutes
Available replication slot
Polytomic requires a replication slot for each bulk sync (you will be setting up one sync per database, unless you want different sync schedules for the same database). The same slot will be used for all tables synced as part of that bulk sync configuration. The total number of replication slots is set using the max_replication_slots PostgreSQL configuration parameter.

You can inspect current replication slots using the pg_replication_slots system table:
```
SELECT * FROM pg_replication_slots;
```
A publication for the tables you wish to replicate
PostgreSQL uses a publication to track changes to one or more tables. Only a user with the owner role for a table may add it to the publication. However, after the publication is created, any user with the REPLICATION property may use it.

You can create a publication for specific tables, for all tables in a schema (PostgreSQL 15 or later), or for all tables in the database (including those created in the future). Publications for all tables or for a schema require superuser privileges. A publication for all tables may increase storage consumption on your PostgreSQL database.

Polytomic only replicates changes for tables in the publication. On PostgreSQL 15 or later, if the publication specifies a column list for a table, only those columns are replicated.

🚧
Tables without primary keys
PostgreSQL requires a replica identity in order to replicate DELETE and UPDATE operations for a table. By default this is the primary key for non-system tables.
In order to add a table without a primary key to the publication, it must have a replica identity set.
Failure to set the replica identity will cause errors when attempting to update or delete rows, so if your table does not have a primary key or replica identity, do not add it to the publication.

Your publication name can be anything; you’ll enter it in Polytomic's PostgreSQL connection configuration (note: even if doing CDC from a read replica, the publication needs to be created on the primary database, not the replica. Your replica will automatically pick it up from the primary):
```
-- create a publication for specific tables
CREATE PUBLICATION polytomic FOR TABLE <table>, <table>, ...;

-- PostgreSQL 15+: create a publication for all current and future tables in a schema
CREATE PUBLICATION polytomic FOR TABLES IN SCHEMA my_schema;

-- create a publication for all current and future tables
CREATE PUBLICATION polytomic FOR ALL TABLES;
```
If you are editing an existing publication, you can use an ALTER query to add your tables to it:
```
ALTER PUBLICATION polytomic
ADD TABLE public.table1_name, public.table2_name, public.table3_name;
```
🚧
Partitioned tables
By default, PostgreSQL publishes changes to a partitioned table under the name of each partition, so Polytomic lists every partition as a separate table. To replicate a partitioned table as a single table, create the publication with publish_via_partition_root (PostgreSQL 13 or later):
```
CREATE PUBLICATION polytomic FOR TABLE <table>, <table>, ...
  WITH (publish_via_partition_root = true);
```
The setting applies to every partitioned table in the publication. Changing it on an existing publication with ALTER PUBLICATION polytomic SET (publish_via_partition_root = true) changes the table names Polytomic receives changes under, so resync the affected tables afterward.
User with the REPLICATION property set
The REPLICATION property signals that a PostgreSQL user (role) is able to manage replication slots. Polytomic uses this property to create a replication slot for each Bulk Sync.

If you're on Amazon RDS or Aurora, grant the rds_replication role instead. The Polytomic user must inherit the role's privileges. PostgreSQL 16 changed inheritance to be set on each role grant rather than on the role. The query depends on your PostgreSQL version:
```
-- PostgreSQL 16 and later
GRANT rds_replication TO polytomic_user WITH INHERIT TRUE;

-- PostgreSQL 15 and earlier
GRANT rds_replication TO polytomic_user;
```
🚧
Existing grants on PostgreSQL 16 and later
A grant without WITH INHERIT TRUE only inherits if the user had the INHERIT attribute when you granted the role. Running ALTER ROLE polytomic_user INHERIT afterward doesn't change existing grants. If you've already granted the role, re-run the GRANT ... WITH INHERIT TRUE query to update it.

To check the grant, run:
```
-- PostgreSQL 16 and later: inherit_option must be true
SELECT roleid::regrole, member::regrole, inherit_option
FROM pg_auth_members
WHERE member = 'polytomic_user'::regrole;
```
On PostgreSQL 15 and earlier, make sure the user has the INHERIT attribute (the default for new users): ALTER ROLE polytomic_user INHERIT;.

Otherwise, use this query:
```
ALTER ROLE polytomic_user WITH REPLICATION;
```
See Advanced Settings below for information on manually managing the replication slot.
SELECT on the replicated tables
Polytomic reads each table in full when it's first synced, so the user needs the read permissions on every table in the publication.
Only for CDC from read replicas: hot_standby_feedback
hot_standby_feedback must be enabled. This causes the replica to notify the primary about queries and replication in order to avoid expiring WAL segments that are required by the replica. Ignore this setting if Polytomic will be reading from your primary database.

Set Polytomic to read your logical replication log

Once the previous steps are done, be sure to turn on the Use logical replication for bulk syncs setting and set the publication name in your PostgreSQL connection configuration:

Advanced settings

Mirroring the publication

When logical replication is on, Bulk Syncs from PostgreSQL offer a Mirror table and column selection with database publication option. When you turn on this option, Polytomic selects exactly the tables in your publication. On PostgreSQL 15 or later, it also selects only the columns in each table's column list. Polytomic updates the sync's selection when you change the publication.

While this option is on, you can't use the Automatically add new tables and Automatically add new fields options.

Specifying a replication slot

🚧
This option is not possible on read replicas
Manually specifying a replication slot is only supported when reading from the primary database. When using CDC with a read replica (PostgreSQL 16 and later), you should leave this option in its default empty state.

By default, Polytomic manages the replication slot for you. It creates a slot named polytomic_<sync ID without hyphens> when you enable a Bulk Sync from your PostgreSQL database, and drops the slot when the sync is disabled.

You can also manage the slot yourself. Create the slot outside of Polytomic, then specify the slot name under Advanced Settings in your sync configuration.

The replication slot must be created with the pgoutput plugin:

select pg_create_logical_replication_slot('polytomic', 'pgoutput');

🚧
Drop slots you no longer use
Polytomic never drops a slot you specify. If you stop using the slot, drop it yourself. An unused slot prevents PostgreSQL from removing WAL and can fill your disk.

Propagating deletes

Polytomic offers two options for propagating deletes during CDC replication:

Soft deletes
Hard deletes

By default, Polytomic will propagate deletes from your source as soft-deletes by marking the __polytomic_deleted_at column for each destination table with a datetime reflecting deletion time.

You can change this behaviour to propagating deletes as hard deletes instead. To do so, simply check the Propagate hard deletes instead of soft deletes checkbox in the Advanced settings option at the bottom of your sync configuration:

Automatic full syncs

Outside of a user-triggered resync, the only time Polytomic automatically runs a full sync for a table is when it's enabled in the Polytomic sync configuration for the first time.

Disabling a table that has completed syncing at least once and then enabling it again will resume CDC updates but does not result in an automatic resync. Polytomic takes a cautious stance after this sequence of events and leaves it up to you to trigger a manual resync on this table.

Tables without primary keys

Incremental CDC updates are not supported for tables without primary keys. When syncing from such tables, Polytomic offers a Mirror table without a unique identifier boolean option to decide behaviour:

When turned on, replicating the source table involves querying and copying it in one transaction. This may be infeasible if the table is large.

When turned off, replicating the source table results in the destination table being append-only, where each row update in the source table results in a new row in the destination table. This is recommended if the source table is large (common with event tables).

Polytomic ignores this setting when replicating tables with primary keys.

CDC from read replicas

It's possible to use CDC from a read replica with PostgreSQL 16 or later. Although these notes are present in the above list of setup instructions, we also list them here in a dedicated section:

The publication must be created on the primary, not the replica. The replica is read-only, so you must create the publication on the primary in order for it to be accessible from the replica.
hot_standby_feedback must be enabled. This causes the replica to notify the primary about queries and replication in order to avoid expiring WAL segments required by the replica.
The replication slot must be automatically managed by Polytomic (this is default behaviour). It cannot be explicitly specified in Polytomic's Bulk Sync configuration.

CDC replication only for Bulk Syncs

Requirements

Tables without primary keys

Partitioned tables

Existing grants on PostgreSQL 16 and later

Set Polytomic to read your logical replication log

Advanced settings

Mirroring the publication

Specifying a replication slot

This option is not possible on read replicas

Drop slots you no longer use

Propagating deletes

Automatic full syncs

Tables without primary keys

CDC from read replicas