PostgreSQL 15: logging in JSON

The freshly released PostgreSQL 15 introduces a lot of new features and improvements, but one, according to me, is going to change the way our favourite database is monitored: the capability to log daemon status in JSON.

Essentially, the log_destination configuration parameter now has another enumerated value: jsonlog. When this value is added to log_destination, PostgreSQL will start to emit JSON structured logs. Here it is a simple configuration example:

% grep log_destination /postgres/15/data/postgresql.conf
log_destination = 'stderr,jsonlog'

and this is how the log directory appears right after the configuration has been reloaded:

% sudo -u postgres ls /postgres/15/data/log -1

Clearly the logs will contain the same values, but in different formats.

There is more: when there’s more than one value set in log_destination, PostgreSQL will store a file named current_logfiles, where each line will represent the format and the current logfile where PostgreSQL has to store the data:

% sudo -u postgres cat /postgres/15/data/current_logfiles
stderr log/postgresql-Fri.log
jsonlog log/postgresql-Fri.json

In this way, not only PostgreSQL, but even the sysadmin can keep track of where the system is going to log right now, and this is useful especially when there’s a log rotation in place.

On the SQL side, the function pg_current_logfile() can optionally accept the log format (the same specified in log_destination) and provide the current log file depending on the choosen format:

testdb=# select pg_current_logfile();

testdb=# select pg_current_logfile( 'jsonlog' );

testdb=# select pg_current_logfile( 'stderr' );

I suspect we will see more and more log crunching applications to switch over the new JSON log format!

The article PostgreSQL 15: logging in JSON has been posted by Luca Ferrari on October 21, 2022