Question 1

What does change data capture require on the PostgreSQL side?

Accepted Answer

Native logical replication: a publication covering the tables you want, a replication slot per bulk sync, and the built in pgoutput plugin. The server needs wal_level set to logical and spare capacity in max_replication_slots, and the connecting user needs the REPLICATION attribute, or rds_replication on RDS, alongside normal SELECT rights. Polytomic usually creates and removes the slot for you. Worth knowing before you start: a slot retains write ahead log while its consumer is behind, so a long paused sync holds disk on the source.

Question 2

Do our tables need a primary key?

Accepted Answer

Not strictly, and this is more forgiving than the documentation suggests. A primary key works, but a selectable unique index is also recognised as a unique identifier. Tables with neither fall back to an incremental xmin read, which behaves like append only data, or to a full read each run if you mirror the table without a unique identifier. PostgreSQL still needs an appropriate replica identity for updates and deletes to be usable, so the publication and the replica identity have to agree.

Question 3

Can we read from a read replica?

Accepted Answer

Yes for ordinary full scans and for xmin reads. Logical change capture from a standby needs PostgreSQL 16 or later, with the publication still living on the primary, hot_standby_feedback enabled, and Polytomic managing the slot rather than you supplying one. Manually supplied slots are primary only. The connector does not check the version before attempting replication, so confirm it yourself rather than relying on an error.

Question 4

If a row is deleted in Postgres, how does that show up in Redshift?

Accepted Answer

This is the most common question customers ask about this pairing. With logical replication the delete arrives as an event, and Redshift records it by default as a soft delete against a deletion timestamp column, so the row stays queryable and you can see when it went. Turning on propagate hard deletes removes the row physically instead. The important caveat is that the fallback paths behave differently: tracking column and xmin incremental reads do not capture deletes at all, so pair those with a scheduled full ETL read if reconciliation matters.

Question 5

Why replicate PostgreSQL into a data warehouse?

Accepted Answer

Because analytical queries and an operational database want opposite things. Aggregations across a large table are expensive on a server that is also serving your application, and joining that data to CRM, billing or marketing systems is awkward from inside the database. ETL into a data warehouse moves the analytical load off production and lands raw application data in Redshift, which is where most teams then build their transformed reporting tables.

Sync PostgreSQL to Redshift

Sync PostgreSQL to Redshift with Polytomic

Deletes carried by replication

Keys decide the read mode

Read from a standby

Or sync Redshift to PostgreSQL

Trusted by businesses worldwide

Step-by-step integration guide

Connect PostgreSQL in Polytomic

Connect Redshift in Polytomic

Create a sync between PostgreSQL and Redshift

Map fields and enable the sync

Frequently asked questions

The sync and ETL platform that does it all

Powerful data sync

Advanced analytics

Enterprise security

Easy setup

Ready to connect PostgreSQL with Redshift?