pgelog

Overview

Release tag 1.0.2 still ships extension SQL version 1.0; requires the dblink extension at runtime in addition to pg_variables.

Version

Build

You can build the RPM / DEB packages for pgelog using pig build:

pig build pkg pgelog         # build RPM / DEB packages

Install

You can install pgelog 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:

• Install
• pig
• dnf
• apt

pig install pgelog;          # Install for current active PG version

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

dnf install -y pgelog_18       # PG 18
dnf install -y pgelog_17       # PG 17
dnf install -y pgelog_16       # PG 16
dnf install -y pgelog_15       # PG 15
dnf install -y pgelog_14       # PG 14

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

Create Extension:

CREATE EXTENSION pgelog CASCADE;  -- requires: dblink, pg_variables

Usage

Syntax:

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS pg_variables;
CREATE EXTENSION pgelog;
SELECT pgelog_to_log('SQL', 'standalone', 'Test of logging by pgelog', '1');

Source: README

pgelog writes log records into PostgreSQL tables using pseudo-autonomous transactions implemented through dblink. The key goal is that log entries survive even when the caller’s main transaction rolls back.

Prerequisites

The README requires:

• PostgreSQL 11 or newer
• dblink
• pg_variables
• local passwordless dblink access, typically via a peer local entry in pg_hba.conf

It also warns that each session may open one extra connection for dblink, so max_connections should be sized accordingly.

Objects

The extension creates:

• pgelog_params for configuration
• pgelog_logs as the base log table
• pgelog_vw_logs as a log view with timing information

The log table/view stores fields such as timestamp, log type, source function, phase, message text, transaction id, SQLSTATE, SQLERRM, and connection name.

Basic Logging

Write a log entry:

SELECT pgelog_to_log('SQL', 'standalone', 'Test of logging by pgelog', '1');

Read the latest log:

SELECT log_stamp, log_info
FROM pgelog_logs
ORDER BY log_stamp DESC
LIMIT 1;

PL/pgSQL Exception Logging

The README includes a larger PL/pgSQL example that catches exceptions, collects diagnostics, writes a FAIL log entry through pgelog_to_log(...), and then re-raises the exception. This is the main pattern for capturing rollback-resistant failure logs.

Configuration

Configuration parameters are managed with:

SELECT pgelog_get_param('pgelog_ttl_minutes');
SELECT pgelog_set_param('pgelog_ttl_minutes', '2880');

The README documents pgelog_ttl_minutes and other parameters through the pgelog_params table and helper functions.