log_fdw

log_fdw : foreign-data wrapper for Postgres log file access

Overview

Packages

Source

pig build pkg log_fdw;		# build deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install log_fdw;		# install via package name, for the active PG version

pig install log_fdw -v 18;   # install for PG 18
pig install log_fdw -v 17;   # install for PG 17
pig install log_fdw -v 16;   # install for PG 16
pig install log_fdw -v 15;   # install for PG 15
pig install log_fdw -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION log_fdw;

Usage

Syntax:

CREATE EXTENSION log_fdw;
CREATE SERVER log_fdw_server FOREIGN DATA WRAPPER log_fdw;
SELECT * FROM list_postgres_log_files();

Source: README

log_fdw is a PostgreSQL foreign data wrapper for reading PostgreSQL log files through SQL. It provides helper functions to list files in the server log directory and to create foreign tables for individual log files.

Core Functions

The upstream README documents two SQL entry points:

create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)
list_postgres_log_files()

list_postgres_log_files() is a compatibility wrapper around PostgreSQL core’s pg_ls_logdir().

Basic Workflow

Create the extension and a foreign server:

CREATE EXTENSION log_fdw;
CREATE SERVER log_fdw_server FOREIGN DATA WRAPPER log_fdw;

List files available in the PostgreSQL log directory:

SELECT * FROM list_postgres_log_files() ORDER BY 1 DESC LIMIT 10;

Create foreign tables for CSV logs or plain .log files:

SELECT * FROM create_foreign_table_for_log_file(
  'postgresql_2022_11_28_csv',
  'log_fdw_server',
  'postgresql-2022-11-28.csv'
);

SELECT * FROM create_foreign_table_for_log_file(
  'postgresql_2022_11_28_log',
  'log_fdw_server',
  'postgresql-2022-11-28.log'
);

Querying

Foreign tables created from plain log files expose a single log-entry style column, while CSV log files expose structured columns such as log_time, error_severity, message, and session metadata.

Typical usage is straightforward:

SELECT * FROM postgresql_2022_11_28_log LIMIT 2;

SELECT log_time, error_severity, message
FROM postgresql_2022_11_28_csv
WHERE error_severity = 'ERROR'
ORDER BY log_time DESC
LIMIT 20;

Privileges

Only superusers can create the extension. The README also notes that superusers can delegate access to non-superusers with the minimum required grants, for example:

CREATE ROLE foo;
GRANT pg_monitor TO foo;
GRANT CREATE ON SCHEMA bar TO foo;
GRANT USAGE ON FOREIGN SERVER log_fdw_server TO foo;

pg_monitor is specifically needed when list_postgres_log_files() is used, because the underlying pg_ls_logdir() function requires it.

Build Notes

The project can be built standalone with PGXS:

export USE_PGXS=1
make
make install

The source can also be copied into PostgreSQL’s contrib tree and built there as part of a larger distribution.