<!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 9.0.15 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="2014-01-24T13:16:52"></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"
><A
HREF="index.html"
>PostgreSQL 9.0.15 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Resource Consumption"
HREF="runtime-config-resource.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="runtime-config.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 18. Server Configuration</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Query Planning"
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"
>18.5. Write Ahead Log</A
></H1
><P
> See also <A
HREF="wal-configuration.html"
>Section 29.4</A
> for details on WAL
and checkpoint tuning.
</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RUNTIME-CONFIG-WAL-SETTINGS"
>18.5.1. Settings</A
></H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><A
NAME="GUC-WAL-LEVEL"
></A
><TT
CLASS="VARNAME"
>wal_level</TT
> (<TT
CLASS="TYPE"
>enum</TT
>)</DT
><DD
><P
> <TT
CLASS="VARNAME"
>wal_level</TT
> determines how much information is written
to the WAL. The default value is <TT
CLASS="LITERAL"
>minimal</TT
>, which writes
only the information needed to recover from a crash or immediate
shutdown. <TT
CLASS="LITERAL"
>archive</TT
> adds logging required for WAL archiving,
and <TT
CLASS="LITERAL"
>hot_standby</TT
> further adds information required to run
read-only queries on a standby server.
This parameter can only be set at server start.
</P
><P
> In <TT
CLASS="LITERAL"
>minimal</TT
> level, WAL-logging of some bulk
operations can be safely skipped, which can make those
operations much faster (see <A
HREF="populate.html#POPULATE-PITR"
>Section 14.4.7</A
>).
Operations in which this optimization can be applied include:
<P
></P
><TABLE
BORDER="0"
><TBODY
><TR
><TD
><TT
CLASS="COMMAND"
>CREATE TABLE AS</TT
></TD
></TR
><TR
><TD
><TT
CLASS="COMMAND"
>CREATE INDEX</TT
></TD
></TR
><TR
><TD
><TT
CLASS="COMMAND"
>CLUSTER</TT
></TD
></TR
><TR
><TD
><TT
CLASS="COMMAND"
>COPY</TT
> into tables that were created or truncated in the same
transaction</TD
></TR
></TBODY
></TABLE
><P
></P
>
But minimal WAL does not contain
enough information to reconstruct the data from a base backup and the
WAL logs, so either <TT
CLASS="LITERAL"
>archive</TT
> or <TT
CLASS="LITERAL"
>hot_standby</TT
>
level must be used to enable
WAL archiving (<A
HREF="runtime-config-wal.html#GUC-ARCHIVE-MODE"
>archive_mode</A
>) and streaming
replication.
</P
><P
> In <TT
CLASS="LITERAL"
>hot_standby</TT
> level, the same information is logged as
with <TT
CLASS="LITERAL"
>archive</TT
>, plus information needed to reconstruct
the status of running transactions from the WAL. To enable read-only
queries on a standby server, <TT
CLASS="VARNAME"
>wal_level</TT
> must be set to
<TT
CLASS="LITERAL"
>hot_standby</TT
> on the primary, and
<A
HREF="runtime-config-wal.html#GUC-HOT-STANDBY"
>hot_standby</A
> must be enabled in the standby. It is
thought that there is
little measurable difference in performance between using
<TT
CLASS="LITERAL"
>hot_standby</TT
> and <TT
CLASS="LITERAL"
>archive</TT
> levels, so feedback
is welcome if any production impacts are noticeable.
</P
></DD
><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
> While turning off <TT
CLASS="VARNAME"
>fsync</TT
> is often a performance
benefit, this can result in unrecoverable data corruption in
the event of a power failure or system crash. Thus it
is only advisable to turn off <TT
CLASS="VARNAME"
>fsync</TT
> if
you can easily recreate your entire database from external
data.
</P
><P
> Examples of safe circumstances for turning off
<TT
CLASS="VARNAME"
>fsync</TT
> include the initial loading of a new
database cluster from a backup file, using a database cluster
for processing a batch of data after which the database
will be thrown away and recreated,
or for a read-only database clone which
gets recreated frequently and is not used for failover. High
quality hardware alone is not a sufficient justification for
turning off <TT
CLASS="VARNAME"
>fsync</TT
>.
</P
><P
> In many situations, turning off <A
HREF="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT"
>synchronous_commit</A
>
for noncritical transactions can provide much of the potential
performance benefit of turning off <TT
CLASS="VARNAME"
>fsync</TT
>, without
the attendant risks of data corruption.
</P
><P
> <TT
CLASS="VARNAME"
>fsync</TT
> 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-SYNCHRONOUS-COMMIT"
></A
><TT
CLASS="VARNAME"
>synchronous_commit</TT
> (<TT
CLASS="TYPE"
>boolean</TT
>)</DT
><DD
><P
> Specifies whether transaction commit will wait for WAL records
to be written to disk before the command returns a <SPAN
CLASS="QUOTE"
>"success"</SPAN
>
indication to the client. The default, and safe, setting is
<TT
CLASS="LITERAL"
>on</TT
>. When <TT
CLASS="LITERAL"
>off</TT
>, there can be a delay between
when success is reported to the client and when the transaction is
really guaranteed to be safe against a server crash. (The maximum
delay is three times <A
HREF="runtime-config-wal.html#GUC-WAL-WRITER-DELAY"
>wal_writer_delay</A
>.) Unlike
<A
HREF="runtime-config-wal.html#GUC-FSYNC"
>fsync</A
>, setting this parameter to <TT
CLASS="LITERAL"
>off</TT
>
does not create any risk of database inconsistency: an operating
system or database crash might
result in some recent allegedly-committed transactions being lost, but
the database state will be just the same as if those transactions had
been aborted cleanly. So, turning <TT
CLASS="VARNAME"
>synchronous_commit</TT
> off
can be a useful alternative when performance is more important than
exact certainty about the durability of a transaction. For more
discussion see <A
HREF="wal-async-commit.html"
>Section 29.3</A
>.
</P
><P
> This parameter can be changed at any time; the behavior for any
one transaction is determined by the setting in effect when it
commits. It is therefore possible, and useful, to have some
transactions commit synchronously and others asynchronously.
For example, to make a single multistatement transaction commit
asynchronously when the default is the opposite, issue <TT
CLASS="COMMAND"
>SET
LOCAL synchronous_commit TO OFF</TT
> within the transaction.
</P
></DD
><DT
><A
NAME="GUC-WAL-SYNC-METHOD"
></A
><TT
CLASS="VARNAME"
>wal_sync_method</TT
> (<TT
CLASS="TYPE"
>enum</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 WAL file 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</TT
> (call <CODE
CLASS="FUNCTION"
>fsync()</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"
>open_sync</TT
> (write WAL files with <CODE
CLASS="FUNCTION"
>open()</CODE
> option <TT
CLASS="SYMBOL"
>O_SYNC</TT
>)
</P
></LI
></UL
><P
> The <TT
CLASS="LITERAL"
>open_</TT
>* options also use <TT
CLASS="LITERAL"
>O_DIRECT</TT
> if available.
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, except that <TT
CLASS="LITERAL"
>fdatasync</TT
> is the default on
Linux. The default is not necessarily ideal; it might be
necessary to change this setting or other aspects of your system
configuration in order to create a crash-safe configuration or
achieve optimal performance.
These aspects are discussed in <A
HREF="wal-reliability.html"
>Section 29.1</A
>.
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 the price
of 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 either unrecoverable data corruption, or silent
data corruption, after a system failure. The risks are similar to turning off
<TT
CLASS="VARNAME"
>fsync</TT
>, though smaller, and it should be turned off
only based on the same circumstances recommended for that parameter.
</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 24.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 might 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 17.4.1</A
> for information on how to
adjust those parameters, if necessary.
</P
></DD
><DT
><A
NAME="GUC-WAL-WRITER-DELAY"
></A
><TT
CLASS="VARNAME"
>wal_writer_delay</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> Specifies the delay between activity rounds for the WAL writer.
In each round the writer will flush WAL to disk. It then sleeps for
<TT
CLASS="VARNAME"
>wal_writer_delay</TT
> milliseconds, and repeats. The default
value is 200 milliseconds (<TT
CLASS="LITERAL"
>200ms</TT
>). Note that on many
systems, the effective resolution of sleep delays is 10 milliseconds;
setting <TT
CLASS="VARNAME"
>wal_writer_delay</TT
> to a value that is not a multiple
of 10 might have the same results as setting it to the next higher
multiple of 10. 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-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"
>18.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 number of log file segments between automatic WAL
checkpoints (each segment is normally 16 megabytes). The default
is three segments. Increasing this parameter can increase the
amount of time needed for crash recovery.
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
>).
Increasing this parameter can increase the amount of time needed
for crash recovery.
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-COMPLETION-TARGET"
></A
><TT
CLASS="VARNAME"
>checkpoint_completion_target</TT
> (<TT
CLASS="TYPE"
>floating point</TT
>)</DT
><DD
><P
> Specifies the target of checkpoint completion, as a fraction of
total time between checkpoints. The default is 0.5.
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"
>18.5.3. Archiving</A
></H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><A
NAME="GUC-ARCHIVE-MODE"
></A
><TT
CLASS="VARNAME"
>archive_mode</TT
> (<TT
CLASS="TYPE"
>boolean</TT
>)</DT
><DD
><P
> When <TT
CLASS="VARNAME"
>archive_mode</TT
> is enabled, completed WAL segments
are sent to archive storage by setting
<A
HREF="runtime-config-wal.html#GUC-ARCHIVE-COMMAND"
>archive_command</A
>.
<TT
CLASS="VARNAME"
>archive_mode</TT
> and <TT
CLASS="VARNAME"
>archive_command</TT
> are
separate variables so that <TT
CLASS="VARNAME"
>archive_command</TT
> can be
changed without leaving archiving mode.
This parameter can only be set at server start. <TT
CLASS="VARNAME"
>wal_level</TT
>
must be set to <TT
CLASS="LITERAL"
>archive</TT
> or <TT
CLASS="LITERAL"
>hot_standby</TT
> to
enable <TT
CLASS="VARNAME"
>archive_mode</TT
>.
</P
></DD
><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 WAL file
segment. 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 only the file name.
(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. It is important for the command to return a zero
exit status only if it succeeds. For more information see
<A
HREF="continuous-archiving.html#BACKUP-ARCHIVING-WAL"
>Section 24.3.1</A
>.
</P
><P
> This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
file or on the server command line. It is ignored unless
<TT
CLASS="VARNAME"
>archive_mode</TT
> was enabled at server start.
If <TT
CLASS="VARNAME"
>archive_command</TT
> is an empty string (the default) while
<TT
CLASS="VARNAME"
>archive_mode</TT
> is enabled, WAL archiving is temporarily
disabled, but the server continues to accumulate WAL segment files in
the expectation that a command will soon be provided. Setting
<TT
CLASS="VARNAME"
>archive_command</TT
> to a command that does nothing but
return true, e.g. <TT
CLASS="LITERAL"
>/bin/true</TT
> (<TT
CLASS="LITERAL"
>REM</TT
> on
Windows), effectively disables
archiving, but also breaks the chain of WAL files needed for
archive recovery, so it should only be used in unusual circumstances.
</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 for
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 limit 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, and there has been any database activity,
including a single checkpoint. (Increasing
<TT
CLASS="VARNAME"
>checkpoint_timeout</TT
> will reduce unnecessary
checkpoints on an idle system.)
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
> — 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
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="RUNTIME-CONFIG-REPLICATION"
>18.5.4. Streaming Replication</A
></H2
><P
> These settings control the behavior of the built-in
<I
CLASS="FIRSTTERM"
>streaming replication</I
> feature.
These parameters would be set on the primary server that is
to send replication data to one or more standby servers.
</P
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><A
NAME="GUC-MAX-WAL-SENDERS"
></A
><TT
CLASS="VARNAME"
>max_wal_senders</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> Specifies the maximum number of concurrent connections from
standby servers (i.e., the
maximum number of simultaneously running WAL sender
processes). The default is zero, meaning replication is
disabled. WAL sender processes count towards the total number
of connections, so the parameter cannot be set higher than
<A
HREF="runtime-config-connection.html#GUC-MAX-CONNECTIONS"
>max_connections</A
>. This parameter can only
be set at server start. <TT
CLASS="VARNAME"
>wal_level</TT
> must be set
to <TT
CLASS="LITERAL"
>archive</TT
> or <TT
CLASS="LITERAL"
>hot_standby</TT
> to allow
connections from standby servers.
</P
></DD
><DT
><A
NAME="GUC-WAL-SENDER-DELAY"
></A
><TT
CLASS="VARNAME"
>wal_sender_delay</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> Specifies the delay between activity rounds for WAL sender processes.
In each round the WAL sender sends any WAL accumulated since the last
round to the standby server. It then sleeps for
<TT
CLASS="VARNAME"
>wal_sender_delay</TT
> milliseconds, and repeats. The default
value is 200 milliseconds (<TT
CLASS="LITERAL"
>200ms</TT
>).
Note that on many systems, the effective resolution of sleep delays is
10 milliseconds; setting <TT
CLASS="VARNAME"
>wal_sender_delay</TT
> to a value that
is not a multiple of 10 might have the same results as setting it to
the next higher multiple of 10. 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-WAL-KEEP-SEGMENTS"
></A
><TT
CLASS="VARNAME"
>wal_keep_segments</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> Specifies the minimum number of past log file segments kept in the
<TT
CLASS="FILENAME"
>pg_xlog</TT
>
directory, in case a standby server needs to fetch them for streaming
replication. Each segment is normally 16 megabytes. If a standby
server connected to the primary falls behind by more than
<TT
CLASS="VARNAME"
>wal_keep_segments</TT
> segments, the primary might remove
a WAL segment still needed by the standby, in which case the
replication connection will be terminated. (However, the standby
server can recover by fetching the segment from archive, if WAL
archiving is in use.)
</P
><P
> This sets only the minimum number of segments retained in
<TT
CLASS="FILENAME"
>pg_xlog</TT
>; the system might need to retain more segments
for WAL archival or to recover from a checkpoint. If
<TT
CLASS="VARNAME"
>wal_keep_segments</TT
> is zero (the default), the system
doesn't keep any extra segments for standby purposes, and the number
of old WAL segments available to standby servers is a function of
the location of the previous checkpoint and status of WAL
archiving. This parameter has no effect on restartpoints.
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-VACUUM-DEFER-CLEANUP-AGE"
></A
><TT
CLASS="VARNAME"
>vacuum_defer_cleanup_age</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> Specifies the number of transactions by which <TT
CLASS="COMMAND"
>VACUUM</TT
> and
<ACRONYM
CLASS="ACRONYM"
>HOT</ACRONYM
> updates will defer cleanup of dead row versions. The
default is zero transactions, meaning that dead row versions can be
removed as soon as possible, that is, as soon as they are no longer
visible to any open transaction. You may wish to set this to a
non-zero value on a primary server that is supporting hot standby
servers, as described in <A
HREF="hot-standby.html"
>Section 25.5</A
>. This allows
more time for queries on the standby to complete without incurring
conflicts due to early cleanup of rows. However, since the value
is measured in terms of number of write transactions occurring on the
primary server, it is difficult to predict just how much additional
grace time will be made available to standby queries.
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-STANDBY"
>18.5.5. Standby Servers</A
></H2
><P
> These settings control the behavior of a standby server that is
to receive replication data.
</P
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><A
NAME="GUC-HOT-STANDBY"
></A
><TT
CLASS="VARNAME"
>hot_standby</TT
> (<TT
CLASS="TYPE"
>boolean</TT
>)</DT
><DD
><P
> Specifies whether or not you can connect and run queries during
recovery, as described in <A
HREF="hot-standby.html"
>Section 25.5</A
>.
The default value is <TT
CLASS="LITERAL"
>off</TT
>.
This parameter can only be set at server start. It only has effect
during archive recovery or in standby mode.
</P
></DD
><DT
><A
NAME="GUC-MAX-STANDBY-ARCHIVE-DELAY"
></A
><TT
CLASS="VARNAME"
>max_standby_archive_delay</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> When Hot Standby is active, this parameter determines how long the
standby server should wait before canceling standby queries that
conflict with about-to-be-applied WAL entries, as described in
<A
HREF="hot-standby.html#HOT-STANDBY-CONFLICT"
>Section 25.5.2</A
>.
<TT
CLASS="VARNAME"
>max_standby_archive_delay</TT
> applies when WAL data is
being read from WAL archive (and is therefore not current).
The default is 30 seconds. Units are milliseconds if not specified.
A value of -1 allows the standby to wait forever for conflicting
queries to complete.
This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
file or on the server command line.
</P
><P
> Note that <TT
CLASS="VARNAME"
>max_standby_archive_delay</TT
> is not the same as the
maximum length of time a query can run before cancellation; rather it
is the maximum total time allowed to apply any one WAL segment's data.
Thus, if one query has resulted in significant delay earlier in the
WAL segment, subsequent conflicting queries will have much less grace
time.
</P
></DD
><DT
><A
NAME="GUC-MAX-STANDBY-STREAMING-DELAY"
></A
><TT
CLASS="VARNAME"
>max_standby_streaming_delay</TT
> (<TT
CLASS="TYPE"
>integer</TT
>)</DT
><DD
><P
> When Hot Standby is active, this parameter determines how long the
standby server should wait before canceling standby queries that
conflict with about-to-be-applied WAL entries, as described in
<A
HREF="hot-standby.html#HOT-STANDBY-CONFLICT"
>Section 25.5.2</A
>.
<TT
CLASS="VARNAME"
>max_standby_streaming_delay</TT
> applies when WAL data is
being received via streaming replication.
The default is 30 seconds. Units are milliseconds if not specified.
A value of -1 allows the standby to wait forever for conflicting
queries to complete.
This parameter can only be set in the <TT
CLASS="FILENAME"
>postgresql.conf</TT
>
file or on the server command line.
</P
><P
> Note that <TT
CLASS="VARNAME"
>max_standby_streaming_delay</TT
> is not the same as
the maximum length of time a query can run before cancellation; rather
it is the maximum total time allowed to apply WAL data once it has
been received from the primary server. Thus, if one query has
resulted in significant delay, subsequent conflicting queries will
have much less grace time until the standby server has caught up
again.
</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
>
