PostgreSQL的 fdw 跨库使用

 

create extension postgres_fdw; 
create server server_remote_farr foreign data wrapper postgres_fdw options(host '172.16.3.59',port '1921',dbname 'postgres');
create user mapping for android_market server server_remote_farr options(user 'skydata_test',password 'skydata_test');
CREATE FOREIGN TABLE tbl_kenyon(id int,create_user int,model_name varchar,flag int) server server_remote_farr options (schema_name 'metadata',table_name 'guide_model');

select * from tbl_kenyon;
drop foreign table tbl_kenyon;

 

Mysql:
CREATE SERVER mysql_svr  FOREIGN DATA WRAPPER mysql_fdw  OPTIONS (host '192.168.173.43', port '3306');
CREATE USER MAPPING FOR local SERVER mysql_svr  OPTIONS (username 'usr_fdw', password '123456');
CREATE FOREIGN TABLE pg_mysql_tbl_fdw (id integer,vname text)
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw',table_name 'tbl_fdw');

Oracle:
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
          OPTIONS (dbserver '//dbserver.mydomain.com/ORADB');
          ////dbserver.mydomain.com/ORADB指的是tns的名称
GRANT USAGE ON FOREIGN SERVER oradb TO pguser;
CREATE USER MAPPING FOR pguser SERVER oradb  OPTIONS (user 'orauser', password 'orapwd');
CREATE FOREIGN TABLE oratab (
          id        integer           OPTIONS (key 'true')  NOT NULL,
          text      character varying(30),
          floating  double precision  NOT NULL
       ) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');


Sqlserver：
CREATE SERVER mssql_svr
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername '127.0.0.1', port '1433', database 'tds_fdw_test');

CREATE USER MAPPING FOR postgres
    SERVER mssql_svr 
    OPTIONS (username 'sa', password 'xxxx');

    
CREATE FOREIGN TABLE mssql_table (
    id integer,
    data varchar)
    SERVER mssql_svr
    OPTIONS (table_name 'dbo.mytable');


alter server:
ALTER SERVER foo  OPTIONS (SET host 'baz');
alter user mapping:
ALTER USER MAPPING FOR bob SERVER foo OPTIONS (SET password 'public');
alter foreign table:
ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');