Re: logfile subprocess and Fancy File Functions - Mailing list pgsql-patches

From Andreas Pflug
Subject Re: logfile subprocess and Fancy File Functions
Date
Msg-id 410102DD.8080908@pse-consulting.de
Whole thread Raw
In response to Re: logfile subprocess and Fancy File Functions  (Bruce Momjian <pgman@candle.pha.pa.us>)
Responses Re: logfile subprocess and Fancy File Functions  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
Bruce Momjian wrote:
>
> Are we done?  Seems pg_file_stat() works fine.  Do we need other
> adjustments?

Here are the documentation changes.

Regards,
Andreas




Index: catalogs.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.89
diff -u -r2.89 catalogs.sgml
--- catalogs.sgml    4 Jul 2004 23:34:23 -0000    2.89
+++ catalogs.sgml    23 Jul 2004 12:16:47 -0000
@@ -3855,6 +3855,11 @@
      </row>

      <row>
+      <entry><link linkend="view-pg-logdir-ls"><structname>pg_logdir_ls</structname></link></entry>
+      <entry>log files in log directory</entry>
+     </row>
+
+     <row>
       <entry><link linkend="view-pg-rules"><structname>pg_rules</structname></link></entry>
       <entry>rules</entry>
      </row>
@@ -3943,6 +3948,50 @@
   </table>

  </sect1>
+ <sect1 id="view-pg-logdir-ls">
+  <title><structname>pg_logdir_ls</structname></title>
+
+  <indexterm zone="view-pg-logdir-ls">
+   <primary>pg_logdir_ls</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_logdir_ls</structname> provides access to
+    log files stored in the log directory.
+  </para>
+
+  <table>
+   <title><structname>pg_logdir_ls</> Columns</title>
+
+   <tgroup cols=3>
+    <thead>
+     <row>
+      <entry>Name</entry>
+      <entry>Type</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+    <tbody>
+     <row>
+      <entry><structfield>filetime</structfield></entry>
+      <entry><type>timestamp</type></entry>
+      <entry>timestamp of log file creation</entry>
+     </row>
+     <row>
+      <entry><structfield>pid</structfield></entry>
+      <entry><type>int4</type></entry>
+      <entry>process id of postmaster that created the logfile</entry>
+     </row>
+     <row>
+      <entry><structfield>filename</structfield></entry>
+      <entry><type>text</type></entry>
+      <entry>full pathname of log file</entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect1>

  <sect1 id="view-pg-locks">
   <title><structname>pg_locks</structname></title>
Index: func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v
retrieving revision 1.214
diff -u -r1.214 func.sgml
--- func.sgml    12 Jul 2004 20:23:47 -0000    1.214
+++ func.sgml    23 Jul 2004 12:17:06 -0000
@@ -2658,8 +2658,10 @@
      function fails and returns null.  To indicate the part of the
      pattern that should be returned on success, the pattern must contain
      two occurrences of the escape character followed by a double quote
