Sophie: postgresql8.2-docs-8.2.14-1mdv2010.0 i586

postgresql8.2-docs-8.2.14-1mdv2010.0.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Write Ahead Log</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.2.14 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Server Configuration"
HREF="runtime-config.html"><LINK
REL="PREVIOUS"
TITLE="Resource Consumption"
HREF="runtime-config-resource.html"><LINK
REL="NEXT"
TITLE="Query Planning"
HREF="runtime-config-query.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-09-04T05:25:47"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.2.14 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="runtime-config-resource.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="runtime-config.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 17. Server Configuration</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="runtime-config.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="runtime-config-query.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="RUNTIME-CONFIG-WAL"
>17.5. Write Ahead Log</A
></H1
><P
>    See also <A
HREF="wal-configuration.html"
>Section 27.3</A
> for details on WAL
    tuning.
   </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RUNTIME-CONFIG-WAL-SETTINGS"
>17.5.1. Settings</A
></H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><A
NAME="GUC-FSYNC"
></A
><TT
CLASS="VARNAME"
>fsync</TT
> (<TT
CLASS="TYPE"
>boolean</TT
>)</DT
><DD
><P
>        If this parameter is on, the <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> server
        will try to make sure that updates are physically written to
        disk, by issuing <CODE
CLASS="FUNCTION"
>fsync()</CODE
> system calls or various
        equivalent methods (see <A
HREF="runtime-config-wal.html#GUC-WAL-SYNC-METHOD"
>wal_sync_method</A
>).
        This ensures that the database cluster can recover to a
        consistent state after an operating system or hardware crash.
       </P
><P
>        However, using <TT
CLASS="VARNAME"
>fsync</TT
> results in a
        performance penalty: when a transaction is committed,
        <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> must wait for the
        operating system to flush the write-ahead log to disk.  When
        <TT
CLASS="VARNAME"
>fsync</TT
> is disabled, the operating system is
        allowed to do its best in buffering, ordering, and delaying
        writes. This can result in significantly improved performance.
        However, if the system crashes, the results of the last few
        committed transactions may be lost in part or whole. In the
        worst case, unrecoverable data corruption may occur.
        (Crashes of the database software itself are <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>not</I
></SPAN
>
        a risk factor here.  Only an operating-system-level crash
        creates a risk of corruption.)
       </P
><P
>        Due to the risks involved, there is no universally correct
        setting for <TT
CLASS="VARNAME"
>fsync</TT
>. Some administrators
        always disable <TT
CLASS="VARNAME"
>fsync</TT
>, while others only
        turn it off during initial bulk data loads, where there is a clear
        restart point if something goes wrong.  Others
        always leave <TT
CLASS="VARNAME"
>fsync</TT
> enabled. The default is
        to enable <TT
CLASS="VARNAME"
>fsync</TT
>, for maximum reliability.
        If you trust your operating system, your hardware, and your
        utility company (or your battery backup), you can consider
        disabling <TT
CLASS="VARNAME"
>fsync</TT
>.
       </P
><P
>        This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
        file or on the server command line.
        If you turn this parameter off, also consider turning off 
        <A
HREF="runtime-config-wal.html#GUC-FULL-PAGE-WRITES"
>full_page_writes</A
>.
       </P
></DD
><DT
><A
NAME="GUC-WAL-SYNC-METHOD"
></A
><TT
CLASS="VARNAME"
>wal_sync_method</TT
> (<TT
CLASS="TYPE"
>string</TT
>)</DT
><DD
><P
>        Method used for forcing WAL updates out to disk.
        If <TT
CLASS="VARNAME"
>fsync</TT
> is off then this setting is irrelevant,
        since updates will not be forced out at all.
        Possible values are:
       </P
><P
></P
><UL
><LI
><P
>         <TT
CLASS="LITERAL"
>open_datasync</TT
> (write WAL files with <CODE
CLASS="FUNCTION"
>open()</CODE
> option <TT
CLASS="SYMBOL"
>O_DSYNC</TT
>)
        </P
></LI
><LI
><P
>         <TT
CLASS="LITERAL"
>fdatasync</TT
> (call <CODE
CLASS="FUNCTION"
>fdatasync()</CODE
> at each commit)
        </P
></LI
><LI
><P
>         <TT
CLASS="LITERAL"
>fsync_writethrough</TT
> (call <CODE
CLASS="FUNCTION"
>fsync()</CODE
> at each commit, forcing write-through of any disk write cache)
        </P
