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:

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

Source: README, control file, extension SQL 1.0.2, Tag v1.0.2

pgelog writes rollback-resistant log rows through pseudo-autonomous transactions implemented with dblink. It caches the extra connection in pg_variables so repeated logging in the same session is cheaper.

Requirements and install

• PostgreSQL 11+
• dblink
• pg_variables
• passwordless local dblink access, typically via peer

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS pg_variables;
CREATE EXTENSION pgelog;

Main objects and functions

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

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

• pgelog_logs: base log table.
• pgelog_vw_logs: log view with timing deltas.
• pgelog_params: configuration table.
• pgelog_to_log(...): write a log row that survives rollback.
• pgelog_get_param(...), pgelog_set_param(...), pgelog_delete_param(...): manage extension settings.

Typical use

The official README shows pgelog_to_log(...) in PL/pgSQL exception handlers: collect diagnostics with GET STACKED DIAGNOSTICS, write a FAIL log row, then re-raise the error.

Caveats

• Each session can open one additional dblink connection, so max_connections should account for that.
• Upstream v1.0.2 still ships extension SQL under the same user-visible object family; Pigsty’s note about runtime dblink plus pg_variables requirements is confirmed by the control file and README.