Permalink
Switch branches/tags
Find file
Fetching contributors…
Cannot retrieve contributors at this time
89 lines (60 sloc) 4.21 KB
pg_log_userqueries is a PostgreSQL module that logs each query executed that
follows a specific pattern (superuser, user name, database name, query). It
records each query in the standard log file. Default is to log superusers
queries executed on all databases. It is also possible to filter the queries
to be logged through a regular expression.
To install pg_log_userqueries, you should untar the pg_log_userqueries tarball
anywhere you want.
You'll then need to compile it with pgxs. So the pg_config tool must be in your
path. Depending on your PostgreSQL installation, you may need to install a dev
package. Once pg_config is in your path, do "make", and then "make install".
Once it's installed, you'll need to configure PostgreSQL to make use of it:
* shared_preload_libraries='pg_log_userqueries'
There are also optional parameters. To configure them, with a PostgreSQL release
older than 9.2, you first need to add the class of pg_log_userqueries in
custom_variable_classes:
* custom_variable_classes = 'pg_log_userqueries'
In more recent release, custom_variable_classes is no longer available but you
can still configure pg_log_userqueries.
You'll be able to set the two following parameters:
* pg_log_userqueries.log_level: to choose the log level of pg_log_userqueries
(WARNING by default)
* pg_log_userqueries.log_label: to give the prefix of the log line for
pg_log_userqueries logs (defaults to the name of the extension).
Once this configuration is done, restart PostgreSQL.
You can specify exactly what you want to log:
If none of the following four variables is set, all queries issued by superusers will be logged
in all databases, and only those.
If you set any of log_db, log_user, log_addr or log_query superuser, queries won't be
systematically logged, and you'll have to set log_superusers to on to reactivate it.
* pg_log_userqueries.log_superusers: to turn on/off logging of all superusers (off by default)
* pg_log_userqueries.log_db: to give a pipe (|) separated list of database to log.
* pg_log_userqueries.log_user: to give a pipe separated list of user to log.
* pg_log_userqueries.log_addr: to give a pipe separated list of IP addresses to log.
You can use advanced regular expression in that list. For example:
* pg_log_userqueries.log_user="postgres|admin_.*|.*_adm"
will match if the exact username is 'postgres', or if it begins with 'admin_' or
ends with '_adm'.
You can also use pg_log_userqueries to log queries matching a particular regular
expression using the 'pg_log_userqueries.log_query' dedicated configuration directive:
* pg_log_userqueries.log_query: to give a regular expression to log all queries matching this regexp.
For example, to only log calls related to prepared transaction statements, you can set the following:
pg_log_userqueries.log_query="^(PREPARE TRANSACTION|COMMIT PREPARED|ROLLBACK PREPARED)"
This filter will be applied after all other filters. For example if you have set log_user
but the user don't match nothing will be logged even if the query regexp match.
By default log_user, log_db and log_addr are checked in this order and the first matching
value will write to the log if log_query is not set or log_query match. If you want that
all of them, when defined, must match to write to log activate the match_all directive. In
this case it will log statements only when defined filters for log_user, log_db, log_addr
and log_query all match.
* pg_log_queries.match_all='on'
By default, pg_log_userqueries will write queries to PostgreSQL log destination.
A superuser can change this behavior with the pg_log_userqueries.log_destination
parameter. By default at stderr, put syslog if you prefer to use a syslog
daemon. You can also configure the syslog facility and the program
identification:
pg_log_userqueries.syslog_facility='local0'
pg_log_userqueries.syslog_ident='pg_log_userqueries'
As pg_log_userqueries is a shared_preload_library, you'll have to restart PostgreSQL to completely remove it. To temporarily deactivate it, you have a workaround though:
* pg_log_userqueries.file_switchoff: path to a file that must be root-owned. If this file is present, pg_log_userqueries won't log anymore
* pg_log_userqueries.time_switchoff: frequency at which backends will check file_switchoff's presence