RDS PostgreSQL提供log_fdw插件,可以查询数据库日志。

前提条件

实例为RDS PostgreSQL 11。

背景信息

log_fdw插件提供如下两个函数,帮助您查询数据库日志。
  • list_postgres_log_files():列出所有的csvlog文件。
  • create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text):创建一个与特定csvlog文件相对应的外部表。

操作步骤

  1. 创建log_fdw插件。
    postgres=> create extension log_fdw;
    CREATE EXTENSION
  2. 创建log服务器定义。
    postgres=> create server <server名称> foreign data wrapper log_fdw;

    示例

    postgres=> create server log_server foreign data wrapper log_fdw;
    CREATE SERVER
  3. 调用list_postgres_log_files()函数,列出所有的csvlog文件。
    postgres=> select * from list_postgres_log_files() order by 1;
                file_name             | file_size_bytes
    ----------------------------------+-----------------
     postgresql-2020-01-10_095546.csv |            3794
     postgresql-2020-01-10_100336.csv |          318318
     postgresql-2020-01-11_000000.csv |          198437
     postgresql-2020-01-11_083546.csv |            4775
     postgresql-2020-01-13_030618.csv |            3347
  4. 调用create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text)函数,创建一个与特定csvlog文件相对应的外部表。
    postgres=> select create_foreign_table_for_log_file('<外部表名称>', '<log服务器名称>', '<csvlog文件名称>');

    示例

    postgres=> select create_foreign_table_for_log_file('ft1', 'log_server', 'postgresql-2020-01-13_030618.csv');
     create_foreign_table_for_log_file
    -----------------------------------
     t
    (1 row)
  5. 查询外部表即可查询到日志内容。
    postgres=> select log_time, message from <外部表名称> order by log_time desc limit 2;

    示例

    postgres=> select log_time, message from ft1 order by log_time desc limit 2;
              log_time          |                              message
    ----------------------------+-------------------------------------------------------------------
     2020-01-13 03:35:00.003+00 | cron job 1 completed: INSERT 0 1 1
     2020-01-13 03:35:00+00     | cron job 1 starting: INSERT INTO cron_test VALUES ('Hello World')
    (2 rows)

外部表结构

postgres=> \d+ ft1
                                                         Foreign table "public.ft1"
         Column         |            Type             | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
------------------------+-----------------------------+-----------+----------+---------+-------------+----------+--------------+-------------
 log_time               | timestamp(3) with time zone |           |          |         |             | plain    |              |
 user_name              | text                        |           |          |         |             | extended |              |
 database_name          | text                        |           |          |         |             | extended |              |
 process_id             | integer                     |           |          |         |             | plain    |              |
 connection_from        | text                        |           |          |         |             | extended |              |
 session_id             | text                        |           |          |         |             | extended |              |
 session_line_num       | bigint                      |           |          |         |             | plain    |              |
 command_tag            | text                        |           |          |         |             | extended |              |
 session_start_time     | timestamp with time zone    |           |          |         |             | plain    |              |
 virtual_transaction_id | text                        |           |          |         |             | extended |              |
 transaction_id         | bigint                      |           |          |         |             | plain    |              |
 error_severity         | text                        |           |          |         |             | extended |              |
 sql_state_code         | text                        |           |          |         |             | extended |              |
 message                | text                        |           |          |         |             | extended |              |
 detail                 | text                        |           |          |         |             | extended |              |
 hint                   | text                        |           |          |         |             | extended |              |
 internal_query         | text                        |           |          |         |             | extended |              |
 internal_query_pos     | integer                     |           |          |         |             | plain    |              |
 context                | text                        |           |          |         |             | extended |              |
 query                  | text                        |           |          |         |             | extended |              |
 query_pos              | integer                     |           |          |         |             | plain    |              |
 location               | text                        |           |          |         |             | extended |              |
 application_name       | text                        |           |          |         |             | extended |              |
Server: log_server
FDW options: (filename 'postgresql-2020-01-13_030618.csv')