From one of PostgreSQL AWS RDS instance, try to access tables of remote RDS instance with below command:
testuser=>select * from dblink('dbname=testrds port=5555
host=testrds.rds.amazonaws.com
user=testuser password=passwd', 'SELECT cust_id, cust_name
from iamesh.customer' ) as cust ( id float, name varchar(50));
ERROR: function dblink(unknown, unknown) does not exist
LINE 1: select * from dblink('dbname=testrds port=5555 host=testrds.
HINT: No function matches the given name and argument types.
You might need to add explicit type casts.
SQL state: 42883
Character: 15
Show current installed extensions:
testuser=> select * from pg_extension; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition --------+----------+--------------+----------------+------------+-----------+-------------- plpgsql | 10 | 11 | f | 1.0 | | (1 rows)
Show available extensions, dblink extension is available to be installed:
testuser>SELECT * FROM pg_available_extensions order by 1;
name | default_version | installed_version | comment
-------------------------+-----------------+-------------------+----
...
..
dblink |1.2 |
plpgsql |1.0 |1.0
...
..
.
We can see dblink extension is NOT installed yet. To create “dblink” extension:
testuser>create extension dblink; ERROR: permission denied to create extension "dblink" HINT: Must be superuser to create this extension.
Logon as super user, and retry again.
rdsdba=> create extension dblink; CREATE EXTENSION
Try to access remote tables of remote instance again, and it is successful.
testuser=>select * from dblink('dbname=testrds port=5555
host=testrds.rds.amazonaws.com
user=testuser password=passwd', 'SELECT cust_id, cust_name
from iamesh.customer' ) as cust ( id float, name varchar(50));
id | name
------------+-------------------------------------
2001873700 | James Bond
2001873800 | David Lee
...
..
.
Make DBA Life Easy 