Re: Patch to implement pg_current_logfile() function

Le 11/03/2016 15:22, Tom Lane a écrit :
> Gilles Darold <gilles(dot)darold(at)dalibo(dot)com> writes:
>> Le 11/03/2016 10:49, Shulgin, Oleksandr a écrit :
>>> Would it make sense to have it as a symlink instead?
>> The only cons I see is that it can be more "difficult" with some
>> language to gather the real path, but do we really need it? There is
>> also little time where the symlink doesn't exist, this is when it needs
>> to be removed before being recreated to point to the new log file.
> Yeah, a symlink is impossible to update atomically (on most platforms
> anyway). Plain file containing the pathname seems better.
>
> Another point is that we might not necessarily want *only* the pathname in
> there. postmaster.pid has accreted more stuff over time, and this file
> might too. I can see wanting the syslogger PID in there, for example,
> so that onlookers can detect a totally stale file. (Not proposing this
> right now, just pointing out that it's a conceivable future feature.)
>
> regards, tom lane

Here is the patch rewritten to use alternate file
$PGDATA/pg_log_filename to store the current log filename used by
syslogger. All examples used in the first mail of this thread work the
exact same way. If there's no other remarks, I will add the patch to the
next commit fest.

Here are some additional examples with this feature.

To obtain the filling percentage of the log file when log_rotation_size
is used:

postgres=# SELECT pg_current_logfile(), (select setting::int*1000 from
pg_settings where name='log_rotation_size'), a.size size,
((a.size*100)/(select setting::int*1000 from pg_settings where
name='log_rotation_size')) percent_used FROM
pg_stat_file(pg_current_logfile())
a(size,access,modification,change,creation,isdir);

-[ RECORD 1 ]------+----------------------------------------
pg_current_logfile | pg_log/postgresql-2016-03-11_160817.log
log_rotation_size | 10240000
size | 1246000
percent_used | 12

This can help to know if the file is near to be rotated. Or if you use
time based rotation:

postgres=# select pg_current_logfile(), (select setting::int*60 from
pg_settings where name='log_rotation_age')
log_rotation_age,a.access,a.modification, (((extract(epoch from
a.modification) - extract(epoch from a.access)) * 100) / (select
setting::int*60 from pg_settings where name='log_rotation_age'))
percent_used FROM pg_stat_file(pg_current_logfile())
a(size,access,modification,change,creation,isdir);
-[ RECORD 1 ]------+----------------------------------------
pg_current_logfile | pg_log/postgresql-2016-03-11_162143.log
log_rotation_age | 3600
access | 2016-03-11 16:21:43+01
modification | 2016-03-11 16:33:12+01
percent_used | 19.1388888888889

Best regards,

--
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org