背景

如果使用了CSV日志个数，可以使用file_fdw访问数据库的日志（慢日志，错误日志，审计日志），如下：

《使用SQL查询数据库日志 - file_fdw , csvlog , program , find》

日志是什么格式？日志在哪个目录？数据盘在哪个目录？都可以通过参数得到。所以我们不需要记录这些东西，直接使用函数来创建file_fdw外部表来查询日志。

如果不是CSV格式，可以使用pg_read_file函数来读取日志的内容。

  1. log_destination  
  3. Valid values are combinations of "stderr", "syslog", "csvlog", and "eventlog", depending on the platform.

使用psql变量存储日志、数据盘位置，便于你创建fdw表

man psql

variable用法

《PostgreSQL Oracle 兼容性之 psql sqlplus 变量 & set variable》

《使用SQL查询数据库日志 - file_fdw , csvlog , program , find》

psql 客户端，使用gset代替分号，可以将结果存储到psql变量中

如下，创建两个psql变量data_directory，log_directory

1、获取数据库的数据、日志目录位置

  1. select setting as data_directory from pg_settings where name='data_directory'  
  2. \gset  
  4. select setting as log_directory from pg_settings where name='log_directory'  
  5. \gset

2、使用pg_ls_logdir()可列出日志文件目录的文件

  1. postgres=# select * from pg_ls_logdir() order by modification;  
  2.                name               |  size  |      modification        
  3. ----------------------------------+--------+------------------------  
  4.  postgresql-2019-03-26_062949.log |    168 | 2019-03-26 14:29:49+08  
  5.  postgresql-2019-03-26_062949.csv |   1800 | 2019-03-26 14:41:48+08  
  6.  postgresql-2019-03-26_064150.csv | 143182 | 2019-03-27 10:03:12+08  
  7.  postgresql-2019-03-26_064150.log |   1734 | 2019-03-27 10:03:12+08  
  8.  postgresql-2019-03-27_020316.log |    168 | 2019-03-27 10:03:16+08  
  9.  postgresql-2019-03-27_020316.csv |  25697 | 2019-05-25 00:11:30+08  
  10.  postgresql-2019-05-28_080838.log |   8085 | 2019-07-10 09:39:33+08  
  11.  postgresql-2019-05-28_080838.csv |  46466 | 2019-07-10 10:08:10+08  
  12. (8 rows)  
  14. postgres=# select * from pg_ls_logdir() where name ~ 'csv$' order by modification;  
  15.                name               |  size  |      modification        
  16. ----------------------------------+--------+------------------------  
  17.  postgresql-2019-03-26_062949.csv |   1800 | 2019-03-26 14:41:48+08  
  18.  postgresql-2019-03-26_064150.csv | 143182 | 2019-03-27 10:03:12+08  
  19.  postgresql-2019-03-27_020316.csv |  25697 | 2019-05-25 00:11:30+08  
  20.  postgresql-2019-05-28_080838.csv |  46466 | 2019-07-10 10:08:10+08  
  21. (4 rows)

3、将日志文件名记录到变量中

  1. select name as log_name from pg_ls_logdir() where name ~ 'csv$' order by modification desc limit 1  
  2. \gset

4、使用psql变量拼出sql，使用pg_read_file直接读取文件内容，指定从哪个字节读到哪个字节。

