PostgreSQL file_fdw的使用

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

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

2. 添加file_fdw扩展。

postgres=# create extension file_fdw ;
CREATE EXTENSION postgres=# 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 TABLE postgres=# 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 SERVER postgres=# 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,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_fdw
FDW Options: (filename '/home/postgres/tb10.csv')

CREATE FOREIGN TABLE
postgres=# 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