README.md

pg_sqlog

An extension providing access to PostgreSQL logs through SQL interface.

Description

pg_sqlog allows to query a foreign table, pointing to a log, recorded in a CSV format. It has special functions to extract the query duration of each query, as well as to group similar queries together.

Prerequisites

Set log_min_duration_statement to a non-negative value in order to record the slow queries.

log_min_duration_statement  = 1000  # logs every query taking more than 1 second

This extension depends on file_fdw as well as on the following configuration directives.

log_destination   = 'syslog,csvlog' # 'csvlog' should be present
log_filename      = 'postgresql.%F' # any combination of %F, %Y, %m, %d, %a
logging_collector = 'on'
log_rotation_age  = '1d'            # at max 1 log file per day
log_rotation_size = 0
log_truncate_on_rotation = 'on'

To use the special autovacuum and autoanalyze reports you need to set log_autovacuum_min_duration to a non-negative value.

log_autovacuum_min_duration = 0

Tables

• sqlog.log - a template table, pointing to a log file, generated through a given day. It could be either queried through the special sqlog.log() set of function, or directly in a combination with the sqlog.set_date() function. By default the date is set to the last call to sqlog.log() or sqlog.set_date().

Functions

• sqlog.log([timestamp]) - a set returning function, giving the contents of the PostgreSQL log file for a given day. If timestamp is omitted, then the current day's log is returned. Calls sqlog.set_date() implicitly.
• sqlog.set_date([timestamp]) - a function to control the sqlog.log filename option. Once set to a given date, it stays that way until another call to it. Note that calling this function will influence the contents of the sqlog.log table for all the other concurrent sessions as well (if any).
• sqlog.duration(text) - extracts the query duration from the message field in milliseconds.
• sqlog.preparable_query(text) - replaces all the possible arguments of a query found in the message field with question marks, thus providing a preparable query, effectively grouping similar queries together.
• sqlog.summary(text, int [, int]) - strip meta data from the query and display the first N, the last N, or both characters of it. By default the first 30 and the last 30 characters will be shown. If -1 is passed for the second argument, no trimming will occur.
• sqlog.temporary_file_size(text) - extracts the file size of each temporary file that has been created and logged, according to the log_temp_files configuration option. Pass sqlog.message as argument.
• sqlog.autovacuum([timestamp]) - a set returning function, giving human readable report of the autovacuum runs for a given day. Calls sqlog.set_date() implicitly.
• sqlog.autoanalyze([timestamp]) - a set returning function, giving human readable report of the autoanalyze runs for a given day. Calls sqlog.set_date() implicitly.

Installation

After making the project, copy the conf/pg_sqlog.conf file to the conf.d/ PostgreSQL directory (or make the appropriate changes to your postgresql.conf file directly) and restart the service.

Upgrading from PostgreSQL 12 (or older)

Drop and create the extension to have proper backend_type parsing.

Upgrading from PostgreSQL 13 (or older)

Drop and create the extension to have proper leader_pid and query_id parsing.

Examples

Get a summary of the errors reported for the day.

postgres=# SELECT error_severity, COUNT(*) FROM sqlog.log() GROUP BY 1;
 error_severity | count
----------------+-------
 FATAL          |     6
 WARNING        |    27
 LOG            |   949
 ERROR          |    10
(4 rows)

Get the top 3 slowest queries of the day.

SELECT
  AVG(sqlog.duration(message)),
  COUNT(*),
  sqlog.preparable_query(message)
FROM
  sqlog.log()
WHERE
  message ~ '^duration'
GROUP BY
  3
ORDER BY
  2 DESC
LIMIT
  3;

                       preparable_query                       |          avg          | count
--------------------------------------------------------------+-----------------------+-------
 SELECT pg_sleep(?)                                           | 9002.774              |     2
 SELECT id, name FROM invoice WHERE status > ?                | 4367.3729834738293848 |    12
 UPDATE app SET credit=?+overdraft WHERE id=? and overdraft>? | 1158.1232790697674419 |    43
(3 rows)

Show the summary for some queries from yesterday.

SELECT
  log_time::time,
  sqlog.duration(message),
  sqlog.summary(message)
FROM
  sqlog.log('yesterday')
WHERE
  message ~ '^duration';

   log_time      | duration |                              summary                              
-----------------+----------+------------------------------------------------------------------
 19:09:44.942+00 | 8604.054 | SELECT DISTINCT (accounts_uuid ... _jupiterprofile"."venus" = 0))
 19:37:52.766+00 | 1209.055 | UPDATE "infrastructure_jupiter ... be5c-5gb7-9d7b-b30bg6cf5b56'))
 19:40:05.506+00 | 1628.792 | SELECT (date_trunc('hour', tim ... r', time)) ORDER BY "hour" ASC
(3 rows)

Get a random autovacuum report for the day.

postgres=# SELECT * FROM sqlog.autovacuum() limit 1;
-[ RECORD 1 ]-------------+---------------------------
log_time                  | 2018-11-06 06:03:00.178+00
database                  | db
schema_name               | public
table_name                | account
idx_scans                 | 1
pages_removed             | 1
pages_remain              | 16
pages_skipped_pins        | 0
pages_skipped_frozen      | 0
tuples_removed            | 455
tuples_remain             | 27
tuples_dead_not_removable | 0
oldest_xmin               | 224250521
buffer_hits               | 187
buffer_misses             | 0
buffer_dirtied            | 7
read_mbs                  | 0.000
write_mbs                 | 0.033
cpu_user                  | 0.04
cpu_system                | 0.02
elapsed                   | 1.64

Querying slave node logs

Analyzing queries on a slave node is also possible. In order to change the date make a call to sqlog.set_date([date]) on the master node prior to querying sqlog.log on the slave.