Contents

• pg_log_statements
  • Installation
    • Compiling
    • PostgreSQL setup
  • Usage
    • Using pid mode
    • Using filter mode

pg_log_statements

pg_log_statements (PGLS) is a PostgreSQL extension that allows to log SQL statements for specific server processes: instead of setting log_statement parameter at instance level or database level, log_statement can be set for specific server processes.

Installation

Compiling

This module can be built using the standard PGXS infrastructure. For this to work, the pg_config program must be available in your $PATH:

git clone https://github.com/pierreforstmann/pg_log_statements.git 
cd pg_log_statements 
make 
make install 

PostgreSQL setup

Extension must be loaded at server level with shared_preload_libraries parameter:

shared_preload_libraries = 'pg_log_statements'

It must also be created with following SQL statement at server level:

create extension pg_log_statements;

This extension has been validated with PostgreSQL 9.5, 9.6, 10, 11, 12, 13, 14 and 15.

Usage

PGLS has no GUC parameter and run log_statement=all for selected server processes.

PGLS can be used in 2 different ways:

1. Either by using the server process identifier (pid): pid mode
2. Or by using a filter to enable logging from server process start to server process end (the filter clause specifies which server processes are going to enable log_statement=all): filter mode

Both modes are complementary and cannot be mixed: - if pid mode has been used, PGLS allows to stop server process logging - if filter mode has been used, PGLS does not allow to choose server process logging start or stop: server process logging starts at process creation and ends at process exit; filter mode only applies to new server processes (existing server processes cannot be selected in filter mode).

Using pid mode

To enable log_statement parameter for a specific server process, run:

select pgls_start(pid):

To disable log_statement parameter for a specific server process, run:

select pgls_stop(pid);

To check what is the current status of log_statement parameter for all server processes, run:

select pgls_state();

Using filter mode

To enable log_statement parameter for new server process started by some specific application, run:

select pgls_start_filter('application_name', 'your_application');

To enable log_statement parameter for new server process started by some specific PostgreSQL user, run:

select pgls_start_filter('user_name', 'your_user');

To enable log_statement parameter for new server process started from some specific host name, run:

select pgls_start_filter('hostname', 'your_hostname');

To enable log_statement parameter for new server process started from some specific IP address, run:

select pgls_start_filter('ip_address', 'your_IP_address');

NB: IP address is tested against remote_host of Port structure.

To enable log_statement parameter for new server process started for some specific database, run:

select pgls_start_filter('database_name', 'your_database_name');

Current filter mode configuration can be listed with:

select pgls_conf();

Filter mode is using parameters defined in Port structure (see libpq-be.h) used for frontend/backend communication. These parameters can be logged using following functions:

To start parameters logging at server process creation time, run:

select pgls_start_debug();

To stop parameters logging at server process creation time, run:

select pgls_stop_debug();