注意pg_read_file函数返回的结果不分行，也不区分字段，如下输出0字节到100字节。但是pg_read_file可以读取任意格式的文件。

  1. select log from pg_read_file(:'data_directory'||'/'||:'log_directory'||'/'||:'log_name', 0, 100) as log;
  1. postgres=# select log from pg_read_file(:'data_directory'||'/'||:'log_directory'||'/'||:'log_name', 0, 10000) as log;  
  2.                                                                                                                                                                                   log               
  4. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  5. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  6.  2019-07-20 00:02:14.771 CST,,,10766,,5d2aea65.2a0e,19,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8340",""                                
  7.                                                                                                                                                                      +  
  8.  2019-07-20 00:02:27.258 CST,,,10766,,5d2aea65.2a0e,20,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint complete: wrote 124 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=12.  
  9. 484 s, sync=0.000 s, total=12.487 s; sync files=48, longest=0.000 s, average=0.000 s; distance=218 kB, estimate=1753715 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8422",""+  
  10.  2019-07-20 00:30:09.784 CST,,,65215,"[local]",5d31f011.febf,1,"",2019-07-20 00:30:09 CST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,"BackendInitialize, postmaster.c:4294",""         
  11.                                                                                                                                                                      +  
  12.  2019-07-20 00:30:09.785 CST,"postgres","postgres",65215,"[local]",5d31f011.febf,2,"authentication",2019-07-20 00:30:09 CST,3/20405,0,LOG,00000,"connection authorized: user=postgres database=pos  
  13. tgres application_name=psql",,,,,,,,"PerformAuthentication, postinit.c:303",""                                                                                       +  
  14.  2019-07-20 00:37:14.358 CST,,,10766,,5d2aea65.2a0e,21,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8340",""                                
  15.                                                                                                                                                                      +  
  16.  2019-07-20 00:37:14.624 CST,,,10766,,5d2aea65.2a0e,22,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.264  
  17.  s, sync=0.000 s, total=0.266 s; sync files=2, longest=0.000 s, average=0.000 s; distance=5 kB, estimate=1578344 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8422",""       +  
  19. (1 row)

5、使用regexp_split_to_table可以分行，但是如果sql也是分行的不太好处理

https://www.postgresql.org/docs/12/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE

  1. postgres=# select regexp_split_to_table(log,'\n') with ordinality from pg_read_file(:'data_directory'||'/'||:'log_directory'||'/'||:'log_name', 0, 1000000) as log;  
  2.                                                                                                                                                                          regexp_split_to_table      
  4. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  5. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  6.  2019-07-20 00:02:14.771 CST,,,10766,,5d2aea65.2a0e,19,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8340",""  
  7.  2019-07-20 00:02:27.258 CST,,,10766,,5d2aea65.2a0e,20,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint complete: wrote 124 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=12.  
  8. 484 s, sync=0.000 s, total=12.487 s; sync files=48, longest=0.000 s, average=0.000 s; distance=218 kB, estimate=1753715 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8422",""  
  9.  2019-07-20 00:30:09.784 CST,,,65215,"[local]",5d31f011.febf,1,"",2019-07-20 00:30:09 CST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,"BackendInitialize, postmaster.c:4294",""  
  10.  2019-07-20 00:30:09.785 CST,"postgres","postgres",65215,"[local]",5d31f011.febf,2,"authentication",2019-07-20 00:30:09 CST,3/20405,0,LOG,00000,"connection authorized: user=postgres database=pos  
  11. tgres application_name=psql",,,,,,,,"PerformAuthentication, postinit.c:303",""  
  12.  2019-07-20 00:37:14.358 CST,,,10766,,5d2aea65.2a0e,21,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8340",""  
  13.  2019-07-20 00:37:14.624 CST,,,10766,,5d2aea65.2a0e,22,,2019-07-14 16:40:05 CST,,0,LOG,00000,"checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.264  
  14.  s, sync=0.000 s, total=0.266 s; sync files=2, longest=0.000 s, average=0.000 s; distance=5 kB, estimate=1578344 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8422",""  
  16. (7 rows)

6、使用file_fdw查看日志内容，很完美的呈现。

  1. create schema _sys;  
  2. set search_path=_sys,public,"$user";  
  3. create extension file_fdw with schema _sys;  
  4. CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

为了让file_fdw可以读取所有日志文件，使用program来读取文件内容。将外部表中的program的内容存入变量

  1. \set pro '''find ':data_directory'/':log_directory' -type f -name "*.csv" -exec cat {} \\;'', format ''csv'''  
  4. postgres=# \set pro '''find ':data_directory'/':log_directory' -type f -name "*.csv" -exec cat {} \\;'', format ''csv'''  
  5. postgres=# \echo :pro  
  6. 'find /data01/pg12/pg_root12000/log -type f -name "*.csv" -exec cat {} \;', format 'csv'

