Unable to get dblink to work on read replica

Question

We have 2 databases on AWS RDS, OMS and SFMS, with each database having its own read replica. We use dblink in SFMS to fetch data of Table A from OMS. It works perfectly on my SFMS db instance with Master role, but get an ERROR: could not establish connection on our read replica DB.

Here is how I have setup the dblink:

SELECT * FROM dblink( 'dbname=<DB End Point> user=<username> password=<password>', 'SELECT id, <Other fields> from A') AS oms_A (id int, <Remaining Schema>)

I can always create a materialized view on SFMS to get it to work. Is their some mistake that I am making while setting up DBLink to use it on a read replica instance?

Answer 1

This works on Aiven's PostgreSQL service. Please checkout aiven.io.

To set it up you first need to create the extension on the master server with 'CREATE EXTENSION dblink;'

The foreign server definitions and user name mappings also have to be created on the master which will then replicate them to the read-only replicas.

Once those are setup you can do things like: SELECT dblink_connect('myconn', 'db2remote'); and SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(id int); on the read-replica.

Hope this helps.