pglog, a flexible and simple log analyzer

Β· 525 words Β· 3 minute read

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:

  • call pglog.create_logtable_from_file(file, directory, version[, schema][, materialized]);
  • call pglog.create_logtable_from_dir(directory, version[, schema][, materialized]);

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:

  • an error report
  • a detailed error analysis for each level of error
  • an autovacuum report
  • a checkpoints report
  • an hourly checkpoints report
  • a tempfile usage report
  • a tempfile queries report
  • a top n slow queries report

So now, go have some fun with pglog: