北京事务所网页设计_二维码转换成短链接在线_东莞营销网站建设_乐云seo

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 | beijing2 | B    |  21 | beijing3 | C    |  22 | shanghai4 | 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=# \dxList of installed extensionsName     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------file_fdw     | 1.0     | public     | foreign-data wrapper for flat file accessplpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural languagepostgres_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 serversName       |  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 tablesSchema |    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 | beijing2 | B    |  21 | beijing3 | C    |  22 | shanghai4 | 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.