></LI
><LI
><P
>         <TT
CLASS="LITERAL"
>fsync</TT
> (call <CODE
CLASS="FUNCTION"
>fsync()</CODE
> at each commit)
        </P
></LI
><LI
><P
>         <TT
CLASS="LITERAL"
>open_sync</TT
> (write WAL files with <CODE
CLASS="FUNCTION"
>open()</CODE
> option <TT
CLASS="SYMBOL"
>O_SYNC</TT
>)
        </P
></LI
></UL
><P
>        Not all of these choices are available on all platforms.
        The default is the first method in the above list that is supported
        by the platform.
        The <TT
CLASS="LITERAL"
>open_</TT
>* options also use <TT
CLASS="LITERAL"
>O_DIRECT</TT
> if available.
        This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
        file or on the server command line.
       </P
></DD
><DT
><A
NAME="GUC-FULL-PAGE-WRITES"
></A
><TT
CLASS="VARNAME"
>full_page_writes</TT
> (<TT
CLASS="TYPE"
>boolean</TT
>)</DT
><DD
><P
>        When this parameter is on, the <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> server
        writes the entire content of each disk page to WAL during the
        first modification of that page after a checkpoint.
        This is needed because
        a page write that is in process during an operating system crash might
        be only partially completed, leading to an on-disk page
        that contains a mix of old and new data.  The row-level change data
        normally stored in WAL will not be enough to completely restore
        such a page during post-crash recovery.  Storing the full page image
        guarantees that the page can be correctly restored, but at a price
        in increasing the amount of data that must be written to WAL.
        (Because WAL replay always starts from a checkpoint, it is sufficient
        to do this during the first change of each page after a checkpoint.
        Therefore, one way to reduce the cost of full-page writes is to
        increase the checkpoint interval parameters.)
       </P
><P
>        Turning this parameter off speeds normal operation, but
        might lead to a corrupt database after an operating system crash
        or power failure. The risks are similar to turning off
        <TT
CLASS="VARNAME"
>fsync</TT
>, though smaller.  It may be safe to turn off
        this parameter if you have hardware (such as a battery-backed disk
        controller) or file-system software that reduces
        the risk of partial page writes to an acceptably low level (e.g., ReiserFS 4).
       </P
><P
>        Turning off this parameter does not affect use of
        WAL archiving for point-in-time recovery (PITR)
        (see <A
HREF="continuous-archiving.html"
>Section 23.3</A
>).
       </P
><P
>        This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
        file or on the server command line.
        The default is <TT
CLASS="LITERAL"
>on</TT
>.
       </P
></DD
><DT
><A
NAME="GUC-WAL-BUFFERS"
></A
><TT
CLASS="VARNAME"
>wal_buffers</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
>        The amount of memory used in shared memory for WAL data.  The
        default is 64 kilobytes (<TT
CLASS="LITERAL"
>64kB</TT
>).  The setting need only
        be large enough to hold the amount of WAL data generated by one
        typical transaction, since the data is written out to disk at
        every transaction commit.  This parameter can only be set at server
        start.
       </P
><P
>        Increasing this parameter may cause <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
        to request more <SPAN
CLASS="SYSTEMITEM"
>System V</SPAN
> shared
        memory than your operating system's default configuration
        allows. See <A
HREF="kernel-resources.html#SYSVIPC"
>Section 16.4.1</A
> for information on how to
        adjust those parameters, if necessary.
       </P
></DD
><DT
><A
NAME="GUC-COMMIT-DELAY"
></A
><TT
CLASS="VARNAME"
>commit_delay</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
>        Time delay between writing a commit record to the WAL buffer
        and flushing the buffer out to disk, in microseconds. A
        nonzero delay can allow multiple transactions to be committed
        with only one <CODE
CLASS="FUNCTION"
>fsync()</CODE
> system call, if
        system load is high enough that additional transactions become
        ready to commit within the given interval. But the delay is
        just wasted if no other transactions become ready to
        commit. Therefore, the delay is only performed if at least
        <TT
CLASS="VARNAME"
>commit_siblings</TT
> other transactions are
        active at the instant that a server process has written its
        commit record. The default is zero (no delay).
       </P
></DD
><DT
><A
NAME="GUC-COMMIT-SIBLINGS"
></A
><TT
CLASS="VARNAME"
>commit_siblings</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
>        Minimum number of concurrent open transactions to require
        before performing the <TT
