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
postgresql-Fri.json
postgresql-Fri.log
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();
pg_current_logfile
------------------------
log/postgresql-Fri.log
testdb=# select pg_current_logfile( 'jsonlog' );
pg_current_logfile
-------------------------
log/postgresql-Fri.json
testdb=# select pg_current_logfile( 'stderr' );
pg_current_logfile
------------------------
log/postgresql-Fri.log
I suspect we will see more and more log crunching applications to switch over the new JSON log format!