Get postgresql exact log file name with sql

Question

I get current log file by using SQL without getting in server via ssh. So I use a query like the one below;

SELECT pg_read_binary_file(
 concat_ws('/', 
 current_setting('log_directory'),
 'postgresql-Fri.log'
 )
);

Log files are not too big so i am fine with that. The thing is, I cannot use this query in a generic way. Is there a way to get 'postgresql-Fri.log' from my log_filename setting which is currently postgresql-%a.log but the format can differ?

Answer 1

There is now a pg_current_logfile() function returning this information:

Primary log file name, or log in the requested format, currently in use by the logging collector.

but it's a new feature of PostgreSQL 10, which you probably don't run since it hasn't yet got out as a General Availability release (it's still Release Candidate at the moment).

With previous versions, you're pretty much on your own to figure out the current name, either by interpreting log_filename, or by finding the latest modified, for instance with a combination of pg_ls_dir and pg_stat_file:

SELECT file, -- should be the latest logfile
 (pg_stat_file(current_setting('log_directory')||'/'||file)).modification
 FROM pg_ls_dir(current_setting('log_directory')||'/') as list(file)
 ORDER BY 2 DESC
 LIMIT 1;

This can fail if the server's clock goes backward or if non-log files are dumped into the same directory as the log files.