CLASS="VARNAME"
>commit_delay</TT
> delay. A larger
        value makes it more probable that at least one other
        transaction will become ready to commit during the delay
        interval. The default is five transactions.
       </P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RUNTIME-CONFIG-WAL-CHECKPOINTS"
>17.5.2. Checkpoints</A
></H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><A
NAME="GUC-CHECKPOINT-SEGMENTS"
></A
><TT
CLASS="VARNAME"
>checkpoint_segments</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
>        Maximum distance between automatic WAL checkpoints, in log
        file segments (each segment is normally 16 megabytes). The
        default is three segments.
        This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
        file or on the server command line.
       </P
></DD
><DT
><A
NAME="GUC-CHECKPOINT-TIMEOUT"
></A
><TT
CLASS="VARNAME"
>checkpoint_timeout</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
>        Maximum time between automatic WAL checkpoints, in
        seconds. The default is five minutes (<TT
CLASS="LITERAL"
>5min</TT
>).
        This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
        file or on the server command line.
       </P
></DD
><DT
><A
NAME="GUC-CHECKPOINT-WARNING"
></A
><TT
CLASS="VARNAME"
>checkpoint_warning</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
>        Write a message to the server log if checkpoints caused by
        the filling of checkpoint segment files happen closer together
        than this many seconds (which suggests that
        <TT
CLASS="VARNAME"
>checkpoint_segments</TT
> ought to be raised).  The default is
        30 seconds (<TT
CLASS="LITERAL"
>30s</TT
>).  Zero disables the warning.
        This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
        file or on the server command line.
       </P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RUNTIME-CONFIG-WAL-ARCHIVING"
>17.5.3. Archiving</A
></H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><A
NAME="GUC-ARCHIVE-COMMAND"
></A
><TT
CLASS="VARNAME"
>archive_command</TT
> (<TT
CLASS="TYPE"
>string</TT
>)</DT
><DD
><P
>        The shell command to execute to archive a completed segment of
        the WAL file series. If this is an empty string (the default),
        WAL archiving is disabled. Any <TT
CLASS="LITERAL"
>%p</TT
> in the string is
        replaced by the path name of the file to archive, and any
        <TT
CLASS="LITERAL"
>%f</TT
> is replaced by the file name only.
        (The path name is relative to the working directory of the server,
        i.e., the cluster's data directory.)
        Use <TT
CLASS="LITERAL"
>%%</TT
> to embed an actual <TT
CLASS="LITERAL"
>%</TT
> character in the
        command. For more information see <A
HREF="continuous-archiving.html#BACKUP-ARCHIVING-WAL"
>Section 23.3.1</A
>.
        This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
        file or on the server command line.
       </P
><P
>        It is important for the command to return a zero exit status if
        and only if it succeeds.  Examples:
</P><PRE
CLASS="PROGRAMLISTING"
>archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
archive_command = 'copy "%p" /mnt/server/archivedir/"%f"'  # Windows</PRE
><P>
       </P
></DD
><DT
><A
NAME="GUC-ARCHIVE-TIMEOUT"
></A
><TT
CLASS="VARNAME"
>archive_timeout</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
>        The <A
HREF="runtime-config-wal.html#GUC-ARCHIVE-COMMAND"
>archive_command</A
> is only invoked on
        completed WAL segments. Hence, if your server generates little WAL
        traffic (or has slack periods where it does so), there could be a
        long delay between the completion of a transaction and its safe
        recording in archive storage.  To put a limit on how old unarchived
        data can be, you can set <TT
CLASS="VARNAME"
>archive_timeout</TT
> to force the
        server to switch to a new WAL segment file periodically.  When this
        parameter is greater than zero, the server will switch to a new
        segment file whenever this many seconds have elapsed since the last
        segment file switch.  Note that archived files that are closed early
        due to a forced switch are still the same length as completely full
        files.  Therefore, it is unwise to use a very short
        <TT
CLASS="VARNAME"
>archive_timeout</TT
> &mdash; it will bloat your archive
        storage.  <TT
CLASS="VARNAME"
>archive_timeout</TT
> settings of a minute or so are
        usually reasonable.  This parameter can only be set in the
        <TT
CLASS="FILENAME"
>postgresql.conf</TT
> file or on the server command line.
       </P
></DD
></DL
></DIV
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="runtime-config-resource.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="runtime-config-query.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Resource Consumption</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="runtime-config.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Query Planning</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>