log_fdw - PolarDB - Alibaba Cloud Documentation Center

You can use the log_fdw extension to query database logs in the CSV format.

Prerequisites

By default, PolarDB for PostgreSQL does not generate database logs in the CSV format. You must change the GUC parameter log_destination to csvlog.
The extension is supported on the PolarDB for PostgreSQL clusters that run the following engine:
- PostgreSQL 14 (revision version 14.5.1.0 or later)
- PostgreSQL 11 (revision version 1.1.27 or later)
Note You can execute the following statements to view the revision version of a PolarDB for PostgreSQL cluster:
- PostgreSQL 14
```
select version();
```
- PostgreSQL 11
```
show polar_version;
```

Background information

The log_fdw extension provides the following functions:

list_postgres_log_files(): lists all CSV-formatted log files in the pg_log directory.
Note The log_fdw extension also provides a view named list_postgres_log_files to query all CSV-formatted log files in the directory.
create_foreign_table_for_log_file('table_name', 'log_server', 'log_file'): creates a foreign table for a specified CSV-formatted log file. You can view the log data by querying the foreign table.
Note Parameters in the function:
- table_name: the name of the foreign table.
- log_server: the name of the foreign server.
- log_file: the name of the CSV-formatted log file.

Usage

Create the log_fdw extension.
```
create extension log_fdw;
```
Create a foreign server.
Use the log_fdw extension to create a virtual foreign server named log_server and map it to the PostgreSQL database. Example:
```
create server log_server foreign data wrapper log_fdw;
```

Call the list_postgres_log_files() function to list all CSV-formatted log files.

select * from list_postgres_log_files();

Sample result:

            file_name             | file_size_bytes
----------------------------------+-----------------
 postgresql-2022-08-24_080308.csv |            1235
(1 row)

Create a foreign table.

select create_foreign_table_for_log_file('ft1', 'log_server', 'postgresql-2022-08-24_080308.csv');

Sample result:

 create_foreign_table_for_log_file
-----------------------------------
 t
(1 row)

Query the foreign table (or the CSV-formatted log file).

select log_time, message from ft1 order by log_time desc limit 2;

Sample result:

          log_time          |                  message
----------------------------+-------------------------------------------
 2022-08-24 08:04:36.699+00 | vfs open dir pg_log, num open dir 1
 2022-08-24 08:03:53.687+00 | create reference for flog_index_insert_bg
(2 rows)

Delete the log_fdw extension.
```
drop extension log_fdw;
```