Skip to content

Commit e8ea9e9

Browse files
tglsfdctglsfdc
committed
Implement archive_timeout feature to force xlog file switches to occur no more
than N seconds apart. This allows a simple, if not very high performance, means of guaranteeing that a PITR archive is no more than N seconds behind real time. Also make pg_current_xlog_location return the WAL Write pointer, add pg_current_xlog_insert_location to return the Insert pointer, and fix pg_xlogfile_name_offset to return its results as a two-element record instead of a smashed-together string, as per recent discussion. Simon Riggs
1 parent bb764e9 commit e8ea9e9

File tree

11 files changed

+278
-73
lines changed

11 files changed

+278
-73
lines changed

‎doc/src/sgml/backup.sgml

Lines changed: 25 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.82 2006/08/06 03:53:43 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.83 2006/08/17 23:04:02 tgl Exp $ -->
22

33
<chapter id="backup">
44
<title>Backup and Restore</title>
@@ -492,7 +492,7 @@ tar -cf backup.tar /usr/local/pgsql/data
492492
the archiving will be done. Instead, <productname>PostgreSQL</> lets
493493
the administrator specify a shell command to be executed to copy a
494494
completed segment file to wherever it needs to go. The command could be
495-
as simple as a <application>cp</>, or it could invoke a complex shell
495+
as simple as a <literal>cp</>, or it could invoke a complex shell
496496
script &mdash; it's all up to you.
497497
</para>
498498

@@ -576,36 +576,6 @@ archive_command = 'test ! -f .../%f &amp;&amp; cp %p .../%f'
576576
it is working as you intend.
577577
</para>
578578

579-
<para>
580-
If you are concerned about being able to recover right up to the
581-
current instant, you may want to take additional steps to ensure that
582-
the current, partially-filled WAL segment is also copied someplace.
583-
This is particularly important if your server generates only little WAL
584-
traffic (or has slack periods where it does so), since it could take a
585-
long time before a WAL segment file is completely filled and ready to
586-
archive. One possible way to handle this is to set up a
587-
<application>cron</> job that periodically (once a minute, perhaps)
588-
identifies the current WAL segment file and saves it someplace safe.
589-
Then the combination of the archived WAL segments and the saved current
590-
segment will be enough to ensure you can always restore to within a
591-
minute of current time. This behavior is not presently built into
592-
<productname>PostgreSQL</> because we did not want to complicate the
593-
definition of the <xref linkend="guc-archive-command"> by requiring it
594-
to keep track of successively archived, but different, copies of the
595-
same WAL file. The <xref linkend="guc-archive-command"> is only
596-
invoked on completed WAL segments. Except in the case of retrying a
597-
failure, it will be called only once for any given file name.
598-
</para>
599-
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-
609579
<para>
610580
In writing your archive command, you should assume that the file names to
611581
be archived may be up to 64 characters long and may contain any
@@ -626,6 +596,29 @@ archive_command = 'test ! -f .../%f &amp;&amp; cp %p .../%f'
626596
<xref linkend="runtime-config-file-locations"> for how to relocate the
627597
configuration files.
628598
</para>
599+
600+
<para>
601+
The archive command is only invoked on completed WAL segments. Hence,
602+
if your server generates only little WAL traffic (or has slack periods
603+
where it does so), there could be a long delay between the completion
604+
of a transaction and its safe recording in archive storage. To put
605+
a limit on how old unarchived data can be, you can set
606+
<xref linkend="guc-archive-timeout"> to force the server to switch
607+
to a new WAL segment file at least that often. Note that archived
608+
files that are ended early due to a forced switch are still the same
609+
length as completely full files. It is therefore unwise to set a very
610+
short <varname>archive_timeout</> &mdash; it will bloat your archive
611+
storage. <varname>archive_timeout</> settings of a minute or so are
612+
usually reasonable.
613+
</para>
614+
615+
<para>
616+
Also, you can force a segment switch manually with
617+
<function>pg_switch_xlog()</>,
618+
if you want to ensure that a just-finished transaction is archived
619+
immediately. Other utility functions related to WAL management are
620+
listed in <xref linkend="functions-admin-backup-table">.
621+
</para>
629622
</sect2>
630623

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

‎doc/src/sgml/config.sgml

Lines changed: 30 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.74 2006/08/15 18:26:58 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.75 2006/08/17 23:04:03 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -1584,6 +1584,35 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
15841584
</listitem>
15851585
</varlistentry>
15861586

