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

*
Author trident-gaming.netPosted on February 28, 2019July 15, 2020Categories Extension, PostgreSQLTags Character: 15, create EXTENSION, create extension dblink;, ERROR: function dblink(unknown unknown) does no exist, ERROR: permission refuse to develop extension "dblink", HINT: need to be superuser to produce this extension, HINT: No duty matches the provided name and also argument types. You could need to add explicit kind casts., line 1: pick * native dblink("dbname=, select * native dblink("dbname=, pick * native pg_extension, SQL state: 42883Leave a discuss ERROR: duty dblink(unknown, unknown) does notexist