X pg_log,数据库日志表postgresqllog

pg_log,数据库日志表postgresqllog

create database logdata;

CREATE TABLE postgres_log
(
log_time timestamp(3) with time zone, --日志生成时间
user_name text, --登陆用户名
database_name text, --数据库名
process_id integer,
connection_from text,--登陆ip
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,--应用名称
PRIMARY KEY (session_id, session_line_num)
);

csv日志中对应列的解释：
A：日志生成时间
B：登陆用户名
C：数据库名
E：登陆ip
L：日志级别
M：日志错误代码
N：异常信息
O：异常详细信息
T：查询脚本
W：应用名称

COPY postgres_log FROM 'd:\datafile\postgresql-2018-04-17_094948.csv' WITH csv;

将数据库日志拷贝到表中

postgres=# create database test;

CREATE TABLE pg_log test-# ( test(# log_time timestamp(3) with time zone, test(# user_name text, test(# database_name text, test(# process_id integer, test(# connection_from text, test(# session_id text, test(# session_line_num bigint, test(# command_tag text, test(# session_start_time timestamp with time zone, test(# virtual_transaction_id text, test(# transaction_id bigint, test(# error_severity text, test(# sql_state_code text, test(# message text, test(# detail text, test(# hint text, test(# internal_query text, test(# internal_query_pos integer, test(# context text, test(# query text, test(# query_pos integer, test(# location text, test(# application_name text, test(# PRIMARY KEY (session_id, session_line_num) test(# );

logdata=#