This topic describes how to use the log_fdw extension to query the database logs of an ApsaraDB RDS for PostgreSQL instance.

Prerequisites

Your RDS instance runs PostgreSQL 11. If you cannot create the extension even though your RDS instance runs PostgreSQL 11, you must update the minor engine version of your RDS instance. For more information, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.

Background information

The log_fdw extension provides the following functions:
  • list_postgres_log_files(): lists all .csv log files.
  • create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text): creates a foreign table associated with a specific .csv log file.

Procedure

  1. Create the log_fdw extension.
    postgres=> create extension log_fdw;
    CREATE EXTENSION
  2. Create a definition for the log server.
    postgres=> create server <The name of the log server> foreign data wrapper log_fdw;

    Example:

    postgres=> create server log_server foreign data wrapper log_fdw;
    CREATE SERVER
  3. Call the list_postgres_log_files() function to list all .csv log files.
    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. Call the create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text) function to create a foreign table associated with a specific .csv log file.
    postgres=> select create_foreign_table_for_log_file('<The name of the foreign table>', '<The name of the log server>', '<The name of the .csv log file associated with the foreign table>');

    Example:

    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. Query the foreign table to obtain the data of the .csv log file.
    postgres=> select log_time, message from <The name of the foreign table> order by log_time desc limit 2;

    Example:

    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)

Schema of a foreign table

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')