【赵渝强老师】在PostgreSQL中使用file_fdw访问外部文件系统

PostgreSQL的file_fdw扩展允许直接从数据库中来访问服务器的文件系统中的文件，而文件的格式要求为text、csv或者 binary。下面通过具体的操作来演示如何使用file_fdw扩展。

（1）进入PostgreSQL源码目录下的contrib/file_fdw目录，编译并安装file_fdw扩展。

cd postgresql-15.3/contrib/file_fdw/
make
make install

# 编译完成后，会在当前目录下生成file_fdw.so文件，
# 并自动将编译好的文件拷贝到PostgreSQL安装目录下。

（2）修改postgresql.conf文件中的shared_preload_libraries参数。

shared_preload_libraries = 'file_fdw'

（3）重启PostgreSQL数据库实例。

bin/pg_ctl -D data/ -l logfile restart

（4）创建file_fdw扩展。

postgres=# create extension file_fdw ;

（5）查看PostgreSQL数据库中已安装的扩展。

postgres=# select * from pg_extension;

# 输出信息如下：
-[ RECORD 1 ]--+---------
oid            | 13566
extname        | plpgsql
extowner       | 10
extnamespace   | 11
extrelocatable | f
extversion     | 1.0
extconfig      | 
extcondition   | 
-[ RECORD 2 ]--+---------
oid            | 16628
extname        | file_fdw
extowner       | 10
extnamespace   | 2200
extrelocatable | t
extversion     | 1.0
extconfig      | 
extcondition   | 

（6）基于file_fdw创建外部文件服务service_file。

postgres=# create server service_file foreign data wrapper file_fdw;

（7）查看当前数据库中已创建的外部服务。

postgres=# \des

# 输出的信息如下：
            List of foreign servers
     Name     |  Owner   | Foreign-data wrapper 
--------------+----------+----------------------
 service_file | postgres | file_fdw
(1 row)

（8）创建基于file_fdw的外部表。

postgres=# create foreign table ft_emp(
  empno int,
  ename varchar(10),
  job varchar(10),
  mgr int,
  hiredate varchar(10),
  sal int,
  comm int,
  deptno int)
  server service_file options 
  (filename '/home/postgres/emp.csv',format 'csv');
  
# emp.csv文件参考，文件中的数据如下：
7369,SMITH,CLERK,7902,1980/12/17,800,0,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,0,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,0,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,0,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,0,20
7839,KING,PRESIDENT,-1,1981/11/17,5000,0,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,0,20
7900,JAMES,CLERK,7698,1981/12/3,950,0,30
7902,FORD,ANALYST,7566,1981/12/3,3000,0,20
7934,MILLER,CLERK,7782,1982/1/23,1300,0,10

（9）查看外部表ft_emp的数据。

postgres=# select * from ft_emp;

# 输出的信息如下：
 empno | ename  |    job    | mgr  |  hiredate  | sal  | comm | deptno 
-------+--------+-----------+------+------------+------+------+-----
  7369 | SMITH  | CLERK     | 7902 | 1980/12/17 |  800 |    0 |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981/2/20  | 1600 |  300 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981/2/22  | 1250 |  500 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981/4/2   | 2975 |    0 |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981/9/28  | 1250 | 1400 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981/5/1   | 2850 |    0 |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981/6/9   | 2450 |    0 |     10
  7788 | SCOTT  | ANALYST   | 7566 | 1987/4/19  | 3000 |    0 |     20
  7839 | KING   | PRESIDENT |   -1 | 1981/11/17 | 5000 |    0 |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981/9/8   | 1500 |    0 |     30
  7876 | ADAMS  | CLERK     | 7788 | 1987/5/23  | 1100 |    0 |     20
  7900 | JAMES  | CLERK     | 7698 | 1981/12/3  |  950 |    0 |     30
  7902 | FORD   | ANALYST   | 7566 | 1981/12/3  | 3000 |    0 |     20
  7934 | MILLER | CLERK     | 7782 | 1982/1/23  | 1300 |    0 |     10
(14 rows)