PostgreSQL 15: logging in JSONThe 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.
log_destinationconfiguration 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
logdirectory 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!