Real-time Change Data Capture from Postgres 16 Read Replicas

At PeerDB, we’re committed to providing a fast and simple experience to replicate data from Postgres to Data Warehouses, Queues and Storage. We're excited to announce a new feature: Change Data Capture (CDC) from Postgres Read Replicas.

Postgres 16 introduced logical decoding via Standbys (a.k.a. Read Replicas). Behind the scenes, PeerDB relies on this feature to enable CDC / replication from Postgres Read Replicas.

In this blog, we will cover:

The impact of this feature on customer workloads.
Our learnings while building this feature. This would be helpful for any application that relies on Postgres logical decoding and wants to start using Read Replicas instead of Primaries.

Eliminate risk on your Primary Postgres Database

A common concern for users replicating data from a Primary Postgres database is the potential risk it poses to the database due to -

Addition Load on the Primary - Logical decoding can incur additional load and resource consumption on the Primary.
Outages due to replication slot growth: Logical replication slots if not consumed properly, can grow in size leading to disks getting filled and resulting in outages.

With this new feature, you can setup a Postgres Read Replica and start replicating data from it instead of the Primary. It completely eliminates the risk of affecting your Primary database.

Logical Decoding on Postgres Standbys, Our Learnings

Before introducing this feature, we checked if PeerDB could use a Postgres Standby as a source without any code changes. But that was not the case. It required a few lines of code change to support replication through Standbys.

We're eager to share our learnings on which Postgres replication-related features worked well on Read Replicas and which ones needed to be worked around -

✅ CREATE_REPLICATION_SLOT works as expected on Read Replicas: Behind the scenes we use CREATE_REPLICATION_SLOT to create the replication slot and that code path required no changes.

       CREATE_REPLICATION_SLOT peerflow_slot_test_replica_1 LOGICAL pgoutput;
                 slot_name           | consistent_point |    snapshot_name    | output_plugin
       ------------------------------+------------------+---------------------+---------------
        peerflow_slot_test_replica_1 | 4/A40005A8       | 0000001B-00000016-1 | pgoutput
       (1 row)

✅ SNAPSHOT can be created and used on Read Replicas: For a consistent initial load from Postgres to a target, we use the snapshot generated by CREATE_REPLICATION_SLOT (see snapshot_name column). That worked as expected and required no code change.
1. ```
       BEGIN;
       SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
       SET TRANSACTION SNAPSHOT '0000001B-00000016-1';
       SELECT * FROM <test_table>;
       END;
```

✅ START_REPLICATION worked as expected on Read Replicas: START_REPLICATION is used for consuming the logical replication slot. This worked as expected.

       START_REPLICATION SLOT peerflow_slot_test_replica 
       LOGICAL 3/9000C8D1 (proto_version '1', 
       publication_names 'pub_all')

❌ Publications cannot be created on the Read Replica: PeerDB couldn't automatically create the publication on the standby. So we had to create the publication manually on the primary and input that to the MIRROR (a.k.a. replication).
❌ WAL control functions cannot run on Read Replicas: The use of pg_current_wal_lsn() to monitor the slot size failed on the replica, showing an error related to recovery in progress. We overcame this by implementing a conditional query using CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() ELSE pg_current_wal_lsn(), which employs pg_last_wal_receive_lsn() END for replicas.

Conclusion

Thank you for reading our blog! If you want to try out this new feature of replicating data from Postgres Read Replicas to Data Warehouses, Queues or Storage:

You can get in touch with us by filling out the Request for Access form on our website.
Alternatively, visit our GitHub repository to Get Started.