Note: this was done using the “postgres” superuser, granting this kind of access to a non-superuser requires additional steps / permissions
Note: setting up cross database access requires setup on the “target” side, and does require that you specify a “user_mapping” that has/knows the credentials back on the source side
— ***** create foreign data wrapper on target side
— Extension: postgres_fdw
— DROP EXTENSION postgres_fdw;
CREATE EXTENSION postgres_fdw
SCHEMA “public”;
— ***** create foreign server on target side
— Foreign server: foreign_server
— DROP SERVER foreign_server;
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host ‘xxx.us-east-1.rds.amazonaws.com‘, port ‘5432’, dbname ‘mydatabase’, sslmode ‘require’);
— on target side – add a user mapping back to your source database with source DB credentials
CREATE USER MAPPING
FOR CURRENT_USER
SERVER foreign_server
OPTIONS (user ‘postgres’, password ‘yourPassword’);
— ***** example use from the target side:
— select example
select field1
from dblink(‘foreign_server’, $POSTGRESQL$
select field1
from myschema.table1 $POSTGRESQL$ ) as T(field1 text);
-— or insert on target side by selecting from source side
insert into myschema.table1
SELECT field1
from dblink(‘foreign_server’, $POSTGRESQL$
select field1
from myschema.table1 $POSTGRESQL$ ) as T( field1 text);
— create view example (on the target side)
create view myschema.table1 as select field1
from dblink(‘foreign_server’, $POSTGRESQL$
select field1
from myschema.table1 $POSTGRESQL$ ) as T(field1 text);
— example use of remote view from the target side after view creation
select * from myschema.table1;