centos 6.8 + postgresql 9.6 + file_fdw
使用 file_fdw 读取 pglog,以便实时监控日志。
创建 extension
CREATE EXTENSION file_fdw;
创建 server
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.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/
https://www.postgresql.org/docs/current/static/sql-createforeigntable.html
https://www.postgresql.org/docs/current/static/file-fdw.html