7、建立fdw表如下

  1. CREATE FOREIGN TABLE pglog (    
  2.   log_time timestamp(3) with time zone,    
  3.   user_name text,    
  4.   database_name text,    
  5.   process_id integer,    
  6.   connection_from text,    
  7.   session_id text,    
  8.   session_line_num bigint,    
  9.   command_tag text,    
  10.   session_start_time timestamp with time zone,    
  11.   virtual_transaction_id text,    
  12.   transaction_id bigint,    
  13.   error_severity text,    
  14.   sql_state_code text,    
  15.   message text,    
  16.   detail text,    
  17.   hint text,    
  18.   internal_query text,    
  19.   internal_query_pos integer,    
  20.   context text,    
  21.   query text,    
  22.   query_pos integer,    
  23.   location text,    
  24.   application_name text    
  25. ) SERVER pglog    
  26. OPTIONS ( program :pro );

8、如果想过滤掉一些记录，可以创建视图

  1. create view v_log as select * from pglog where ... -- 过滤不想输出的内容  
  2. grant select on v_log to public;

9、查询例子

  1. postgres=# select * from pglog limit 2;  
  2. -[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  3. -----------------------------------------------------  
  4. log_time               | 2019-07-20 14:02:14.771+08  
  5. user_name              |   
  6. database_name          |   
  7. process_id             | 10766  
  8. connection_from        |   
  9. session_id             | 5d2aea65.2a0e  
  10. session_line_num       | 19  
  11. command_tag            |   
  12. session_start_time     | 2019-07-15 06:40:05+08  
  13. virtual_transaction_id |   
  14. transaction_id         | 0  
  15. error_severity         | LOG  
  16. sql_state_code         | 00000  
  17. message                | checkpoint starting: time  
  18. detail                 |   
  19. hint                   |   
  20. internal_query         |   
  21. internal_query_pos     |   
  22. context                |   
  23. query                  |   
  24. query_pos              |   
  25. location               | LogCheckpointStart, xlog.c:8340  
  26. application_name       |   
  27. -[ RECORD 2 ]----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  28. -----------------------------------------------------  
  29. log_time               | 2019-07-20 14:02:27.258+08  
  30. user_name              |   
  31. database_name          |   
  32. process_id             | 10766  
  33. connection_from        |   
  34. session_id             | 5d2aea65.2a0e  
  35. session_line_num       | 20  
  36. command_tag            |   
  37. session_start_time     | 2019-07-15 06:40:05+08  
  38. virtual_transaction_id |   
  39. transaction_id         | 0  
  40. error_severity         | LOG  
  41. sql_state_code         | 00000  
  42. message                | checkpoint complete: wrote 124 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=12.484 s, sync=0.000 s, total=12.487 s; sync files=48, longest=0.000 s,   
  43. average=0.000 s; distance=218 kB, estimate=1753715 kB  
  44. detail                 |   
  45. hint                   |   
  46. internal_query         |   
  47. internal_query_pos     |   
  48. context                |   
  49. query                  |   
  50. query_pos              |   
  51. location               | LogCheckpointEnd, xlog.c:8422  
  52. application_name       |

使用plpgsql创建file_fdw表，更加简便

  1. do language plpgsql $$  
  2. declare  
  3.   v_datadir text;  
  4.   v_logdir text;  
  5. begin  
  6.   select setting into v_datadir from pg_settings where name='data_directory';  
  7.   select setting into v_logdir  from pg_settings where name='log_directory';  
  9.   create schema IF NOT EXISTS _sys ;  
  11.   set search_path=_sys,public,"$user";  
  13.   create extension IF NOT EXISTS file_fdw with schema _sys;  
  15.   CREATE SERVER IF NOT EXISTS pglog FOREIGN DATA WRAPPER file_fdw;    
  17.   execute format('CREATE FOREIGN TABLE IF NOT EXISTS pglog (    
  18.     log_time timestamp(3) with time zone,    
  19.     user_name text,    
  20.     database_name text,    
  21.     process_id integer,    
  22.     connection_from text,    
  23.     session_id text,    
  24.     session_line_num bigint,    
  25.     command_tag text,    
  26.     session_start_time timestamp with time zone,    
  27.     virtual_transaction_id text,    
  28.     transaction_id bigint,    
  29.     error_severity text,    
  30.     sql_state_code text,    
  31.     message text,    
  32.     detail text,    
  33.     hint text,    
  34.     internal_query text,    
  35.     internal_query_pos integer,    
  36.     context text,    
  37.     query text,    
  38.     query_pos integer,    
  39.     location text,    
  40.     application_name text    
  41.     ) SERVER pglog    
  42.     OPTIONS ( program %L , format ''csv'')' ,  
  43.   format('find %s/%s -type f -name "*.csv" -exec cat {} \;', v_datadir, v_logdir)   
  44.   );   
  46. end;  
  47. $$;

