Remote views – or – postgresql to postgresql cross database copy / SQL access

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;

Leave a Comment

Scroll to Top