The log_fdw extension maps PostgreSQL CSV log files to foreign tables, letting you query database logs with standard SQL — no shell access required. Use it to analyze slow queries, audit activity over a time range, or filter logs by severity, user, or session.
Prerequisites
Before you begin, ensure that:
Your RDS instance runs PostgreSQL 11
If the extension cannot be created, update the minor engine version of your instance
How it works
log_fdw provides two functions:
| Function | Description |
|---|---|
list_postgres_log_files() | Lists all available .csv log files and their sizes |
create_foreign_table_for_log_file(IN table_name text, IN log_server text, IN log_file text) | Creates a foreign table mapped to a specific .csv log file |
Once a foreign table is created, query it like any regular table to retrieve log data.
Query logs with log_fdw
Step 1: Create the extension
postgres=> create extension log_fdw;
CREATE EXTENSIONStep 2: Define a log server
postgres=> create server log_server foreign data wrapper log_fdw;
CREATE SERVERReplace log_server with the name you want to assign to the log server.
Step 3: List available 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 | 3347Step 4: Create a foreign table for a log file
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)The three parameters are the foreign table name, the log server name, and the log file name.
Step 5: Query the foreign table
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)Foreign table schema
The foreign table created by create_foreign_table_for_log_file() has the following columns:
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')Key columns for log analysis:
| Column | Type | Description |
|---|---|---|
log_time | timestamp(3) with time zone | Timestamp of the log entry |
user_name | text | Database user who triggered the event |
database_name | text | Database where the event occurred |
process_id | integer | OS process ID of the PostgreSQL backend |
error_severity | text | Log level: DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, or PANIC |
sql_state_code | text | Five-character SQLSTATE error code (follows the SQL standard) |
message | text | Primary log message |
detail | text | Secondary detail message, if any |
hint | text | Suggested action to resolve an error, if provided |
internal_query | text | Internal query that triggered the error, if applicable |
internal_query_pos | integer | Character offset within internal_query where the error occurred |
context | text | Call stack context at the time of the log entry |
query | text | The user-supplied query string, if applicable |
query_pos | integer | Character offset within query where the error occurred |
command_tag | text | SQL command type associated with the log entry (for example, SELECT, INSERT) |
session_id | text | Unique identifier for the database session |
session_start_time | timestamp with time zone | Time the database session started |
session_line_num | bigint | Line number within the session's log output |
virtual_transaction_id | text | Virtual transaction ID assigned before a real transaction ID is allocated |
transaction_id | bigint | Real transaction ID, once assigned |
connection_from | text | Client host address and port |
location | text | Source file location in the PostgreSQL server code where the log entry was generated |
application_name | text | Name of the client application, if set |