centos 6.8 + pgsql 9.6 + file_fdw

使用 file_fdw 读取 pglog，以便实时监控日志。

创建 extension、server

CREATE EXTENSION file_fdw;

CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;

获取最新日志文件

select t00.*
from (

select t1.file,       t1.file_ls,       (pg_stat_file(t1.file)).modification as last_update_time,       round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb  from (select dir||'/'||pg_ls_dir(t0.dir) as file,               pg_ls_dir(t0.dir) as file_ls          from ( select setting as dir from pg_settings where name='log_directory'                ) t0        ) t1  where 1=1 order by (pg_stat_file(file)).modification desc) t00

where 1=1
and t00.file_ls like ‘%.csv’
limit 1
;

创建外部表

select name,setting
from pg_settings where name ~ ‘log’;

CREATE FOREIGN TABLE pglog_current (
log_time timestamp(3) without 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 file_fdw_server
options(filename ‘/var/log/postgresql/postgresql-2017-08-29_000000.csv’,format ‘csv’)
;

查询外部表

select count(1)
from pglog_current
where 1=1
;

select plc.*
from pglog_current plc
where 1=1
order by plc.log_time
;

可以和德哥一样写个存储过程来判断和创建。
参考：http://blog.163.com/digoal@126/blog/static/16387704020121023105322442/