<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.82 2006/08/06 03:53:43 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.83 2006/08/17 23:04:02 tgl Exp $ -->

<chapter id="backup">

<title>Backup and Restore</title>

the archiving will be done. Instead, <productname>PostgreSQL</> lets

the administrator specify a shell command to be executed to copy a

completed segment file to wherever it needs to go. The command could be

as simple as a <application>cp</>, or it could invoke a complex shell

as simple as a <literal>cp</>, or it could invoke a complex shell

script &mdash; it's all up to you.

</para>

it is working as you intend.

</para>

<para>

If you are concerned about being able to recover right up to the

current instant, you may want to take additional steps to ensure that

the current, partially-filled WAL segment is also copied someplace.

This is particularly important if your server generates only little WAL

traffic (or has slack periods where it does so), since it could take a

long time before a WAL segment file is completely filled and ready to

archive. One possible way to handle this is to set up a

<application>cron</> job that periodically (once a minute, perhaps)

identifies the current WAL segment file and saves it someplace safe.

Then the combination of the archived WAL segments and the saved current

segment will be enough to ensure you can always restore to within a

minute of current time. This behavior is not presently built into

<productname>PostgreSQL</> because we did not want to complicate the

definition of the <xref linkend="guc-archive-command"> by requiring it

to keep track of successively archived, but different, copies of the

same WAL file. The <xref linkend="guc-archive-command"> is only

invoked on completed WAL segments. Except in the case of retrying a

failure, it will be called only once for any given file name.

</para>

<para>

Another way to limit your exposure to data loss is to call

<function>pg_switch_xlog()</> periodically, such as once a minute.

This function forces the current WAL segment file to be completed

and made available to the archiving command. This approach does

not work well for extremely short update intervals, however, since

copying a new 16MB segment file every few seconds is expensive.

</para>

<para>

In writing your archive command, you should assume that the file names to

be archived may be up to 64 characters long and may contain any

<xref linkend="runtime-config-file-locations"> for how to relocate the

configuration files.

</para>

<para>

The archive command is only invoked on completed WAL segments. Hence,

if your server generates only 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

<xref linkend="guc-archive-timeout"> to force the server to switch

to a new WAL segment file at least that often. Note that archived

files that are ended early due to a forced switch are still the same

length as completely full files. It is therefore unwise to set a very

short <varname>archive_timeout</> &mdash; it will bloat your archive

storage. <varname>archive_timeout</> settings of a minute or so are

usually reasonable.

</para>

<para>

Also, you can force a segment switch manually with

<function>pg_switch_xlog()</>,

if you want to ensure that a just-finished transaction is archived

immediately. Other utility functions related to WAL management are

listed in <xref linkend="functions-admin-backup-table">.

</para>

</sect2>

<sect2 id="backup-base-backup">

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.74 2006/08/15 18:26:58 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.75 2006/08/17 23:04:03 tgl Exp $ -->

<chapter Id="runtime-config">

<title>Server Configuration</title>

</listitem>

</varlistentry>

<varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">

<term><varname>archive_timeout</varname> (<type>integer</type>)</term>

<indexterm>

<primary><varname>archive_timeout</> configuration parameter</primary>

</indexterm>

<listitem>

<para>

The <xref linkend="guc-archive-command"> is only invoked on completed

WAL segments. Hence,

if your server generates only 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

<varname>archive_timeout</> 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 elapse since the last segment file switch.

Note that archived

files that are ended early due to a forced switch are still the same

length as completely full files. It is therefore unwise to set a very

short <varname>archive_timeout</> &mdash; it will bloat your archive

storage. <varname>archive_timeout</> settings of a minute or so are

usually reasonable.

This parameter can only be set in the <filename>postgresql.conf</>

file or on the server command line.

</para>

</listitem>

</varlistentry>

</variablelist>

</sect2>

</sect1>

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.329 2006/08/06 03:53:43 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.330 2006/08/17 23:04:03 tgl Exp $ -->

<chapter id="functions">

<title>Functions and Operators</title>

<indexterm zone="functions-admin">

<primary>pg_current_xlog_location</primary>

</indexterm>

<indexterm zone="functions-admin">

<primary>pg_current_xlog_insert_location</primary>

</indexterm>

<indexterm zone="functions-admin">

<primary>pg_xlogfile_name_offset</primary>

</indexterm>

<literal><function>pg_current_xlog_location</function>()</literal>

</entry>

<entry><type>text</type></entry>

<entry>Get current xlog location</entry>

<entry>Get current xlogwritelocation</entry>

</row>

<row>

<entry>

<literal><function>pg_xlogfile_name_offset</function>(<parameter>location</> <type>text</>)</literal>

<literal><function>pg_current_xlog_insert_location</function>()</literal>

</entry>

<entry><type>text</type></entry>

<entry>Get current xlog insert location</entry>

</row>

<row>

<entry>

<literal><function>pg_xlogfile_name_offset</function>(<parameter>location</> <type>text</>)</literal>

</entry>

<entry><type>text</>, <type>integer</></entry>

<entry>Convert xlog location string to filename and decimal byte offset within file</entry>

</row>

<row>

</para>

<para>

<function>pg_current_xlog_location</> displays the current xlog insertion

point in the same format used by the above functions. This is a

read-only operation and does not require superuser permissions.

<function>pg_current_xlog_location</> displays the current xlog write

location in the same format used by the above functions. Similarly

<function>pg_current_xlog_insert_location</> displays the current xlog

insertion point. The insertion point is the <quote>logical</> end of xlog

at any instant, while the write location is the end of what has actually

been written out from the server's internal buffers. The write location

is the end of what can be examined from outside the server, and is usually

what you want if you are interested in archiving partially-complete xlog

files. The insertion point is made available primarily for server

debugging purposes. These are both read-only operations and do not

require superuser permissions.

</para>

<para>

You can use <function>pg_xlogfile_name_offset</> to extract the

corresponding xlog filename and byte offset from the results of any of the

above functions. For example:

<programlisting>

postgres=# select pg_xlogfile_name_offset(pg_stop_backup());

----------------------------------

00000001000000000000000D 4039624

postgres=# select* frompg_xlogfile_name_offset(pg_stop_backup());

--------------------------+-------------

00000001000000000000000D|4039624

(1 row)

</programlisting>

Similarly, <function>pg_xlogfile_name</> extracts just the xlog filename.