我的书签
添加书签
移除书签背景
如果使用了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
