Extensions
- pg_log_statements 0.0.2
- set log_statement for specific server processes
README
Contents
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 and 13.
Usage
PGLS has no GUC parameter and run log_statement=all
for selected server processes.
PGLS can be used in 2 different ways:
- Either by using the server process identifier (pid): pid mode
- Or by using a filter to enable logging from server process start to server process end (the filter clause specifies which server processed 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_filter('application_name', 'your_application');
To enable log_statement
parameter for new server process started by some specific PostgreSQL user, run:
select pgls_filter('user_name', 'your_user');
To enable log_statement
parameter for new server process started from some specific host name, run:
select pgls_filter('hostname', 'your_hostname');
To enable log_statement
parameter for new server process started from some specific IP address, run:
select pgls_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_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();