Pg连接外部文件

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;

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