1

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?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Shubham
  • 352
  • 3
  • 14
  • check logs in replica for errors?.. – Vao Tsun Oct 10 '17 at 09:33
  • `2017-10-10 10:50:50 UTC:static-tataidc.co.in(50130):postgres@sfms_production:[19804]:ERROR: could not establish connection 2017-10-10 10:50:50 UTC:static-tataidc.co.in(50130):postgres@sfms_production:[19804]:DETAIL: could not connect to server: Connection timed out Is the server running on host () and accepting TCP/IP connections on port 5432? ` but I am able to successfully run it on my master RDS instance. – Shubham Oct 10 '17 at 10:52
  • there's no DETAIL line?.. please try smae in psql (not sure which client you use) - aha - timed out. now makes sense – Vao Tsun Oct 10 '17 at 10:58
  • check security groups then?.. – Vao Tsun Oct 10 '17 at 10:59

1 Answers1

-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.

PremN
  • 1