PostgreSQL file_fdw详解及使用实践(使用SQL查询数据库日志)

本文链接：https://blog.csdn.net/weixin_39540651/article/details/106553057

PostgreSQL的file_fdw模块允许我们直接从数据库中来访问服务器的文件系统中的文件，不过这些文件必须是能够被COPY FROM读取的格式。

通过file_fdw创建外部表时可指定的参数有：

filename：指定要被读取的文件。必须是一个绝对路径名。必须指定filename或program，但不能同时指定两个。
program：指定要执行的命令。该命令的标准输出将被读取，就像使用COPY FROM PROGRAM一样。必须指定program
或filename，但不能同时指定两个。
format：指定数据的格式，和COPY的FORMAT选项相同。
header：指定数据是否具有一个头部行，和COPY的HEADER选项相同。
delimiter：指定数据的定界符字符，和COPY的DELIMITER选项相同。
quote：指定数据的引用字符，和COPY的QUOTE选项相同。
escape：指定数据的转义字符，和COPY的ESCAPE选项相同。
null：指定数据的空字符串，和COPY的NULL选项相同。
encoding：指定数据的编码，和COPY的ENCODING选项相同。

例子：
安装模块：

bill=# create extension file_fdw ;
CREATE EXTENSION

创建server：

bill=# create server fdw_server1 foreign data wrapper file_fdw;
CREATE SERVER

创建外部表：

bill=# create foreign table fdw_emp(
bill(# emp_id numeric(6,0),
bill(# ename varchar(45),
bill(# dept_id numeric(4,0),
bill(# hire_date date,
bill(# sal numeric(8,2)
bill(# )SERVER fdw_server1
bill-# OPTIONS (filename '/home/pg13/emp.csv', format 'csv' );
CREATE FOREIGN TABLE

查看外部表：
可以看到外部表中读取到了服务器上emp.csv文件里面的数据了。

bill=# select * from fdw_emp;
 emp_id | ename | dept_id | hire_date  |   sal   
--------+-------+---------+------------+---------
    101 | Tom   |      20 | 1989-09-21 | 2000.00
    102 | Mike  |      20 | 1993-01-13 | 8000.00
    120 | John  |      50 | 1996-07-18 | 1000.00
    121 | Joy   |      50 | 1997-04-10 | 4000.00
    122 | Rich  |      50 | 1995-05-01 | 3000.00
    123 | Kate  |      50 | 1997-10-10 | 5000.00
    124 | Jess  |      50 | 1999-11-16 | 6000.00
    100 | Stev  |      10 | 1990-01-01 | 7000.00
(8 rows)

从上例中我们发现使用file_fdw可以很轻松的读取服务器上的数据文件里面的内容。那么通过file_fdw我们可以实现一个很常见的功能：利用file_fdw在数据库中创建外部表来读取数据库日志里面的信息。

首先我们还是需要创建一个外部服务器：

bill=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

然后创建外部表：

CREATE FOREIGN TABLE pglog (
log_time timestamp(3) with time zone, user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone, virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) SERVER pglog
OPTIONS ( filename '/home/pg13/pgdata/log/pglog.csv', format 'csv' );

但是需要注意下上面我们指定的文件名是pglog.csv，但是在实际的log目录下面你可能看到的日志是这样的：
在这里插入图片描述

可以发现，上面我们创建的外部表只能访问一个日志文件，并且需要指定绝对路径，如果日志文件中像上图所示有多个LOG文件，而且绝对路径不固定又该怎么办呢？

首先我们需要先配置log相关的参数，例如：

log_destination = 'csvlog'    
logging_collector = on    
log_directory = 'log'    
# 保留一周，每天一个文件，当ROTATE时间周期到达后，重复使用同一个文件前truncate文件内容    
log_filename = 'postgresql-%a.log'    
log_truncate_on_rotation = on    
log_rotation_age = 1d     
  
# 如果文件达到100MB，切换到下一个文件（如果文件名与当前已有文件名同名，则APPEND，而不会truncate这个文件）    
# 所以以上配置，1天内文件大小可以超过100MB。   
log_rotation_size = 100MB

然后我们前面介绍了file_fdw创建外部表有一个program参数，可以用来调用操作系统命令，结果作为file_fdw的输入。

比方说这样我们就可以从外部表中访问所有日志的信息：

bill=# CREATE FOREIGN TABLE pglog (  
bill(#   log_time timestamp(3) with time zone,  
bill(#   user_name text,  
bill(#   database_name text,  
bill(#   process_id integer,  
bill(#   connection_from text,  
bill(#   session_id text,  
bill(#   session_line_num bigint,  
bill(#   command_tag text,  
bill(#   session_start_time timestamp with time zone,  
bill(#   virtual_transaction_id text,  
bill(#   transaction_id bigint,  
bill(#   error_severity text,  
bill(#   sql_state_code text,  
bill(#   message text,  
bill(#   detail text,  
bill(#   hint text,  
bill(#   internal_query text,  
bill(#   internal_query_pos integer,  
bill(#   context text,  
bill(#   query text,  
bill(#   query_pos integer,  
bill(#   location text,  
bill(#   application_name text  
bill(# ) SERVER pglog  
bill-# OPTIONS ( program 'find $PGDATA/log -type f -name "*.csv" -exec cat {} \;', format 'csv' );  
CREATE FOREIGN TABLE

查看：

bill=>select count(*) from pglog; 
 count 
-------
 89262
(1 row)

又或者我们可以指定时间只访问最近一周的数据：

find $PGDATA/log -type f -name "*.csv" -mtime -7 -exec cat {} \;

除此之外，我们还可以为每个日志文件创建一个外部表，在外部表上创建视图等等，这样更加方便我们管理以及查询。

PG13注意事项：
因为pg13中在日志中新增加了记录backend type的数据，所有pg13中对应的表需要加上backend type这一列，如下所示：
在这里插入图片描述

CREATE FOREIGN TABLE pglog (  
  log_time timestamp(3) with time zone,  
  user_name text,  
  database_name text,  
  process_id integer,
  connection_from text,
  session_id text,  
  session_line_num bigint,  
  command_tag text,  
  session_start_time timestamp with time zone,  
  virtual_transaction_id text,  
  transaction_id bigint,  
  error_severity text,  
  sql_state_code text,  
  message text,  
  detail text,  
  hint text,  
  internal_query text,  
  internal_query_pos integer,  
  context text,  
  query text,  
  query_pos integer,  
  location text,  
  application_name text,
  backend_type text  
) SERVER pglog  
OPTIONS ( program 'find $PGDATA/log -type f -name "*.csv" -mtime -1 -exec cat {} \;', format 'csv' );