<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Log-Shipping Standby Servers</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="High Availability, Load Balancing, and Replication"
HREF="high-availability.html"><LINK
REL="PREVIOUS"
TITLE="Comparison of different solutions"
HREF="different-replication-solutions.html"><LINK
REL="NEXT"
TITLE="Failover"
HREF="warm-standby-failover.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="Comparison of different solutions"
HREF="different-replication-solutions.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="high-availability.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 25. High Availability, Load Balancing, and Replication</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Failover"
HREF="warm-standby-failover.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="WARM-STANDBY"
>25.2. Log-Shipping Standby Servers</A
></H1
><P
> Continuous archiving can be used to create a <I
CLASS="FIRSTTERM"
>high
availability</I
> (HA) cluster configuration with one or more
<I
CLASS="FIRSTTERM"
>standby servers</I
> ready to take over operations if the
primary server fails. This capability is widely referred to as
<I
CLASS="FIRSTTERM"
>warm standby</I
> or <I
CLASS="FIRSTTERM"
>log shipping</I
>.
</P
><P
> The primary and standby server work together to provide this capability,
though the servers are only loosely coupled. The primary server operates
in continuous archiving mode, while each standby server operates in
continuous recovery mode, reading the WAL files from the primary. No
changes to the database tables are required to enable this capability,
so it offers low administration overhead compared to some other
replication solutions. This configuration also has relatively low
performance impact on the primary server.
</P
><P
> Directly moving WAL records from one database server to another
is typically described as log shipping. <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
implements file-based log shipping, which means that WAL records are
transferred one file (WAL segment) at a time. WAL files (16MB) can be
shipped easily and cheaply over any distance, whether it be to an
adjacent system, another system at the same site, or another system on
the far side of the globe. The bandwidth required for this technique
varies according to the transaction rate of the primary server.
Record-based log shipping is also possible with streaming replication
(see <A
HREF="warm-standby.html#STREAMING-REPLICATION"
>Section 25.2.5</A
>).
</P
><P
> It should be noted that the log shipping is asynchronous, i.e., the WAL
records are shipped after transaction commit. As a result, there is a
window for data loss should the primary server suffer a catastrophic
failure; transactions not yet shipped will be lost. The size of the
data loss window in file-based log shipping can be limited by use of the
<TT
CLASS="VARNAME"
>archive_timeout</TT
> parameter, which can be set as low
as a few seconds. However such a low setting will
substantially increase the bandwidth required for file shipping.
If you need a window of less than a minute or so, consider using
streaming replication (see <A
HREF="warm-standby.html#STREAMING-REPLICATION"
>Section 25.2.5</A
>).
</P
><P
> Recovery performance is sufficiently good that the standby will
typically be only moments away from full
availability once it has been activated. As a result, this is called
a warm standby configuration which offers high
availability. Restoring a server from an archived base backup and
rollforward will take considerably longer, so that technique only
offers a solution for disaster recovery, not high availability.
A standby server can also be used for read-only queries, in which case
it is called a Hot Standby server. See <A
HREF="hot-standby.html"
>Section 25.5</A
> for
more information.
</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="STANDBY-PLANNING"
>25.2.1. Planning</A
></H2
><P
> It is usually wise to create the primary and standby servers
so that they are as similar as possible, at least from the
perspective of the database server. In particular, the path names
associated with tablespaces will be passed across unmodified, so both
primary and standby servers must have the same mount paths for
tablespaces if that feature is used. Keep in mind that if
<A
HREF="sql-createtablespace.html"
>CREATE TABLESPACE</A
>
is executed on the primary, any new mount point needed for it must
be created on the primary and all standby servers before the command
is executed. Hardware need not be exactly the same, but experience shows
that maintaining two identical systems is easier than maintaining two
dissimilar ones over the lifetime of the application and system.
In any case the hardware architecture must be the same — shipping
from, say, a 32-bit to a 64-bit system will not work.
</P
><P
> In general, log shipping between servers running different major
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> release
levels is not possible. It is the policy of the PostgreSQL Global
Development Group not to make changes to disk formats during minor release
upgrades, so it is likely that running different minor release levels
on primary and standby servers will work successfully. However, no
formal support for that is offered and you are advised to keep primary
and standby servers at the same release level as much as possible.
When updating to a new minor release, the safest policy is to update
the standby servers first — a new minor release is more likely
to be able to read WAL files from a previous minor release than vice
versa.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="STANDBY-SERVER-OPERATION"
>25.2.2. Standby Server Operation</A
></H2
><P
> In standby mode, the server continuously applies WAL received from the
master server. The standby server can read WAL from a WAL archive
(see <A
HREF="archive-recovery-settings.html#RESTORE-COMMAND"
>restore_command</A
>) or directly from the master
over a TCP connection (streaming replication). The standby server will
also attempt to restore any WAL found in the standby cluster's
<TT
CLASS="FILENAME"
>pg_xlog</TT
> directory. That typically happens after a server
restart, when the standby replays again WAL that was streamed from the
master before the restart, but you can also manually copy files to
<TT
CLASS="FILENAME"
>pg_xlog</TT
> at any time to have them replayed.
</P
><P
> At startup, the standby begins by restoring all WAL available in the
archive location, calling <TT
CLASS="VARNAME"
>restore_command</TT
>. Once it
reaches the end of WAL available there and <TT
CLASS="VARNAME"
>restore_command</TT
>
fails, it tries to restore any WAL available in the <TT
CLASS="FILENAME"
>pg_xlog</TT
> directory.
If that fails, and streaming replication has been configured, the
standby tries to connect to the primary server and start streaming WAL
from the last valid record found in archive or <TT
CLASS="FILENAME"
>pg_xlog</TT
>. If that fails
or streaming replication is not configured, or if the connection is
later disconnected, the standby goes back to step 1 and tries to
restore the file from the archive again. This loop of retries from the
archive, <TT
CLASS="FILENAME"
>pg_xlog</TT
>, and via streaming replication goes on until the server
is stopped or failover is triggered by a trigger file.
</P
><P
> Standby mode is exited and the server switches to normal operation,
when a trigger file is found (<TT
CLASS="VARNAME"
>trigger_file</TT
>). Before failover,
any WAL immediately available in the archive or in <TT
CLASS="FILENAME"
>pg_xlog</TT
> will be
restored, but no attempt is made to connect to the master.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="PREPARING-MASTER-FOR-STANDBY"
>25.2.3. Preparing the Master for Standby Servers</A
></H2
><P
> Set up continuous archiving on the primary to an archive directory
accessible from the standby, as described
in <A
HREF="continuous-archiving.html"
>Section 24.3</A
>. The archive location should be
accessible from the standby even when the master is down, i.e. it should
reside on the standby server itself or another trusted server, not on
the master server.
</P
><P
> If you want to use streaming replication, set up authentication on the
primary server to allow replication connections from the standby
server(s); that is, provide a suitable entry or entries in
<TT
CLASS="FILENAME"
>pg_hba.conf</TT
> with the database field set to
<TT
CLASS="LITERAL"
>replication</TT
>. Also ensure <TT
CLASS="VARNAME"
>max_wal_senders</TT
> is set
to a sufficiently large value in the configuration file of the primary
server.
</P
><P
> Take a base backup as described in <A
HREF="continuous-archiving.html#BACKUP-BASE-BACKUP"
>Section 24.3.2</A
>
to bootstrap the standby server.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="STANDBY-SERVER-SETUP"
>25.2.4. Setting Up a Standby Server</A
></H2
><P
> To set up the standby server, restore the base backup taken from primary
server (see <A
HREF="continuous-archiving.html#BACKUP-PITR-RECOVERY"
>Section 24.3.3</A
>). Create a recovery
command file <TT
CLASS="FILENAME"
>recovery.conf</TT
> in the standby's cluster data
directory, and turn on <TT
CLASS="VARNAME"
>standby_mode</TT
>. Set
<TT
CLASS="VARNAME"
>restore_command</TT
> to a simple command to copy files from
the WAL archive.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> Do not use pg_standby or similar tools with the built-in standby mode
described here. <TT
CLASS="VARNAME"
>restore_command</TT
> should return immediately
if the file does not exist; the server will retry the command again if
necessary. See <A
HREF="log-shipping-alternative.html"
>Section 25.4</A
>
for using tools like pg_standby.
</P
></BLOCKQUOTE
></DIV
><P
> If you want to use streaming replication, fill in
<TT
CLASS="VARNAME"
>primary_conninfo</TT
> with a libpq connection string, including
the host name (or IP address) and any additional details needed to
connect to the primary server. If the primary needs a password for
authentication, the password needs to be specified in
<TT
CLASS="VARNAME"
>primary_conninfo</TT
> as well.
</P
><P
> If you're setting up the standby server for high availability purposes,
set up WAL archiving, connections and authentication like the primary
server, because the standby server will work as a primary server after
failover. You will also need to set <TT
CLASS="VARNAME"
>trigger_file</TT
> to make
it possible to fail over.
If you're setting up the standby server for reporting
purposes, with no plans to fail over to it, <TT
CLASS="VARNAME"
>trigger_file</TT
>
is not required.
</P
><P
> If you're using a WAL archive, its size can be minimized using the <A
HREF="archive-recovery-settings.html#ARCHIVE-CLEANUP-COMMAND"
>archive_cleanup_command</A
> parameter to remove files that are no
longer required by the standby server.
The <SPAN
CLASS="APPLICATION"
>pg_archivecleanup</SPAN
> utility is designed specifically to
be used with <TT
CLASS="VARNAME"
>archive_cleanup_command</TT
> in typical single-standby
configurations, see <A
HREF="pgarchivecleanup.html"
>Section F.22</A
>.
Note however, that if you're using the archive for backup purposes, you
need to retain files needed to recover from at least the latest base
backup, even if they're no longer needed by the standby.
</P
><P
> A simple example of a <TT
CLASS="FILENAME"
>recovery.conf</TT
> is:
</P><PRE
CLASS="PROGRAMLISTING"
>standby_mode = 'on'
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
restore_command = 'cp /path/to/archive/%f %p'
trigger_file = '/path/to/trigger_file'
archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'</PRE
><P>
</P
><P
> You can have any number of standby servers, but if you use streaming
replication, make sure you set <TT
CLASS="VARNAME"
>max_wal_senders</TT
> high enough in
the primary to allow them to be connected simultaneously.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="STREAMING-REPLICATION"
>25.2.5. Streaming Replication</A
></H2
><P
> Streaming replication allows a standby server to stay more up-to-date
than is possible with file-based log shipping. The standby connects
to the primary, which streams WAL records to the standby as they're
generated, without waiting for the WAL file to be filled.
</P
><P
> Streaming replication is asynchronous, so there is still a small delay
between committing a transaction in the primary and for the changes to
become visible in the standby. The delay is however much smaller than with
file-based log shipping, typically under one second assuming the standby
is powerful enough to keep up with the load. With streaming replication,
<TT
CLASS="VARNAME"
>archive_timeout</TT
> is not required to reduce the data loss
window.
</P
><P
> If you use streaming replication without file-based continuous
archiving, you have to set <TT
CLASS="VARNAME"
>wal_keep_segments</TT
> in the master
to a value high enough to ensure that old WAL segments are not recycled
too early, while the standby might still need them to catch up. If the
standby falls behind too much, it needs to be reinitialized from a new
base backup. If you set up a WAL archive that's accessible from the
standby, <TT
CLASS="VARNAME"
>wal_keep_segments</TT
> is not required as the standby can always
use the archive to catch up.
</P
><P
> To use streaming replication, set up a file-based log-shipping standby
server as described in <A
HREF="warm-standby.html"
>Section 25.2</A
>. The step that
turns a file-based log-shipping standby into streaming replication
standby is setting <TT
CLASS="VARNAME"
>primary_conninfo</TT
> setting in the
<TT
CLASS="FILENAME"
>recovery.conf</TT
> file to point to the primary server. Set
<A
HREF="runtime-config-connection.html#GUC-LISTEN-ADDRESSES"
>listen_addresses</A
> and authentication options
(see <TT
CLASS="FILENAME"
>pg_hba.conf</TT
>) on the primary so that the standby server
can connect to the <TT
CLASS="LITERAL"
>replication</TT
> pseudo-database on the primary
server (see <A
HREF="warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION"
>Section 25.2.5.1</A
>).
</P
><P
> On systems that support the keepalive socket option, setting
<A
HREF="runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE"
>tcp_keepalives_idle</A
>,
<A
HREF="runtime-config-connection.html#GUC-TCP-KEEPALIVES-INTERVAL"
>tcp_keepalives_interval</A
> and
<A
HREF="runtime-config-connection.html#GUC-TCP-KEEPALIVES-COUNT"
>tcp_keepalives_count</A
> helps the primary promptly
notice a broken connection.
</P
><P
> Set the maximum number of concurrent connections from the standby servers
(see <A
HREF="runtime-config-wal.html#GUC-MAX-WAL-SENDERS"
>max_wal_senders</A
> for details).
</P
><P
> When the standby is started and <TT
CLASS="VARNAME"
>primary_conninfo</TT
> is set
correctly, the standby will connect to the primary after replaying all
WAL files available in the archive. If the connection is established
successfully, you will see a walreceiver process in the standby, and
a corresponding walsender process in the primary.
</P
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="STREAMING-REPLICATION-AUTHENTICATION"
>25.2.5.1. Authentication</A
></H3
><P
> It is very important that the access privileges for replication be set up
so that only trusted users can read the WAL stream, because it is
easy to extract privileged information from it. Standby servers must
authenticate to the primary as a superuser account.
So a role with the <TT
CLASS="LITERAL"
>SUPERUSER</TT
> and <TT
CLASS="LITERAL"
>LOGIN</TT
>
privileges needs to be created on the primary.
</P
><P
> Client authentication for replication is controlled by a
<TT
CLASS="FILENAME"
>pg_hba.conf</TT
> record specifying <TT
CLASS="LITERAL"
>replication</TT
> in the
<TT
CLASS="REPLACEABLE"
><I
>database</I
></TT
> field. For example, if the standby is running on
host IP <TT
CLASS="LITERAL"
>192.168.1.100</TT
> and the superuser's name for replication
is <TT
CLASS="LITERAL"
>foo</TT
>, the administrator can add the following line to the
<TT
CLASS="FILENAME"
>pg_hba.conf</TT
> file on the primary:
</P><PRE
CLASS="PROGRAMLISTING"
># Allow the user "foo" from host 192.168.1.100 to connect to the primary
# as a replication standby if the user's password is correctly supplied.
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host replication foo 192.168.1.100/32 md5</PRE
><P>
</P
><P
> The host name and port number of the primary, connection user name,
and password are specified in the <TT
CLASS="FILENAME"
>recovery.conf</TT
> file.
The password can also be set in the <TT
CLASS="FILENAME"
>~/.pgpass</TT
> file on the
standby (specify <TT
CLASS="LITERAL"
>replication</TT
> in the <TT
CLASS="REPLACEABLE"
><I
>database</I
></TT
>
field).
For example, if the primary is running on host IP <TT
CLASS="LITERAL"
>192.168.1.50</TT
>,
port <TT
CLASS="LITERAL"
>5432</TT
>, the superuser's name for replication is
<TT
CLASS="LITERAL"
>foo</TT
>, and the password is <TT
CLASS="LITERAL"
>foopass</TT
>, the administrator
can add the following line to the <TT
CLASS="FILENAME"
>recovery.conf</TT
> file on the
standby:
</P><PRE
CLASS="PROGRAMLISTING"
># The standby connects to the primary that is running on host 192.168.1.50
# and port 5432 as the user "foo" whose password is "foopass".
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'</PRE
><P>
</P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="STREAMING-REPLICATION-MONITORING"
>25.2.5.2. Monitoring</A
></H3
><P
> An important health indicator of streaming replication is the amount
of WAL records generated in the primary, but not yet applied in the
standby. You can calculate this lag by comparing the current WAL write
location on the primary with the last WAL location received by the
standby. They can be retrieved using
<CODE
CLASS="FUNCTION"
>pg_current_xlog_location</CODE
> on the primary and the
<CODE
CLASS="FUNCTION"
>pg_last_xlog_receive_location</CODE
> on the standby,
respectively (see <A
HREF="functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE"
>Table 9-56</A
> and
<A
HREF="functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE"
>Table 9-57</A
> for details).
The last WAL receive location in the standby is also displayed in the
process status of the WAL receiver process, displayed using the
<TT
CLASS="COMMAND"
>ps</TT
> command (see <A
HREF="monitoring-ps.html"
>Section 27.1</A
> for details).
</P
></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="different-replication-solutions.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="warm-standby-failover.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Comparison of different solutions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="high-availability.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Failover</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
