[source-postgres] Logical replication not working on read replicas

[assaadhjb]

Connector Name

source-postgres

Connector Version

3.6.18

What step the error happened?

Syncs never start.

Relevant information

CDC Replication doesn't work on Postgres read replicas because it uses functions that can only be executed on the master - probably only one function: pg_current_wal_lsn().
I believe this can be solved quickly by replacing usage of pg_current_wal_lsn function with pg_last_wal_receive_lsn.

Postgres version : 16.13R2

Good to know: Debezium has solved this issue on their side very simply since V2.5, however the connector doesn't use Debezium for everything.

Relevant log output

java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: recovery is in progress Hint: WAL control functions cannot be executed during recovery.

Contribute

• Yes, I want to contribute

[assaadhjb]

@marcosmarxm it's not resolved until dest postgres uses latest version of CDK

[theyueli]

hi @assaadhjb , we tested the latest version against a Postgres replica; the sync succeeded. As mentioned in the PR above, we need one more change on the query that computes the current transaction id. Please have a try and let us know. Thanks!

[assaadhjb]

@theyueli I tested it and was successfully able to sync from a read-replica!
Thanks a lot for your patience and your effort - now the community is able to use Airbyte to sync from replicas in Postgres like it was possible previously for other flavors (MySQL ...)

[theyueli]

@theyueli I tested it and was successfully able to sync from a read-replica! Thanks a lot for your patience and your effort - now the community is able to use Airbyte to sync from replicas in Postgres like it was possible previously for other flavors (MySQL ...)

This is great to hear! Thanks!