pg将运行日志导入表中的方法及时区问题解决

作者：瀚高PG实验室 （Highgo PG Lab）- 徐云鹤 postgresql将数据库运行告警日志导入表中的方法及时间时区问题解决。 ==如果是看表中日志记录时间log_time和session_start_time和实际时间相差10小时或者8小时之类问题的解决方案，可以直接往下拉。==

想将pg数据库运行告警日志支持导入表中，需要配置如下参数： logging_collector：日志开关。设置为on。修改此参数需要重启。 log_directory：日志存放路径。此处举例设置为log，则放置到data目录的log下。修改此参数需要reload。 log_destination：日志记录方法。设置为csvlog。修改此参数需要reload。

提供一个模板供参考： 该模板可实现： 1.日志记录开启。 2.存放于data数据目录的log目录下 3.每天一个，每周循环覆盖，不限制日志大小。

alter system set logging_collector = on;
alter system set log_directory = 'log';
alter system set log_filename = 'pg-%a.log';
alter system set log_rotation_age = '1d';
alter system set log_rotation_size = 0;
alter system set log_truncate_on_rotation = on;

开启日志模式为csvlog后，就可以将其导入表中了。 不同版本数据库日志列的内容略有差别，例如pg13比pg12多了backend_type列。下边附一下官方手册提供的建表语句。 PG13：

CREATE table postgres_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,
  backend_type text,
  PRIMARY KEY (session_id, session_line_num)
)

PG9-12:

CREATE TABLE postgres_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)
);

下面导入日志内容：

COPY postgres_log FROM 'log/postgresql-2021-01-15_000000.csv' WITH csv;

如果提示ERROR: extra data after last expected column或者ERROR: missing data for column "backend_type"之类的，则说明日志列数和表列数不一致。按照上边的检查一下即可。 导入成功会提示COPY XXX。 然后查询即可。

select * from postgres_log;

如果log_time和session_start_time的时间和实际时间相差10小时或者8小时之类问题，继续往下：

如果日志记录时区参数是PRC或者Asia/Shanghai，那么日志csv的log_time和session_start_time是以CST结尾。 而CST为如下4个不同的时区的缩写： 美国中部时间：Central Standard Time (USA) UT-6:00 澳大利亚中部时间：Central Standard Time (Australia) UT+9:30 中国标准时间：China Standard Time UT+8:00 古巴标准时间：Cuba Standard Time UT-4:00 PG将其当成了美国中部时间。因为表中查询的时间有误。 查询pg_timezone_names可以发现大部分时区名简称均为CST。

postgres=# select * from pg_timezone_names  where utc_offset='+08:00:00';
        name        | abbrev | utc_offset | is_dst 
--------------------+--------+------------+--------
 ROC                | CST    | 08:00:00   | f
 Asia/Hong_Kong     | HKT    | 08:00:00   | f
 Asia/Choibalsan    | +08    | 08:00:00   | f
 Asia/Macao         | CST    | 08:00:00   | f
 Asia/Kuala_Lumpur  | +08    | 08:00:00   | f
 Asia/Manila        | PST    | 08:00:00   | f
 Asia/Kuching       | +08    | 08:00:00   | f
 Asia/Chongqing     | CST    | 08:00:00   | f
 Asia/Makassar      | WITA   | 08:00:00   | f
 Asia/Brunei        | +08    | 08:00:00   | f
 Asia/Ulaanbaatar   | +08    | 08:00:00   | f
 Asia/Taipei        | CST    | 08:00:00   | f
 Asia/Shanghai      | CST    | 08:00:00   | f
 Asia/Macau         | CST    | 08:00:00   | f
 Asia/Ulan_Bator    | +08    | 08:00:00   | f
 Asia/Singapore     | +08    | 08:00:00   | f
 Asia/Chungking     | CST    | 08:00:00   | f
 Asia/Harbin        | CST    | 08:00:00   | f
 Asia/Ujung_Pandang | WITA   | 08:00:00   | f
 Asia/Irkutsk       | +08    | 08:00:00   | f
 Australia/West     | AWST   | 08:00:00   | f
 Australia/Perth    | AWST   | 08:00:00   | f
 Hongkong           | HKT    | 08:00:00   | f
 Etc/GMT-8          | +08    | 08:00:00   | f
 Singapore          | +08    | 08:00:00   | f
 PRC                | CST    | 08:00:00   | f
(26 rows)

因此可以采取如下解决方案： 1.设置数据库参数log_timezone为'Asia/Hong_Kong','Hongkong','Etc/GMT-8'之一并reload数据库。这样在导入日志到表中查询，时间就没有问题了。 2.建表语句去掉带时区的timestamp即可。 PG13：

CREATE table postgres_log
(
  log_time timestamp(3),
  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,
  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,
  PRIMARY KEY (session_id, session_line_num)
)

PG9-12:

CREATE TABLE postgres_log
(
  log_time timestamp(3),
  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,
  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)
);

最后简单提一个可以查看实时日志内容的方法的demo：

create extension file_fdw;
create server pg_file_server foreign data wrapper file_fdw;
CREATE foreign table pg_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,
  backend_type text 
)                                           
SERVER pg_file_server options(filename 'log/postgresql.csv',format 'csv',header 'false');

select * from pg_log;