是不是很方便呢？以上执行完，自动创建pglog外部表，查询它，可以看到日志的内容。换行没有问题。

  1. -[ RECORD 15 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  2. -----------------------------------------------------  
  3. log_time               | 2019-07-20 14:47:35.418+08  
  4. user_name              | postgres  
  5. database_name          | postgres  
  6. process_id             | 65215  
  7. connection_from        | [local]  
  8. session_id             | 5d31f011.febf  
  9. session_line_num       | 11  
  10. command_tag            | idle  
  11. session_start_time     | 2019-07-20 14:30:09+08  
  12. virtual_transaction_id | 3/20443  
  13. transaction_id         | 0  
  14. error_severity         | LOG  
  15. sql_state_code         | 00000  
  16. message                | statement: SELECT n.nspname as "Schema",                                                                                                                                   
  17.                                                      +  
  18.                        |   p.proname as "Name",                                                                                                                                                     
  19.                                                      +  
  20.                        |   pg_catalog.pg_get_function_result(p.oid) as "Result data type",                                                                                                          
  21.                                                      +  
  22.                        |   pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",                                                                                                    
  23.                                                      +  
  24.                        |  CASE p.prokind                                                                                                                                                            
  25.                                                      +  
  26.                        |   WHEN 'a' THEN 'agg'                                                                                                                                                      
  27.                                                      +  
  28.                        |   WHEN 'w' THEN 'window'                                                                                                                                                   
  29.                                                      +  
  30.                        |   WHEN 'p' THEN 'proc'                                                                                                                                                     
  31.                                                      +  
  32.                        |   ELSE 'func'                                                                                                                                                              
  33.                                                      +  
  34.                        |  END as "Type"                                                                                                                                                             
  35.                                                      +  
  36.                        | FROM pg_catalog.pg_proc p                                                                                                                                                  
  37.                                                      +  
  38.                        |      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace                                                                                                         
  39.                                                      +  
  40.                        | WHERE p.proname OPERATOR(pg_catalog.~) '^(.*ordi.*)$' COLLATE pg_catalog.default                                                                                           
  41.                                                      +  
  42.                        | ORDER BY 1, 2, 4;  
  43. detail                 |   
  44. hint                   |   
  45. internal_query         |   
  46. internal_query_pos     |   
  47. context                |   
  48. query                  |   
  49. query_pos              |   
  50. location               | exec_simple_query, postgres.c:1045  
  51. application_name       | psql

参考

man find

man psql

《PostgreSQL Oracle 兼容性之 psql sqlplus 变量 & set variable》

《使用SQL查询数据库日志 - file_fdw , csvlog , program , find》

https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

https://www.postgresql.org/docs/12/file-fdw.html

https://www.postgresql.org/docs/12/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE