8000 Add support for forcing a switch to a new xlog file; cause such a switch · postgrespro/postgres_cluster@704ddaa · GitHub
[go: up one dir, main page]
Skip to content

Commit 704ddaa

Browse files
tglsfdctglsfdc
committed
Add support for forcing a switch to a new xlog file; cause such a switch
to happen automatically during pg_stop_backup(). Add some functions for interrogating the current xlog insertion point and for easily extracting WAL filenames from the hex WAL locations displayed by pg_stop_backup and friends. Simon Riggs with some editorialization by Tom Lane.
1 parent c0dc166 commit 704ddaa

File tree

7 files changed

+516
-67
lines changed

7 files changed

+516
-67
lines changed

‎doc/src/sgml/backup.sgml

Lines changed: 14 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.81 2006/06/18 15:38:35 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.82 2006/08/06 03:53:43 tgl Exp $ -->
22

33
<chapter id="backup">
44
<title>Backup and Restore</title>
@@ -597,6 +597,15 @@ archive_command = 'test ! -f .../%f &amp;&amp; cp %p .../%f'
597597
failure, it will be called only once for any given file name.
598598
</para>
599599

600+
<para>
601+
Another way to limit your exposure to data loss is to call
602+
<function>pg_switch_xlog()</> periodically, such as once a minute.
603+
This function forces the current WAL segment file to be completed
604+
and made available to the archiving command. This approach does
605+
not work well for extremely short update intervals, however, since
606+
copying a new 16MB segment file every few seconds is expensive.
607+
</para>
608+
600609
<para>
601610
In writing your archive command, you should assume that the file names to
602611
be archived may be up to 64 characters long and may contain any
@@ -670,7 +679,9 @@ SELECT pg_stop_backup();
670679
<listitem>
671680
<para>
672681
Once the WAL segment files used during the backup are archived as part
673-
of normal database activity, you are done.
682+
of normal database activity, you are done. The file identified by
683+
<function>pg_stop_backup</>'s result is the last segment that needs
684+
to be archived to complete the backup.
674685
</para>
675686
</listitem>
676687
</orderedlist>
@@ -739,10 +750,7 @@ SELECT pg_stop_backup();
739750
segments with names numerically less are no longer needed to recover
740751
the file system backup and may be deleted. However, you should
741752
consider keeping several backup sets to be absolutely certain that
742-
you can recover your data. Keep in mind that only completed WAL
743-
segment files are archived, so there will be delay between running
744-
<function>pg_stop_backup</> and the archiving of all WAL segment
745-
files needed to make the file system backup consistent.
753+
you can recover your data.
746754
</para>
747755

748756
<para>

‎doc/src/sgml/func.sgml

Lines changed: 91 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.328 2006/07/28 18:33:03 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.329 2006/08/06 03:53:43 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -10142,14 +10142,26 @@ SELECT set_config('log_statement_stats', 'off', false);
1014210142
<indexterm zone="functions-admin">
1014310143
<primary>pg_stop_backup</primary>
1014410144
</indexterm>
10145+
<indexterm zone="functions-admin">
10146+
<primary>pg_switch_xlog</primary>
10147+
</indexterm>
10148+
<indexterm zone="functions-admin">
10149+
<primary>pg_current_xlog_location</primary>
10150+
</indexterm>
10151+
<indexterm zone="functions-admin">
10152+
<primary>pg_xlogfile_name_offset</primary>
10153+
</indexterm>
10154+
<indexterm zone="functions-admin">
10155+
<primary>pg_xlogfile_name</primary>
10156+
</indexterm>
1014510157
<indexterm zone="functions-admin">
1014610158
<primary>backup</primary>
1014710159
</indexterm>
1014810160

1014910161
<para>
1015010162
The functions shown in <xref
1015110163
linkend="functions-admin-backup-table"> assist in making on-line backups.
10152-
Use of these functions is restricted to superusers.
10164+
Use of the first three functions is restricted to superusers.
1015310165
</para>
1015410166

