<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.81 2006/06/18 15:38:35 petere Exp $ -->

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

<chapter id="backup">

<title>Backup and Restore</title>

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

<listitem>

<para>

Once the WAL segment files used during the backup are archived as part

of normal database activity, you are done.

of normal database activity, you are done. The file identified by

<function>pg_stop_backup</>'s result is the last segment that needs

to be archived to complete the backup.

</para>

</listitem>

</orderedlist>

segments with names numerically less are no longer needed to recover

the file system backup and may be deleted. However, you should

consider keeping several backup sets to be absolutely certain that

you can recover your data. Keep in mind that only completed WAL

segment files are archived, so there will be delay between running

<function>pg_stop_backup</> and the archiving of all WAL segment

files needed to make the file system backup consistent.

you can recover your data.

</para>

<para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.328 2006/07/28 18:33:03 tgl Exp $ -->

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

<chapter id="functions">

<title>Functions and Operators</title>

<indexterm zone="functions-admin">

<primary>pg_stop_backup</primary>

</indexterm>

<indexterm zone="functions-admin">

<primary>pg_switch_xlog</primary>

</indexterm>

<indexterm zone="functions-admin">

<primary>pg_current_xlog_location</primary>

</indexterm>

<indexterm zone="functions-admin">

<primary>pg_xlogfile_name_offset</primary>

</indexterm>

<indexterm zone="functions-admin">

<primary>pg_xlogfile_name</primary>

</indexterm>

<indexterm zone="functions-admin">

<primary>backup</primary>

</indexterm>

<para>

The functions shown in <xref

linkend="functions-admin-backup-table"> assist in making on-line backups.

Use ofthese functions is restricted to superusers.

Use ofthe first three functions is restricted to superusers.

</para>

<table id="functions-admin-backup-table">

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

<entry>Finish performing on-line backup</entry>

</row>

<row>

<entry>

<literal><function>pg_switch_xlog</function>()</literal>

</entry>

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

<entry>Force switch to a new xlog file</entry>

</row>

<row>

<entry>

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

</entry>

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

<entry>Get current xlog location</entry>

</row>

<row>

<entry>

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

</entry>

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

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

</row>

<row>

<entry>

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

</entry>

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

<entry>Convert xlog location string to filename</entry>

</row>

</tbody>

</tgroup>

</table>

arbitrary user-defined label for the backup. (Typically this would be

the name under which the backup dump file will be stored.) The function

writes a backup label file into the database cluster's data directory,

and then returns the backup's startingWAL offset as text.(The user

and then returns the backup's startingxlog location as text. The user

need not pay any attention to this result value, but it is provided in

case it is of use.)

case it is of use.

<programlisting>

postgres=# select pg_start_backup('label_goes_here');

pg_start_backup

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

0/D4445B8

(1 row)

</programlisting>

</para>

<para>

<function>pg_stop_backup</> removes the label file created by

<function>pg_start_backup</>, and instead creates a backup history file in

theWAL archive area. The history file includes the label given to

<function>pg_start_backup</>, the starting and endingWAL offsets for

thexlog archive area. The history file includes the label given to

<function>pg_start_backup</>, the starting and endingxlog locations for

the backup, and the starting and ending times of the backup. The return

value is the backup's ending WAL offset (which again may be of little

interest).

value is the backup's ending xlog location (which again may be of little

interest). After noting the ending location, the current xlog insertion

point is automatically advanced to the next xlog file, so that the

ending xlog file can be archived immediately to complete the backup.

</para>

<para>

<function>pg_switch_xlog</> moves to the next xlog file, allowing the

current file to be archived (assuming you are using continuous archiving).

The result is the ending xlog location within the just-completed xlog file.

If there has been no xlog activity since the last xlog switch,

<function>pg_switch_xlog</> does nothing and returns the end location

of the previous xlog file.

</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.

</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());

pg_xlogfile_name_offset

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

00000001000000000000000D 4039624

(1 row)

</programlisting>

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

When the given xlog location is exactly at an xlog file boundary, both

these functions return the name of the preceding xlog file.

This is usually the desired behavior for managing xlog archiving

behavior, since the preceding file is the last one that currently

needs to be archived.

</para>

<para>