-     (<literal>"</>).  The text matching the portion of the pattern
+     (<literal>"</>). The text matching the portion of the pattern
      between these markers is returned.
+     <!-- This comment is to stop misbehaving sgml highlighting from
+     previous " double qoutes -->
     </para>

    <para>
@@ -7455,6 +7457,41 @@
    </para>

    <indexterm zone="functions-misc">
+   <primary>pg_logdir_ls</primary>
+   </indexterm>
+   <indexterm zone="functions-misc">
+   <primary>pg_logfile_rotate</primary>
+   </indexterm>
+   <para>
+    The functions shown in <xref linkend="functions-misc-logfile">
+    deal with the server log file if configured with log_destination
+    <quote>file</quote>.
+   </para>
+
+   <table id="functions-misc-logfile">
+    <title>Server Logfile Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry><literal><function>pg_logfile_rotate</function>()</literal></entry>
+       <entry><type>bool</type></entry>
+       <entry>rotates the server log file</entry>
+      </row>
+      </tbody>
+</tgroup>
+</table>
+<para>
+       <function>pg_logfile_rotate</function> will force the logger
+       process to rotate log files. If logging to file was not enabled
+       ('file' in <literal>log_destination</> configuration
+       parameter), false will be returned.
+</para>
+
+   <indexterm zone="functions-misc">
     <primary>pg_cancel_backend</primary>
    </indexterm>

@@ -7463,6 +7500,10 @@
    </indexterm>

    <indexterm zone="functions-misc">
+    <primary>pg_reload_config</primary>
+   </indexterm>
+
+   <indexterm zone="functions-misc">
     <primary>signal</primary>
     <secondary sortas="backend">backend processes</secondary>
    </indexterm>
@@ -7497,6 +7538,13 @@
        <entry><type>int</type></entry>
        <entry>Terminate a backend process</entry>
       </row>
+      <row>
+       <entry>
+    <literal><function>pg_reload_config</function>()</literal>
+       </entry>
+       <entry><type>int</type></entry>
+       <entry>Reload configuration from postgresql.conf</entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
@@ -7508,6 +7556,196 @@
     <structname>pg_stat_activity</structname> view, or by listing the postgres
     processes on the server.
    </para>
+   <para>
+   <literal><function>pg_reload_config</function></literal> will send
+    a <literal>SIGHUP</> signal to all backends, forcing them to
+    reload their configuration from <literal>postgresql.conf</>.
+   </para>
+
+   <indexterm zone="functions-misc">
+    <primary>pg_file_stat</primary>
+   </indexterm>
+   <indexterm zone="functions-misc">
+    <primary>pg_file_length</primary>
+   </indexterm>
+   <indexterm zone="functions-misc">
+    <primary>pg_file_read</primary>
+   </indexterm>
+   <indexterm zone="functions-misc">
+    <primary>pg_file_write</primary>
+   </indexterm>
+   <indexterm zone="functions-misc">
+    <primary>pg_file_rename</primary>
+   </indexterm>
+   <indexterm zone="functions-misc">
+    <primary>pg_file_unlink</primary>
+   </indexterm>
+   <indexterm zone="functions-misc">
+    <primary>pg_dir_ls</primary>
+   </indexterm>
+
+   <indexterm zone="functions-misc">
+    <primary>generic file</primary>
+   </indexterm>
+
+   <para>
+    The functions shown in <xref
+    linkend="functions-misc-file-table"> implement generic file access
+    and directory listing functions. Use of these functions is
+    restricted to superusers.
+   </para>
+
+   <table id="functions-misc-file-table">
+    <title>Generic File and Directory Access Functions</title>
+    <tgroup cols="3">
+     <thead>
+      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry>
+        <literal><function>pg_file_stat</function>(<parameter>filename_text</parameter>)</literal>
+       </entry>
+       <entry><type>record</type></entry>
+       <entry>Retrieves file stat information</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_length</function>(<parameter>filename_text</parameter>)</literal>
+       </entry>
+       <entry><type>int8</type></entry>
+       <entry>Retrieves length of file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_read</function>(<parameter>filename_text</>, <parameter>offset_int8</>,
<parameter>length_int8</>)</literal>
+       </entry>
+       <entry><type>text</type></entry>
+       <entry>Retrieves contents of text file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_write</function>(<parameter>filename_text</>, <parameter>contents_text</>,
<parameter>append_bool</>)</literal>
+       </entry>
+       <entry><type>int8</type></entry>
+       <entry>Writes data to text file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_ren</function>(<parameter>oldname_text</>, <parameter>newname_text</>)</literal>
+       </entry>
+       <entry><type>bool</type></entry>
+       <entry>Renames file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_ren</function>(<parameter>oldname_text</>, <parameter>newname_text</>)</literal>
+       </entry>
+       <entry><type>bool</type></entry>
+       <entry>Renames file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_file_ren</function>(<parameter>oldname_text</>, <parameter>newname_text</>,
<parameter>archivename_text</>)</literal>
+       </entry>
+       <entry><type>bool</type></entry>
+       <entry>Renames file and previous file</entry>
+      </row>
+
+      <row>
+       <entry>
+        <literal><function>pg_file_unlink</function>(<parameter>filename_text</parameter>)</literal>
+       </entry>
+       <entry><type>bool</type></entry>
+       <entry>unlinks/deletes file</entry>
+      </row>
+      <row>
+       <entry>
+        <literal><function>pg_dir_ls</function>(<parameter>filename_text</>, <parameter>fullpath_bool</>)</literal>
+       </entry>
+       <entry><type>setof text</type></entry>
+       <entry>Retrieves list of filenames in a directory</entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+   <para>
+    All file functions take a <parameter>filename_text</> argument,
+    which may specify an absolute path or is evaluated relative to the
+    <application>cluster directory</> <literal>$PG_DATA</>. While most
+    functions will throw an SQL error causing interrupting the
+    execution if problems are encountered,
+    <literal><function>pg_file_stat</function></literal>
+    and <literal><function>pg_file_length</function></literal> will
+    return -1 if the file is not found,
+    <literal><function>pg_file_rename</function></literal> and
+    <literal><function>pg_file_unlink</function></literal> will return
+    false if the files remain unchanged.
+   </para>
+   <para>
+    <literal><function>pg_file_stat</function></literal> returns
+    metafile information about a given file in a record. From this,
+    the current file length, time of creation, time of last acces, time
+    of last modification can be retrieved, as well as a flag if the
+    name specifies a directory or not. Example:
+<programlisting>
+SELECT len, ctime, atime, mtime, isdir
+  FROM pg_file_stat('postgresql.conf')
+    AS st(len int8,
+          ctime  timestamp, atime timestamp, mtime timestamp,
+          isdir bool);
+</programlisting>
+    If the file metadata is not accessible, the length is reported as -1
+    and the timestamp fields will be NULL.
+   </para>
+   <para>
+    <literal><function>pg_file_len</function></literal> returns the
+    length of the given file or -1 if the file metadata is not accessible.
+   </para>
+   <para>
+    <literal><function>pg_file_read</function></literal> returns the
+    contents of a text file partially or in whole. The
+    <parameter>offset_int8</> parameter specifies a file offset from
+    the beginning of the file, the <parameter>length_int8</> parameter
+    the maximum number of bytes to retrieve.
+   </para>
+   <para>
+    <literal><function>pg_file_write</function></literal> writes data
+    to a text file. If the <parameter>append_bool</> parameter is
+    true, the data is appended to an existing file; if it is false,
+    the file is created.
+   </para>
+   <para>
+    <literal><function>pg_file_rename</function></literal> comes in
+    two flavours. The first covers the standard case of renaming a file from
+    an old name to a new name, while the second variation will perform
+    chained renames: oldname is renamed to archivename, and oldname is
+    renamed to newname. This can be used to replace files in a safe way,
+    minimizing the risk of letting the system in an undefined
+    state. If renaming was done successfully, true is returned.
+   </para>
+   <para>
+    This Example replaces postgresql.conf with a new version, keeping the
+    previous version archived:
+<programlisting>
+SELECT pg_file_rename('postgresql.conf.tmp',
+                      'postgresql.conf',
+                      'postgresql.conf.bak');
+</programlisting>
+   </para>
+   <para>
+    <literal><function>pg_file_unlink</function></literal> will unlink
+    or delete the file. If successful, true is returned.
+   </para>
+   <para>
+    <literal><function>pg_dir_ls</function></literal> returns a set of
+    text containing filenames found in the directory. the
+    <parameter>fullpath_bool</> specifies if the returned text should
+    be extended to the full absolute path or contain the filename only.
+   </para>
   </sect1>

  <sect1 id="functions-array">
Index: maintenance.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/maintenance.sgml,v
retrieving revision 1.35
diff -u -r1.35 maintenance.sgml
--- maintenance.sgml    16 May 2004 19:34:46 -0000    1.35
+++ maintenance.sgml    23 Jul 2004 12:17:07 -0000
@@ -474,25 +474,56 @@
    performance.  Use a <literal>-</> at the start of the file name
    in the <application>syslog</> config file to disable this behavior.
   </para>
-
   <para>
-   You may find it more useful to pipe the
-   <systemitem>stderr</> of the <command>postmaster</> to some type of
-   log rotation program. If you start the server with
-   <command>pg_ctl</>, then the <systemitem>stderr</> of the <command>postmaster</command>
+   In <productname>PostgreSQL</> 7.5, a new internal logging rotation
+   subprocess has been implemented. To activate the logger process,
+   set the configurations parameter <literal>log_destination</> to
+   'file' in <filename>postgresql.conf</>. <productname>PostgreSQL</>
+   will write all logging output to text files in a subdirectory,
+   which can be configured with the configurations parameter
+   <literal>log_directory</>. To read the logfiles, you can access
+   them via the file system, or use the <link linkend=view-pg-logdir-ls>
+   <command>pg_logdir_ls</></link> view and the <link linkend="functions-misc-file-table">
+   <command>pg_file_read()</></link> function.
+  </para>
+  <para>
+   The log file names are created using an internal naming scheme,
+   that may be modified using the <literal>log_filename_prefix</>
+   parameter. The name is assembled according to the following scheme:
+<screen>
+NNNYYYY-MM-DD_HHMMSS_PPPPP.log
+</screen>
+   NNN is the user configurable <literal>log_filename_prefix</>; its
+   length may vary while the rest of the name will be of constant
+   length containing the file date in ANSI format, the time and the
+   process id of the <command>postmaster</>.
+  </para>
+  <para>
+   The <command>system logger</> process will redirect all stderr
+   output to the logfiles, as soon as it is running. Error messages
+   issued before the <command>system logger</> process is running will
+   still go to stderr, so make sure you still catch these startup
+   messages by redirecting the <command>postmaster's</> output.
+  </para>
+  <para>
+   There are some system generated error messages (e.g. load errors of
+   dynamically loaded modules) that are written to stderr, and can not
+   be caught using the 'syslog' destination. Only 'stderr' and 'file'
+   options guarantee to catch these runtime messages as well.
+  </para>
+  <para>
+   You may like to pipe the
+   <systemitem>stderr</> of the <command>postmaster</> to some other
+   log rotation program. If you start the server with <command>pg_ctl</>,
+   then the <systemitem>stderr</> of the <command>postmaster</command>
    is already redirected to <systemitem>stdout</>, so you just need a
    pipe command:

 <programlisting>
 pg_ctl start | rotatelogs /var/log/pgsql_log 86400
 </programlisting>
-
-   The <productname>PostgreSQL</> distribution doesn't include a
-   suitable log rotation program, but there are many available on the
-   Internet. For example, the <application>rotatelogs</application>
-   tool included in the <productname>Apache</productname> distribution
-   can be used with <productname>PostgreSQL</productname>.
   </para>
+
  </sect1>
 </chapter>

Index: monitoring.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/monitoring.sgml,v
retrieving revision 1.26
diff -u -r1.26 monitoring.sgml
--- monitoring.sgml    26 Mar 2004 03:18:28 -0000    1.26
+++ monitoring.sgml    23 Jul 2004 12:17:09 -0000
@@ -50,9 +50,11 @@

 <screen>
 $ ps auxww | grep ^postgres
-postgres   960  0.0  1.1  6104 1480 pts/1    SN   13:17   0:00 postmaster -i
-postgres   963  0.0  1.1  7084 1472 pts/1    SN   13:17   0:00 postgres: stats buffer process
-postgres   965  0.0  1.1  6152 1512 pts/1    SN   13:17   0:00 postgres: stats collector process
+postgres   960  0.0  1.2  8960 1280 pts/1    SN   13:17   0:00 postmaster -i
+postgres   962  0.0  0.4  6301 1421 pts/1    SN   13:17   0:00 postgres: system logger process
+postgres   964  0.0  0.6  6104 1480 pts/1    SN   13:17   0:00 postgres: writer process
+postgres   965  0.0  1.1  7084 1472 pts/1    SN   13:17   0:00 postgres: stats buffer process
+postgres   966  0.0  1.1  6152 1512 pts/1    SN   13:17   0:00 postgres: stats collector process
 postgres   998  0.0  2.3  6532 2992 pts/1    SN   13:18   0:00 postgres: tgl runbug 127.0.0.1 idle
 postgres  1003  0.0  2.4  6532 3128 pts/1    SN   13:19   0:00 postgres: tgl regression [local] SELECT waiting
 postgres  1016  0.1  2.4  6532 3080 pts/1    SN   13:19   0:00 postgres: tgl regression [local] idle in transaction
@@ -62,12 +64,18 @@
    platforms, as do the details of what is shown.  This example is from a
    recent Linux system.)  The first process listed here is the
    <application>postmaster</>, the master server process.  The command arguments
-   shown for it are the same ones given when it was launched.  The next two
-   processes implement the statistics collector, which will be described in
-   detail in the next section.  (These will not be present if you have set
-   the system not to start the statistics collector.)  Each of the remaining
-   processes is a server process handling one client connection.  Each such
-   process sets its command line display in the form
+   shown for it are the same ones given when it was launched. The
+   <application>writer process</> manages the dirty buffers, flushing
+   them to disk when appropriate. The <application>system logger</>
+   process catches message and error output of all <application>backends</>
+   and writes them to logfiles, rotating them if necessary. This process
+   will only be present if <literal>log_destination</> 'file' is selected.
+   The next two processes implement the statistics collector, which
+   will be described in  detail in the next section. (These will not
+   be present if you have set the system not to start the statistics
+   collector.)  Each of the remaining processes is a server process
+   handling one client connection.  Each such process sets its command
+   line display in the form

 <screen>
 postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <replaceable>activity</>
Index: runtime.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.269
diff -u -r1.269 runtime.sgml
--- runtime.sgml    11 Jul 2004 00:18:40 -0000    1.269
+++ runtime.sgml    23 Jul 2004 12:17:19 -0000
@@ -1768,13 +1768,39 @@
       <term><varname>log_destination</varname> (<type>string</type>)</term>
       <listitem>
        <para>
-    <productname>PostgreSQL</productname> supports several methods
-     for loggning, including <systemitem>stderr</systemitem> and
-     <systemitem>syslog</systemitem>. On Windows,
-     <systemitem>eventlog</systemitem> is also supported. Set this
-     option to a list of desired log destinations separated by a
-     comma. The default is to log to <systemitem>stderr</systemitem>
-     only. This option must be set at server start.
+        <productname>PostgreSQL</> supports several methods for logging,
+        including <systemitem>stderr</>, <systemitem>file></> and
+        <systemitem>syslog</>. On Windows, <systemitem>eventlog</> is
+        also supported. Set this option to a list of desired log
+        destinations separated by a comma. The default is to log to
+        <systemitem>stderr</> only. This option must be set at server start.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="guc-log-directory" xreflabel="log_directory">
+      <term><varname>log_directory</> (<type>string</>)</term>
+      <listitem>
+       <para>
+        If <systemitem>file</> is selected in <literal>log_destination</>,
+        <productname>PostgreSQL</> will write log files to this directory.
+        It may be specified as absolute path or relative to the
+        <application>cluster directory</>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry id="guc-log-filename-prefix" xreflabel="log_filename_prefix">
+      <term><varname>log_directory</> (<type>string</>)</term>
+      <listitem>
+       <para>
+        If <systemitem>file</> is enabled by the <literal>log_destination</>
+        option, <productname>PostgreSQL</> will create the log files
+        according to a naming scheme that includes the timestamp of
+        the start time of the logfile, and the process id of the
+        <command>postmaster</>. This internally selected name is
+        prefixed by a user selectable string, which may be changed
+        using the <literal>log_filename_prefix</> option.
        </para>
       </listitem>
      </varlistentry>

pgsql-patches by date:

Previous
From: "Magnus Hagander"
Date:
Subject: Re: initdb authentication
Next
From: Tom Lane
Date:
Subject: Re: logfile subprocess and Fancy File Functions