1015510167
<table id="functions-admin-backup-table">
@@ -10175,6 +10187,34 @@ SELECT set_config('log_statement_stats', 'off', false);
1017510187
<entry><type>text</type></entry>
1017610188
<entry>Finish performing on-line backup</entry>
1017710189
</row>
10190+
<row>
10191+
<entry>
10192+
<literal><function>pg_switch_xlog</function>()</literal>
10193+
</entry>
10194+
<entry><type>text</type></entry>
10195+
<entry>Force switch to a new xlog file</entry>
10196+
</row>
10197+
<row>
10198+
<entry>
10199+
<literal><function>pg_current_xlog_location</function>()</literal>
10200+
</entry>
10201+
<entry><type>text</type></entry>
10202+
<entry>Get current xlog location</entry>
10203+
</row>
10204+
<row>
10205+
<entry>
10206+
<literal><function>pg_xlogfile_name_offset</function>(<parameter>location</> <type>text</>)</literal>
10207+
</entry>
10208+
<entry><type>text</type></entry>
10209+
<entry>Convert xlog location string to filename and decimal byte offset within file</entry>
10210+
</row>
10211+
<row>
10212+
<entry>
10213+
<literal><function>pg_xlogfile_name</function>(<parameter>location</> <type>text</>)</literal>
10214+
</entry>
10215+
<entry><type>text</type></entry>
10216+
<entry>Convert xlog location string to filename</entry>
10217+
</row>
1017810218
</tbody>
1017910219
</tgroup>
1018010220
</table>
@@ -10184,19 +10224,62 @@ SELECT set_config('log_statement_stats', 'off', false);
1018410224
arbitrary user-defined label for the backup. (Typically this would be
1018510225
the name under which the backup dump file will be stored.) The function
1018610226
writes a backup label file into the database cluster's data directory,
10187-
and then returns the backup's starting WAL offset as text. (The user
10227+
and then returns the backup's starting xlog location as text. The user
1018810228
need not pay any attention to this result value, but it is provided in
10189-
case it is of use.)
10229+
case it is of use.
10230+
<programlisting>
10231+
postgres=# select pg_start_backup('label_goes_here');
10232+
pg_start_backup
10233+
-----------------
10234+
0/D4445B8
10235+
(1 row)
10236+
</programlisting>
1019010237
</para>
1019110238

1019210239
<para>
1019310240
<function>pg_stop_backup</> removes the label file created by
1019410241
<function>pg_start_backup</>, and instead creates a backup history file in
10195-
the WAL archive area. The history file includes the label given to
10196-
<function>pg_start_backup</>, the starting and ending WAL offsets for
10242+
the xlog archive area. The history file includes the label given to
10243+
<function>pg_start_backup</>, the starting and ending xlog locations for
1019710244
the backup, and the starting and ending times of the backup. The return
10198-
value is the backup's ending WAL offset (which again may be of little
10199-
interest).
10245+
value is the backup's ending xlog location (which again may be of little
10246+
interest). After noting the ending location, the current xlog insertion
10247+
point is automatically advanced to the next xlog file, so that the
10248+
ending xlog file can be archived immediately to complete the backup.
10249+
</para>
10250+
10251+
<para>
10252+
<function>pg_switch_xlog</> moves to the next xlog file, allowing the
10253+
current file to be archived (assuming you are using continuous archiving).
10254+
The result is the ending xlog location within the just-completed xlog file.
10255+
If there has been no xlog activity since the last xlog switch,
10256+
<function>pg_switch_xlog</> does nothing and returns the end location
10257+
of the previous xlog file.
10258+
</para>
10259+
10260+
<para>
10261+
<function>pg_current_xlog_location</> displays the current xlog insertion
10262+
point in the same format used by the above functions. This is a
10263+
read-only operation and does not require superuser permissions.
10264+
</para>
10265+
10266+
<para>
10267+
You can use <function>pg_xlogfile_name_offset</> to extract the
10268+
corresponding xlog filename and byte offset from the results of any of the
10269+
above functions. For example:
10270+
<programlisting>
10271+
postgres=# select pg_xlogfile_name_offset(pg_stop_backup());
10272+
pg_xlogfile_name_offset
10273+
----------------------------------
10274+
00000001000000000000000D 4039624
10275+
(1 row)
10276+
</programlisting>
10277+
Similarly, <function>pg_xlogfile_name</> extracts just the xlog filename.
10278+
When the given xlog location is exactly at an xlog file boundary, both
10279+
these functions return the name of the preceding xlog file.
10280+
This is usually the desired behavior for managing xlog archiving
10281+
behavior, since the preceding file is the last one that currently
10282+
needs to be archived.
1020010283
</para>
1020110284

1020210285
<para>

0 commit comments

Comments
 (0)
0