pglog, a flexible and simple log analyzer

September 17, 2021
Administration PostgreSQL

As a consultant, I have to analyze various databases daily. I need to be able to grasp what the major problems are quickly and find the best way to solve them. Analyzing logs is a very efficient way to find problems. I was frustrated with the existing tools as I couldn’t deep dive into a problem to find more accurate data related to that problem only. That’s where I decided to use SQL to analyze my customers' logfiles.

What you need

Before getting right into the extension, you need your logs to be correctly formatted with enough information to do something with them. So, here are the log settings I ask my customers to comply with. Only the csv formatting is mandatory. If others are not set as recommended, I’ll simply have less information about what’s going on and I might miss something.

Parameter Setting Comment
log_destination csvlog Mandatory
logging_collector on Mandatory for csv loggin
log_min_duration_statement 250 ms For a transactional workload, for Business Intelligence worload, 1s or 5s can be fine
log_autovacuum_min_duration 0 To see all autovacuum operation occuring
log_checkpoints on To see when checkpoints occur
log_connections on To see the rythm of connections
log_disconnections on To see how long sessions are
log_lock_waits on To see when locks occur
log_temp_files 0 To see if PostgreSQL is swapping

How to install it

pglog is an extension. It will also install the file_fdw extension which is part of the extensions natively provided with Postgres.

pglog shouldn’t be installed on a production cluster. This extension is meant to analyze your logs (regularly or after something happened). It should be installed on your laptop or desktop.

You will need to have at least Postgres 11 and it can analyze logs from Postgres 9.0 to 14.

Getting the extension

First, clone the repository and build the extension:

git clone <pglog_project>
cd pglog
make install

Creating the extension

Then you can create the extension.

create extension pglog cascade;

How to create the pglog table

You need either a log file or a directory where you stored several log files. If you give a directory, it will create a partitioned table.

You can use either:

The version of Postgres needs to be provided. It’s the version of the Postgres engine that outputs the logs you’re trying to analyze. The schema can be provided. If it’s not, the pglog table will be in the pglog schema. materialized will make the pglog table a real table (so data will be imported) while if set to false, it will use a foreign-data wrapper. It means that the creation of the pglog table will be faster, but querying it will be slower.

Dashboards

Some standard dashboards are already provided with pglog, but don’t hesitate to write your own (or customize the ones already written).

From now on, pglog provides:

So now, go have some fun with pglog: https://gitlab.com/l_avrot/pglog.

3 ways to auto-increment with Postgres

September 30, 2021
Administration PostgreSQL

My Postgres environment

September 23, 2021
PostgreSQL

A Tip a day takes the GUI away!

April 4, 2021
Administration PostgreSQL