PostgreSQL访问外部文件FILE_FDW

X数据库日记

于 2025-04-27 08:51:20 发布

阅读量284

点赞数 6

CC 4.0 BY-SA版权

分类专栏： PostgreSQL 文章标签： postgresql linux java database c++ github

本文链接：https://blog.csdn.net/Story_begins/article/details/147546921

PostgreSQL 专栏收录该内容

6 篇文章

订阅专栏

PostgreSQL访问外部文件FILE_FDW

1. 创建测试文件
2. 安装扩展包file\_fdw
3. 创建外部文件服务
4. 创建外部表

file_fdw模块提供外部数据包装器 file_fdw，它能被用来访问服务器的文件系统中的数据文件，或者在服务器上执行程序并读取它们的输出。数据文件或程序输出必须是能够被 COPY FROM读取的格式，详见 COPY。当前只能读取数据文件。

1. 创建测试文件

postgres=# create table t18 (id int,name varchar,age int,city varchar);
CREATE TABLE
postgres=# insert into t18 values (1,'A',21,'beijing'),(2,'B',21,'beijing'),(3,'C',22,'shanghai'),(4,'D',23,'beijing');
INSERT 0 4
postgres=# select * from t18;
 id | name | age |   city   
----+------+-----+----------
  1 | A    |  21 | beijing
  2 | B    |  21 | beijing
  3 | C    |  22 | shanghai
  4 | D    |  23 | beijing
(4 rows)

postgres=# \copy t18 to '/home/postgres/t18.csv' CSV header;
COPY 4
postgres=# drop table t18;
DROP TABLE
postgres=# \q
[postgres@pgdb ~]$ cat t18.csv 
id,name,age,city
1,A,21,beijing
2,B,21,beijing
3,C,22,shanghai
4,D,23,beijing
[postgres@pgdb ~]$

2. 安装扩展包file_fdw

pg软件编译安装扩展file_fdw

[root@pgdb file_fdw]# pwd
/pgsql/soft/postgresql-16.1/contrib/file_fdw
[root@pgdb file_fdw]# ls
data  expected  file_fdw--1.0.sql  file_fdw.c  file_fdw.control  Makefile  meson.build  sql
[root@pgdb file_fdw]# make
[root@pgdb file_fdw]# make install

数据库中安装file_fdw扩展包

postgres=# create extension file_fdw;
CREATE EXTENSION
postgres=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 file_fdw     | 1.0     | public     | foreign-data wrapper for flat file access
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.1     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

3. 创建外部文件服务

postgres=# create server file_fdw_server foreign data wrapper file_fdw;
CREATE SERVER
postgres=# \des+
                                                                    List of foreign servers
      Name       |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                      FDW options                       | Description 
-----------------+----------+----------------------+-------------------+------+---------+--------------------------------------------------------+-------------
 file_fdw_server | postgres | file_fdw             |                   |      |         |                                                        | 
 pg_fdw_server   | postgres | postgres_fdw         |                   |      |         | (host '192.168.1.100', port '5432', dbname 'testdb13') | 
(2 rows)

4. 创建外部表

postgres=# create foreign table file_fdw_t18 (id int,name varchar,age int,city varchar) server file_fdw_server options (filename '/home/postgres/t18.csv',format 'csv',header 'true');
CREATE FOREIGN TABLE
postgres=# \det+
                                                  List of foreign tables
 Schema |    Table     |     Server      |                           FDW options                            | Description 
--------+--------------+-----------------+------------------------------------------------------------------+-------------
 public | file_fdw_t18 | file_fdw_server | (filename '/home/postgres/t18.csv', format 'csv', header 'true') | 
(1 row)

postgres=# select * from file_fdw_t18 ;
 id | name | age |   city   
----+------+-----+----------
  1 | A    |  21 | beijing
  2 | B    |  21 | beijing
  3 | C    |  22 | shanghai
  4 | D    |  23 | beijing
(4 rows)

PostgreSQL.log也可以设置成csvlog模式，创建外部文件表，进行select访问。

vi postgresql.conf
#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.