PG fdw 跨库建立外部表 （连接其它PG库或其它数据库 ）

fdw是foreign-data wrapper的一个简称，可以叫外部封装数据
PostgreSQL的数据库逻辑上是相互独立的，和Oracle类似，如果要访问其他数据库，需要做跨库操作，Postgres本身提供了一些扩展，比如dblink,pgsql_fdw等，高版本的建议使用postgres_fdw，也就是pgsql_fdw的升级版

1安装fdw

fdw工具是pg自带的扩展工具，可以直接到postgres的解压目录安装

路径是postgresql-9.4.4/contrib/file_fdw 10版本之后不需要安装

　　make

　　make install

安装后进入数据库用select * from pg_available_extension 查看是否安装完成 postgres_fdw

2.创建库连接

create extension postgres_fdw; 建立fdw扩展工具

创建远程服务器，这里需要定义主机地址，数据库名和端口

create server fore_server foreign data wrapper postgres_fdw options(host '1.1.1.1',port '5432',dbname 'postgres');

指定连接远程数据库的用户，和创建的远程服务器一致
create user mapping for postgres server server_remote_farr options(user 'postgres',password 'postgres');

创建外部表

CREATE FOREIGN TABLE F_A
(id int)
 server fore_server options (schema_name 'postgres',table_name 'A');

连接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');

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

3.使用外部表（连接文件）

需要先安装file_fdw，一般是进到PostgreSQL的源码包中的contrib/file_fdw目录下，执行：

make
make install

然后进入数据库中，执行以下SQL把file_fdw安装上：

CREATE EXTENSION file_fdw;
CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;

Pg连接外部文件

1. 安装file_fdw

需要先安装file_fdw，一般是进到PostgreSQL的源码包中的contrib/file_fdw目录下，执行：

make
make install

然后进入数据库中，执行以下SQL把file_fdw安装上：

CREATE EXTENSION file_fdw;
CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;

2. 建立外部表

 

就以常见emp表为例；

 

create foreign table EMP

(

  empno    integer not null,

  ename    VARCHAR(10),

  job      VARCHAR(9),

  mgr      integer,

  hiredate DATE,

  sal      NUMERIC(6,2),

  comm     NUMERIC(6,2),

  deptno   integer

)SERVER file_fdw_server

OPTIONS (format 'csv',header 'true',filename '/home/postgres/emp.log', delimiter ',', null '');

 

--后面options里面参数的说明
--filename后面是文件名和绝对路径
--format是格式,csv是逗号分隔,text表示是tab分隔的方式
--delimiter是分隔符--header表示第一行数据是否需要;为true表示不需要；默认为false；为需要
--null表示空数据的转化处理,例子中字段''将转化为null