postgres_attach() error when connecting to read-only replica

[loicalleyne]

I'm trying to connect to a postgres read-only replica and getting the following error. The replica is using log shipping to prevent replication delays from cascading up to the master DB in production.

call postgres_attach('host=< IP address > port=5432 user=< USER > dbname=< DB NAME > sslmode=require', source_schema='public', sink_schema='abc');
Error: IO Error: Unable to query Postgres: ERROR:  cannot assign TransactionIds during recovery
 ERROR:  cannot assign TransactionIds during recovery

Is this something that can be supported with the pg library being used? Connecting to the same DB from the same machine with psql works fine.

[seanabraham]

Seeing the exact same issue with a postgres read replica, I don't have much to add except that the same error occurs with a SELECT * from postgres_scan(...) as well.

[Mytherin]

Thanks for the report! This should be addressed in #112. I've tested this with a read-replica through AWS RDS but it would be great if you could verify that it resolves the issue.

[seanabraham]

Amazing, thank you @Mytherin. How does this get deployed so to speak? I see that DuckDB bundles this extension and presumably this commit hash needs to be bumped: https://github.com/duckdb/duckdb/blob/5ec85a719940a9fade15c38e7601712e9cef58d8/.github/config/extensions.csv#L12

Is there a way to test this version of postgres_scanner locally somehow?

[Mytherin]

You can compile the postgres_scanner by cloning the repo and running make. The extension can then be found in build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension which can be installed using the SQL command:

FORCE INSTALL 'build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension';