mysql外部表不是预期格式_postgresql 日志管理-外部表

postgresql的日志管理比较灵活，可以设置多种格式

log_destination='csvlog'

;调成csvlog后log_line_prefix无效；然后自己写个脚本就可以在数据库中方便的调用了，我这里用的是外部表方式，得装file_fdw扩展；

如果不装就可以考虑直接copy到数据库里去；

#!/bin/bash

#export PGDATA=/ssd/database/data5433

source ~/.bash_profile

if [[ $1 = "" || $2 = "" ]];

then

echo "usage create_log.sh databasename port"

exit 1

else

echo $1 $2

log_dir=`grep -i ^log_directory $PGDATA/postgresql.conf|awk '{print $3}'`

log_dir=`echo ${log_dir//\'/}`

logfile=`ls $PGDATA/$log_dir/*.csv|sort -nr |head -n 1`

psql -d $1 -c 'drop FOREIGN table pg_fdw_log' -p $2 -U postgres -h 127.0.0.1

echo $logfile

echo -e "

create foreign table pg_fdw_log

(

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

-- PRIMARY KEY (session_id, session_line_num)

)SERVER file_fdw_server

OPTIONS (format 'csv',header 'true',filename '$logfile', delimiter ',', null '');

" > create_pg_log.sql

psql -d $1 -f create_pg_log.sql -p $2 -U postgres -h 127.0.0.1

psql -d $1 -c 'select count(*) from pg_fdw_log' -p $2 -U postgres -h 127.0.0.1

fi

字段说明可以参见源码:

src/include/utils/elog.h

typedef struct ErrorData

{

int elevel; /* error level */

bool output_to_server; /* will report to server log? */

bool output_to_client; /* will report to client? */

bool show_funcname; /* true to force funcname inclusion */

bool hide_stmt; /* true to prevent STATEMENT: inclusion */

bool hide_ctx; /* true to prevent CONTEXT: inclusion */

const char *filename; /* __FILE__ of ereport() call */

int lineno; /* __LINE__ of ereport() call */

const char *funcname; /* __func__ of ereport() call */

const char *domain; /* message domain */

const char *context_domain; /* message domain for context message */

int sqlerrcode; /* encoded ERRSTATE */

char *message; /* primary error message (translated) */

char *detail; /* detail error message */

char *detail_log; /* detail error message for server log only */

char *hint; /* hint message */

char *context; /* context message */

const char *message_id; /* primary message's id (original string) */

char *schema_name; /* name of schema */

char *table_name; /* name of table */

char *column_name; /* name of column */

char *datatype_name; /* name of datatype */

char *constraint_name; /* name of constraint */

int cursorpos; /* cursor index into query string */

int internalpos; /* cursor index into internalquery */

char *internalquery; /* text of internally-generated query */

int saved_errno; /* errno at entry */

/* context containing associated non-constant strings */

struct MemoryContextData *assoc_context;

} ErrorData;

对应关系见:src/backend/utils/error/elog.c