背景

如果使用了CSV日志个数,可以使用file_fdw访问数据库的日志(慢日志,错误日志,审计日志),如下:

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

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

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

  1. log_destination
  2. 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
  3. select setting as log_directory from pg_settings where name='log_directory'
  4. \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)
  13. postgres=# select * from pg_ls_logdir() where name ~ 'csv$' order by modification;
  14. name | size | modification
  15. ----------------------------------+--------+------------------------
  16. postgresql-2019-03-26_062949.csv | 1800 | 2019-03-26 14:41:48+08
  17. postgresql-2019-03-26_064150.csv | 143182 | 2019-03-27 10:03:12+08
  18. postgresql-2019-03-27_020316.csv | 25697 | 2019-05-25 00:11:30+08
  19. postgresql-2019-05-28_080838.csv | 46466 | 2019-07-10 10:08:10+08
  20. (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
  3. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  4. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  5. 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",""
  6. +
  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. +
  11. 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
  12. tgres application_name=psql",,,,,,,,"PerformAuthentication, postinit.c:303","" +
  13. 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",""
  14. +
  15. 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
  16. 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","" +
  17. (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
  3. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  4. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  5. 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",""
  6. 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.
  7. 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",""
  8. 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",""
  9. 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
  10. tgres application_name=psql",,,,,,,,"PerformAuthentication, postinit.c:303",""
  11. 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",""
  12. 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
  13. 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",""
  14. (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'''
  2. postgres=# \set pro '''find ':data_directory'/':log_directory' -type f -name "*.csv" -exec cat {} \\;'', format ''csv'''
  3. postgres=# \echo :pro
  4. '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';
  8. create schema IF NOT EXISTS _sys ;
  9. set search_path=_sys,public,"$user";
  10. create extension IF NOT EXISTS file_fdw with schema _sys;
  11. CREATE SERVER IF NOT EXISTS pglog FOREIGN DATA WRAPPER file_fdw;
  12. execute format('CREATE FOREIGN TABLE IF NOT EXISTS pglog (
  13. log_time timestamp(3) with time zone,
  14. user_name text,
  15. database_name text,
  16. process_id integer,
  17. connection_from text,
  18. session_id text,
  19. session_line_num bigint,
  20. command_tag text,
  21. session_start_time timestamp with time zone,
  22. virtual_transaction_id text,
  23. transaction_id bigint,
  24. error_severity text,
  25. sql_state_code text,
  26. message text,
  27. detail text,
  28. hint text,
  29. internal_query text,
  30. internal_query_pos integer,
  31. context text,
  32. query text,
  33. query_pos integer,
  34. location text,
  35. application_name text
  36. ) SERVER pglog
  37. OPTIONS ( program %L , format ''csv'')' ,
  38. format('find %s/%s -type f -name "*.csv" -exec cat {} \;', v_datadir, v_logdir)
  39. );
  40. end;
  41. $$;

是不是很方便呢?以上执行完,自动创建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