PostgreSQL file_fdw的使用

1. 简介
　　file_fdw模块提供了外部数据封装器file_fdw，可以用来在服务器的文件系统中访问数据文件。数据文件必须是COPY FROM 可读的格式；参阅COPY获取细节。访问这样的数据文件当前只是可读的。
　　简单的说，就是创建一个表，表的数据来源于数据库之外的文件，而这个文件是通过copy命令导出去的。

　　使用这个封装器创建的外部表可以有下列选项：

参数 说明 filename 指定要读取的文件。这是必需的。必须是一个绝对路径名。 format 指定文件的格式，与COPY的FORMAT选项相同。 header 指定文件是否有标题行，与COPY的HEADER选项相同。 delimiter 指定文件的分隔符，与COPY的DELIMITER选项相同。 quote 指定文件的引用字符，与COPY的QUOTE选项相同。 escape 指定文件的逃逸字符，与COPY的ESCAPE选项相同。 null 指定文件的null字符串，与COPY的NULL选项相同。 encoding 指定文件的编码，与COPY的ENCODING选项相同。

2. 添加file_fdw扩展。

postgres=# create extension file_fdw ;CREATE EXTENSIONpostgres=#postgres=# \dx --查看所有的外部扩展                            List of installed extensions   Name   | Version |   Schema   |                   Description                    ----------+---------+------------+-------------------------------------------------- file_fdw | 1.0     | public     | foreign-data wrapper for flat file access hstore   | 1.2     | public     | data type for storing sets of (key, value) pairs plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language(3 rows)

3. 测试file_fdw

　3.1 创建测试表，添加测试数据

postgres=# create  table tb10(id integer,name character varying,passworld character varying);CREATE TABLEpostgres=# postgres=# insert into tb10 select generate_series(1,50),'john',md5(random()::text);INSERT 0 50

　3.2 通过copy拷贝成文件

postgres=# copy tb10 to '/home/postgres/tb10.csv';COPY 50

　3.3 创建SERVER（外部服务器）

postgres=# create server server_file_fdw foreign data wrapper file_fdw;CREATE SERVERpostgres=# \des              List of foreign servers      Name       |  Owner   | Foreign-data wrapper -----------------+----------+---------------------- server_file_fdw | postgres | file_fdw(1 row)

注：以 postgres 用户连接虚拟机上的库 mydb, 并创建 SERVER;Server 可以理解为外部服务器，是本地库访问外部数据的桥梁。

　3.4 创建外部表

postgres=# create foreign table foreign_tb10 (id integer,name character varying,password character varying)server server_file_fdw  options (filename '/home/postgres/tb10.csv');CREATE FOREIGN TABLE

备注：这里创建外部表，并指定外部 Server 为 server_file_fdw，在测试过程中，发现外部表有很多限制,目前发现了以下：

• default 值不支持；
• 索引不支持；

postgres=# \d foreign_tb10           Foreign table "public.foreign_tb10"  Column  |       Type        | Modifiers | FDW Options ----------+-------------------+-----------+------------- id       | integer           |           |  name     | character varying |           |  password | character varying |           | Server: server_file_fdwFDW Options: (filename '/home/postgres/tb10.csv')

CREATE FOREIGN TABLEpostgres=# select * from foreign_tb10 order by id limit 10; id | name |             password             ----+------+----------------------------------  1 | john | 40f328dd6d918fc2565325917e2c0fa5  2 | john | 2fe543e9983e6fbbcbee8b78045c44fc  3 | john | 9315889aac4098a7f26c948a6e3be3da  4 | john | 0bf9dc150651eaa358568b94451a9796  5 | john | 8c44c90724f5bdf4fd5da4e959e1700f  6 | john | 0470c540c0273519607e63bedaf939ca  7 | john | a84a0bfdb0f0712ff4d2fc418128aa02  8 | john | 06acd231204e572dc96af794c4b8bc44  9 | john | fe52c4a5e449d22a1339c0767efb56b6 10 | john | c1f228300368b320192d0e6939f6ee80(10 rows)

　3.5 执行计划

postgres=# explain select * from foreign_tb10 order by id limit 10;                                  QUERY PLAN                                  ------------------------------------------------------------------------------ Limit  (cost=3.55..3.58 rows=10 width=68)   ->  Sort  (cost=3.55..3.61 rows=21 width=68)         Sort Key: id         ->  Foreign Scan on foreign_tb10  (cost=0.00..3.10 rows=21 width=68)               Foreign File: /home/postgres/tb10.csv               Foreign File Size: 2041(6 rows)

可以看到，数据是从外部文件扫描获取，文件的位置，大小也有展示。

参考：
http://francs3.blog.163.com/blog/static/40576727201181513126498/
http://www.postgresql.org/docs/current/static/file-fdw.html