1587+
<varlistentry id="guc-archive-timeout" xreflabel="archive_timeout">
1588+
<term><varname>archive_timeout</varname> (<type>integer</type>)</term>
1589+
<indexterm>
1590+
<primary><varname>archive_timeout</> configuration parameter</primary>
1591+
</indexterm>
1592+
<listitem>
1593+
<para>
1594+
The <xref linkend="guc-archive-command"> is only invoked on completed
1595+
WAL segments. Hence,
1596+
if your server generates only little WAL traffic (or has slack periods
1597+
where it does so), there could be a long delay between the completion
1598+
of a transaction and its safe recording in archive storage. To put
1599+
a limit on how old unarchived data can be, you can set
1600+
<varname>archive_timeout</> to force the server to switch
1601+
to a new WAL segment file periodically. When this parameter is
1602+
greater than zero, the server will switch to a new segment file
1603+
whenever this many seconds elapse since the last segment file switch.
1604+
Note that archived
1605+
files that are ended early due to a forced switch are still the same
1606+
length as completely full files. It is therefore unwise to set a very
1607+
short <varname>archive_timeout</> &mdash; it will bloat your archive
1608+
storage. <varname>archive_timeout</> settings of a minute or so are
1609+
usually reasonable.
1610+
This parameter can only be set in the <filename>postgresql.conf</>
1611+
file or on the server command line.
1612+
</para>
1613+
</listitem>
1614+
</varlistentry>
1615+
15871616
</variablelist>
15881617
</sect2>
15891618
</sect1>

‎doc/src/sgml/func.sgml

Lines changed: 28 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.329 2006/08/06 03:53:43 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.330 2006/08/17 23:04:03 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -10148,6 +10148,9 @@ SELECT set_config('log_statement_stats', 'off', false);
1014810148
<indexterm zone="functions-admin">
1014910149
<primary>pg_current_xlog_location</primary>
1015010150
</indexterm>
10151+
<indexterm zone="functions-admin">
10152+
<primary>pg_current_xlog_insert_location</primary>
10153+
</indexterm>
1015110154
<indexterm zone="functions-admin">
1015210155
<primary>pg_xlogfile_name_offset</primary>
1015310156
</indexterm>
@@ -10199,13 +10202,20 @@ SELECT set_config('log_statement_stats', 'off', false);
1019910202
<literal><function>pg_current_xlog_location</function>()</literal>
1020010203
</entry>
1020110204
<entry><type>text</type></entry>
10202-
<entry>Get current xlog location</entry>
10205+
<entry>Get current xlog write location</entry>
1020310206
</row>
1020410207
<row>
1020510208
<entry>
10206-
<literal><function>pg_xlogfile_name_offset</function>(<parameter>location</> <type>text</>)</literal>
10209+
<literal><function>pg_current_xlog_insert_location</function>()</literal>
1020710210
</entry>
1020810211
<entry><type>text</type></entry>
10212+
<entry>Get current xlog insert location</entry>
10213+
</row>
10214+
<row>
10215+
<entry>
10216+
<literal><function>pg_xlogfile_name_offset</function>(<parameter>location</> <type>text</>)</literal>
10217+
</entry>
10218+
<entry><type>text</>, <type>integer</></entry>
1020910219
<entry>Convert xlog location string to filename and decimal byte offset within file</entry>
1021010220
</row>
1021110221
<row>
@@ -10258,20 +10268,28 @@ postgres=# select pg_start_backup('label_goes_here');
1025810268
</para>
1025910269

1026010270
<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.
10271+
<function>pg_current_xlog_location</> displays the current xlog write
10272+
location in the same format used by the above functions. Similarly
10273+
<function>pg_current_xlog_insert_location</> displays the current xlog
10274+
insertion point. The insertion point is the <quote>logical</> end of xlog
10275+
at any instant, while the write location is the end of what has actually
10276+
been written out from the server's internal buffers. The write location
10277+
is the end of what can be examined from outside the server, and is usually
10278+
what you want if you are interested in archiving partially-complete xlog
10279+
files. The insertion point is made available primarily for server
10280+
debugging purposes. These are both read-only operations and do not
10281+
require superuser permissions.
1026410282
</para>
1026510283

1026610284
<para>
1026710285
You can use <function>pg_xlogfile_name_offset</> to extract the
1026810286
corresponding xlog filename and byte offset from the results of any of the
1026910287
above functions. For example:
1027010288
<programlisting>
10271-
postgres=# select pg_xlogfile_name_offset(pg_stop_backup());
10272-
pg_xlogfile_name_offset
10273-
----------------------------------
10274-
00000001000000000000000D 4039624
10289+
postgres=# select * from pg_xlogfile_name_offset(pg_stop_backup());
10290+
file_name | file_offset
10291+
--------------------------+-------------
10292+
00000001000000000000000D | 4039624
1027510293
(1 row)
1027610294
</programlisting>
1027710295
Similarly, <function>pg_xlogfile_name</> extracts just the xlog filename.

0 commit comments

Comments
 (0)