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: https://gitlab.com/l_avrot/pglog.