From one of PostgreSQL AWS RDS instance, shot to access tables of far RDS instance with listed below command:
testuser=>select * indigenous dblink("dbname=testrds port=5555 host=testrds.rds.amazonaws.com user=testuser password=passwd", "SELECT cust_id, cust_name from iamesh.customer" ) together cust ( id float, surname varchar(50));ERROR: role dblink(unknown, unknown) does not existLINE 1: select * from dblink("dbname=testrds port=5555 host=testrds.HINT: No role matches the provided name and also argument types. You can need to add explicit form casts.SQL state: 42883Character: 15Show present installed extensions:
testuser=> pick * from pg_extension;extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition--------+----------+--------------+----------------+------------+-----------+--------------plpgsql | 10 | 11 | f | 1.0 | |(1 rows)Show obtainable extensions, dblink extension is obtainable to it is in installed:
testuser>SELECT * from pg_available_extensions order by 1; surname | default_version | installed_version | comment-------------------------+-----------------+-------------------+----.....dblink |1.2 |plpgsql |1.0 |1.0......We have the right to see dblink extension is NOT installed yet. To create “dblink” extension:
testuser>create extension dblink; ERROR: permission refuse to develop extension "dblink"HINT: must be superuser to produce this extension.Logon together super user, and retry again.
rdsdba=> create extension dblink;CREATE EXTENSIONTry to accessibility remote tables of remote instance again, and it is successful.
testuser=>select * indigenous dblink("dbname=testrds port=5555host=testrds.rds.amazonaws.com user=testuser password=passwd", "SELECT cust_id, cust_name native iamesh.customer" ) together cust ( id float, surname varchar(50)); id | name------------+------------------------------------- 2001873700 | James bond 2001873800 | David Lee......
