Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Use PostgreSQL log_fdw to Query Logs

How to Use PostgreSQL log_fdw to Query Logs

In this article, the author explains how to use PostgreSQL log_fdw to query log data directly in ApsaraDB RDS for PostgreSQL.

By digoal

Background

Usually, you query logs such as SQL audit, error logs, and slow SQL directly in the $PGDATA/log directory file. However, you can only query logs using the GUI in case of ApsaraDB RDS, which can be inconvenient.

The plug-in log_fdw is now available for direct log queries in ApsaraDB RDS for PostgreSQL. The usage is simple as follows. Please refer to the official guide of ApsaraDB RDS for PostgreSQL and the list of plug-ins supported.

How to Use log_fdw?

1) Create a plug-in.

postgres=> create extension log_fdw cascade;    
NOTICE:  installing required extension "file_fdw"    
CREATE EXTENSION

2) Check the features of the plug-in.

postgres=> \df    
                                                                     List of functions    
 Schema |               Name                |       Result data type        |                          Argument data types                          | Type     
--------+-----------------------------------+-------------------------------+-----------------------------------------------------------------------+------    
 public | create_foreign_table_for_log_file | boolean                       | table_name text, log_server text, log_file text                       | func    
 public | file_fdw_handler                  | fdw_handler                   |                                                                       | func    
 public | file_fdw_validator                | void                          | text[], oid                                                           | func    
 public | list_postgres_log_files           | SETOF record                  | OUT file_name text, OUT file_size_bytes bigint                        | func

3) List the log files.

postgres=> select * from list_postgres_log_files();    
WARNING:  1 temporary files and directories not closed at end-of-transaction    
            file_name             | file_size_bytes     
----------------------------------+-----------------    
 postgresql-2019-12-19_054325.csv |            5386    
(1 row)

4) Create a server, which may be provided, and you needn’t manually create in the future.

i=> create server log foreign data wrapper file_fdw ;    
CREATE SERVER

5) Create the external table corresponding to the specified log file.

postgres=> select create_foreign_table_for_log_file('ft1','log','postgresql-2019-12-19_054325.csv') ;    
 create_foreign_table_for_log_file     
-----------------------------------    
 t    
(1 row)

6) Query the external table, namely to query the current log content in real time.

postgres=> select * from ft1;    
-[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
------------    
log_time               | 2019-12-19 13:43:25.275+08    
user_name              |     
database_name          |     
process_id             | 56    
connection_from        |     
session_id             | 5dfb0dfd.38    
session_line_num       | 1    
command_tag            |     
session_start_time     | 2019-12-19 13:43:25+08    
virtual_transaction_id |     
transaction_id         | 0    
error_severity         | LOG    
sql_state_code         | 00000    
message                | database system was shut down at 2019-12-19 05:43:18 UTC    
detail                 |     
hint                   |     
internal_query         |     
internal_query_pos     |     
context                |     
query                  |     
query_pos              |     
location               | StartupXLOG, xlog.c:6362    
application_name       |     
    
.......

Configure the Number of Logs Retained

Configure the rds_max_log_files parameter to set the number of files to be retained. The default value is 20.

The size of a single file is 100MB by default.

postgres=> select name,setting,unit from pg_settings where name ~ 'log_ro';  
       name        | setting | unit   
-------------------+---------+------  
 log_rotation_age  | 0       | min  
 log_rotation_size | 102400  | kB  
(2 rows)

Therefore, logs of 2 GB are retained by default.

References

PostgreSQL Database Database Optimization SQL Audit Error Log Slow SQL Retention Period
0 0 0
Share on

Read previous post:

PostgreSQL v12: How pg_stat_statements Causes High-concurrency Performance Issues

Read next post:

How to Write into a Database Using reWriteBatchedInserts Parameter

digoal

276 posts | 24 followers

You may also like

Comments

digoal

276 posts | 24 followers

Related Products

More Posts by digoal

See All