log_fdw

Overview

PG18 fixed by vonng

Version

Build

You can build the DEB packages for log_fdw using pig build:

pig build pkg log_fdw         # build DEB packages

Install

You can install log_fdw directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

pig install log_fdw;          # Install for current active PG version

pig ext install -y log_fdw -v 18  # PG 18
pig ext install -y log_fdw -v 17  # PG 17
pig ext install -y log_fdw -v 16  # PG 16
pig ext install -y log_fdw -v 15  # PG 15
pig ext install -y log_fdw -v 14  # PG 14

dnf install -y log_fdw_18       # PG 18
dnf install -y log_fdw_17       # PG 17
dnf install -y log_fdw_16       # PG 16
dnf install -y log_fdw_15       # PG 15
dnf install -y log_fdw_14       # PG 14

apt install -y postgresql-18-log-fdw   # PG 18
apt install -y postgresql-17-log-fdw   # PG 17
apt install -y postgresql-16-log-fdw   # PG 16
apt install -y postgresql-15-log-fdw   # PG 15
apt install -y postgresql-14-log-fdw   # PG 14

Create Extension:

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.