Re: [HACKERS] For review: Server instrumentation patch

Andreas Pflug wrote:
> Bruce Momjian wrote:
>
> >
> >
> > I don't see how listing the log files relates to editing the confuration
> > files.
>
> Both are remote administration. While we've seen the discussion that one
> aspect (config file editing) should be performed in psql, you assume the
> other aspect (viewing the logfile) to be not interesting. Your
> argumentation doesn't seem consequent to me.

To me monitoring (logfiles) and configuration (postgresql.conf) are
different, but if I can make it easy to do both from psql, great.

I can see making postgresql.conf easy (SET GLOBAL), I am unsure about
making pg_hba.conf easy (and many feel that way), and I am not sure how
adding a log directory listing took makes things significantly easier to
monitor log files.

What I can imagine making things very easy is a readonly GUC that returns
the current log file name. That I think should be in the backend, and I
can see a query that combines that with pg_read_file() that prints the
last 1000 bytes from the file.

SELECT pg_read_file(t1.setting, -1000, 1000);
FROM (SELECT setting FROM pg_settings WHERE NAME = 'log_current_name') AS t1

> >>>it would be something done in C or another application language. Aren't
> >>>the file names already ordered based on their file names, given the
> >>>default pattern, postgresql-%Y-%m-%d_%H%M%S.log?
> >>
> >>The issue is _filtering_, not ordering. Since the log directory might be
> >>directed to a different location, non-pgsql logfiles might be there too.
> >>You'd probably won't expect to retrieve these files over a pgsql connection.
> >
> >
> > Well, if they mix log files and non-log files in the same directory, we
> > would have to filter based on the log_filename directive in the
> > application, or use LIKE in a query.
>
> .. which is what pg_logdir_ls does. And it's robust against filenames
> that don't have valid dates too; imagine postgresql-2005-01-01_crash1.log.

True, but that is more for the application. I don't imagine a user
looking at that from psql would have a problem.

However, you asked for a query that looks like pg_ls_logdir() and here
it is:

SELECT pg_ls_dir
FROM (
SELECT pg_ls_dir(t1.setting)
FROM (SELECT setting FROM pg_settings WHERE NAME = 'log_directory') AS t1
) AS t2,
(SELECT setting FROM pg_settings WHERE NAME = 'log_filename') AS t3
WHERE t2.pg_ls_dir LIKE regexp_replace(t3.setting, '%.*', '') || '%';

The one thing it doesn't do, as you mentioned, is check for valid dates,
but it is certainly more flexible than embedding something in the backend.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073