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
- Create the log_fdw extension.
postgres=> create extension log_fdw; CREATE EXTENSION - 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 - 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 - 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) - 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')