|
Server : Apache/2.2.2 (Fedora) System : Linux App1.pathumtani.go.th 2.6.20-1.2320.fc5smp #1 SMP Tue Jun 12 19:40:16 EDT 2007 i686 User : apache ( 48) PHP Version : 5.2.9 Disable Function : NONE Directory : /proc/self/root/usr/share/doc/postgresql-8.1.9/html/ |
Upload File : |
<!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.1.9 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="2007-04-20T04:40:08"></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.1.9 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 26.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 option 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 option can be set at server start or in the
<TT
CLASS="FILENAME"
>postgresql.conf</TT
> file. If you turn
this option 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.
This option can be set at server start or in the
<TT
CLASS="FILENAME"
>postgresql.conf</TT
> file.
</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 option 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.
</P
><P
> This parameter is currently ignored (treated as always <TT
CLASS="LITERAL"
>on</TT
>)
because turning it off can cause failure to recover from crashes
even when no hardware or OS-level error occurred. This will be
fixed in some future release, or else the parameter will be removed
entirely.
</P
></DD
><DT
><A
NAME="GUC-WAL-BUFFERS"
></A
><TT
CLASS="VARNAME"
>wal_buffers</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> Number of disk-page buffers allocated in shared memory for WAL data.
The default is 8. 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 option 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.
</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. This option can only be set at server start
or in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
> file.
</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 300 seconds. This option can only be
set at server start or in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
file.
</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. Zero disables the warning.
</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="backup-online.html#BACKUP-ARCHIVING-WAL"
>Section 23.3.1</A
>. This option can only be set at
server start or in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
file.
</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
></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
>