pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPITR Doc clarifications
5+ messages / 3 participants
[nested] [flat]
* PITR Doc clarifications
@ 2007-11-28 13:08 Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Simon Riggs @ 2007-11-28 13:08 UTC (permalink / raw)
To: pgsql-docs
A few docs changes, as mentioned.
Comments?
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PITR Doc clarifications
@ 2007-11-28 13:12 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2007-11-28 13:12 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-docs
Simon Riggs wrote:
>
> A few docs changes, as mentioned.
>
> Comments?
You forgot the patch :-)
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PITR Doc clarifications
@ 2007-11-28 13:39 Simon Riggs <simon@2ndquadrant.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 5+ messages in thread
From: Simon Riggs @ 2007-11-28 13:39 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-docs
On Wed, 2007-11-28 at 10:12 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> >
> > A few docs changes, as mentioned.
> >
> > Comments?
>
> You forgot the patch :-)
No, just a very short patch. :-)
This patch is slightly longer...
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
Attachments:
pitr_docs.v1.patch (text/x-patch, 7.4K) download
Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.107
diff -c -r2.107 backup.sgml
*** doc/src/sgml/backup.sgml 16 Oct 2007 19:44:18 -0000 2.107
--- doc/src/sgml/backup.sgml 28 Nov 2007 12:58:27 -0000
***************
*** 641,650 ****
<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">
--- 641,660 ----
<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 as soon as possible. Other utility
functions related to WAL management are listed in <xref
linkend="functions-admin-backup-table">.
</para>
+
+ <para>
+ When <varname>archive_mode</> is off certain SQL operations are optimized
+ to avoid writing WAL, as described in <xref linkend="populate-pitr">. If
+ archiving were enabled during execution of one of these statements then it
+ would be possible to write data that would not then be part of any
+ concurrent backup because the WAL for those changes had been optimized away.
+ So it is possible to alter <varname>archive_command</> with a reload, but
+ <varname>archive_mode</> cannot be changed except at server start.
+ </para>
</sect2>
<sect2 id="backup-base-backup">
***************
*** 973,986 ****
<para>
Normally, recovery will proceed through all available WAL segments,
thereby restoring the database to the current point in time (or as
! close as we can get given the available WAL segments). But if you want
! to recover to some previous point in time (say, right before the junior
! DBA dropped your main transaction table), just specify the required
! stopping point in <filename>recovery.conf</>. You can specify the stop
! point, known as the <quote>recovery target</>, either by date/time or
! by completion of a specific transaction ID. As of this writing only
! the date/time option is very usable, since there are no tools to help
! you identify with any accuracy which transaction ID to use.
</para>
<note>
--- 983,1006 ----
<para>
Normally, recovery will proceed through all available WAL segments,
thereby restoring the database to the current point in time (or as
! close as we can get given the available WAL segments). So a normal
! recovery will end with a "file not found" message, the exact text
! of the error message depending upon your choice of
! <varname>restore_command</>. You may also see an error message
! at the start of recovery for a file named something like
! <filename>00000001.history</>. This is also normal and does not
! indicate a problem in simple recovery situations. See
! <xref linkend="backup-timelines"> for discussion.
! </para>
!
! <para>
! If you want to recover to some previous point in time (say, right before
! the junior DBA dropped your main transaction table), just specify the
! required stopping point in <filename>recovery.conf</>. You can specify
! the stop point, known as the <quote>recovery target</>, either by
! date/time or by completion of a specific transaction ID. As of this
! writing only the date/time option is very usable, since there are no tools
! to help you identify with any accuracy which transaction ID to use.
</para>
<note>
***************
*** 1214,1219 ****
--- 1234,1324 ----
</para>
</sect2>
+ <sect2 id="backup-tips">
+ <title>Tips and Examples</title>
+
+ <para>
+ Some examples of configuring Continuous Archiving are given here.
+ </para>
+
+ <sect3 id="backup-standalone">
+ <title>Recovery Settings</title>
+
+ <para>
+ It is possible to use the existing backup facilities to produce
+ standalone hot backups. These are backups that cannot be used for
+ Point in Time Recovery, yet are much faster to backup and restore
+ than <application>pg_dump</>.
+ </para>
+
+ <para>
+ To configure standalone backups you should use a switch file. If the
+ file exists then archives are made, otherwise archiving is ignored.
+ <programlisting>
+ archive_command = 'test -f /var/lib/pgsql/backup_in_progress && cp -i %p /var/lib/pgsql/archive/%f </dev/null'
+ </programlisting>
+ Backup can then be taken using a script like the following:
+ <programlisting>
+ touch /var/lib/pgsql/backup_in_progress
+ psql -c "select pg_start_backup('hot_backup');"
+ tar -cvf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
+ psql -c "select pg_stop_backup();"
+ sleep 20
+ rm /var/lib/pgsql/backup_in_progress
+ tar -rvf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
+ </programlisting>
+ The switch file /var/lib/pgsql/backup_in_progress is created
+ first, allowing archiving to start prior to the backup. After the
+ backup the switch file is removed. Archived WAL files are then added
+ into the archive so that both Base Backup and all required WAL files
+ are part of the same tar file.
+ </para>
+ </sect3>
+
+ <sect3 id="backup-scripts">
+ <title><varname>archive_command</varname> scripts</title>
+
+ <para>
+ Many people choose to use scripts to define their
+ <varname>archive_command</varname>, so that their
+ <filename>postgresql.conf</> looks very simple:
+ <programlisting>
+ archive_command = 'local_backup_script.sh'
+ </programlisting>
+ This allows all complexity to be managed within the script, which could
+ be written in a popular scripting language such as bash or perl etc..
+ Statements echoed to stderr will appear in the database server log,
+ allowing complex configurations to be easily diagnosed if they should fail.
+ </para>
+ <para>
+ Example of how scripts might be used include
+ <itemizedlist>
+ <listitem>
+ <para>
+ Copying data to a secure off-site data storage provider.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Batching WAL files so they are transferred every 3 hours, rather than
+ one at a time as they fill.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Interfaces with other Backup and Recovery software.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Interfaces with monitoring software to report errors directly.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect3>
+ </sect2>
+
<sect2 id="continuous-archiving-caveats">
<title>Caveats</title>
***************
*** 1435,1441 ****
Pseudocode for a suitable <varname>restore_command</> is:
<programlisting>
triggered = false;
! while (!NextWALFileReady() && !triggered)
{
sleep(100000L); /* wait for ~0.1 sec */
if (CheckForExternalTrigger())
--- 1540,1546 ----
Pseudocode for a suitable <varname>restore_command</> is:
<programlisting>
triggered = false;
! while (!NextWALFileReady() && !triggered)
{
sleep(100000L); /* wait for ~0.1 sec */
if (CheckForExternalTrigger())
* Re: PITR Doc clarifications
@ 2007-11-28 22:36 Bruce Momjian <bruce@momjian.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2007-11-28 22:36 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-docs
Simon Riggs wrote:
> On Wed, 2007-11-28 at 10:12 -0300, Alvaro Herrera wrote:
> > Simon Riggs wrote:
> > >
> > > A few docs changes, as mentioned.
> > >
> > > Comments?
> >
> > You forgot the patch :-)
>
> No, just a very short patch. :-)
>
> This patch is slightly longer...
Slightly modified patch attached and applied. Thanks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
/rtmp/diff (text/x-diff, 7.6K) download
Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.108
diff -c -c -r2.108 backup.sgml
*** doc/src/sgml/backup.sgml 28 Nov 2007 15:42:30 -0000 2.108
--- doc/src/sgml/backup.sgml 28 Nov 2007 22:34:06 -0000
***************
*** 641,650 ****
<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">
--- 641,662 ----
<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 as soon as possible. Other utility
functions related to WAL management are listed in <xref
linkend="functions-admin-backup-table">.
</para>
+
+ <para>
+ When <varname>archive_mode</> is <literal>off</> some SQL commands
+ are optimized to avoid WAL logging, as described in <xref
+ linkend="populate-pitr">. If archiving were turned on during execution
+ of one of these statements, WAL would not contain enough information
+ for archive recovery. (Crash recovery is unaffected.) For
+ this reason, <varname>archive_mode</> can only be changed at server
+ start. (<varname>archive_command</> can be changed with a
+ configuration file reload, and setting it to <literal>''</> does
+ prevent archiving.)
+ </para>
</sect2>
<sect2 id="backup-base-backup">
***************
*** 973,986 ****
<para>
Normally, recovery will proceed through all available WAL segments,
thereby restoring the database to the current point in time (or as
! close as we can get given the available WAL segments). But if you want
! to recover to some previous point in time (say, right before the junior
! DBA dropped your main transaction table), just specify the required
! stopping point in <filename>recovery.conf</>. You can specify the stop
! point, known as the <quote>recovery target</>, either by date/time or
! by completion of a specific transaction ID. As of this writing only
! the date/time option is very usable, since there are no tools to help
! you identify with any accuracy which transaction ID to use.
</para>
<note>
--- 985,1008 ----
<para>
Normally, recovery will proceed through all available WAL segments,
thereby restoring the database to the current point in time (or as
! close as we can get given the available WAL segments). So a normal
! recovery will end with a "file not found" message, the exact text
! of the error message depending upon your choice of
! <varname>restore_command</>. You may also see an error message
! at the start of recovery for a file named something like
! <filename>00000001.history</>. This is also normal and does not
! indicate a problem in simple recovery situations. See
! <xref linkend="backup-timelines"> for discussion.
! </para>
!
! <para>
! If you want to recover to some previous point in time (say, right before
! the junior DBA dropped your main transaction table), just specify the
! required stopping point in <filename>recovery.conf</>. You can specify
! the stop point, known as the <quote>recovery target</>, either by
! date/time or by completion of a specific transaction ID. As of this
! writing only the date/time option is very usable, since there are no tools
! to help you identify with any accuracy which transaction ID to use.
</para>
<note>
***************
*** 1214,1219 ****
--- 1236,1327 ----
</para>
</sect2>
+ <sect2 id="backup-tips">
+ <title>Tips and Examples</title>
+
+ <para>
+ Some examples of configuring Continuous Archiving are given here.
+ </para>
+
+ <sect3 id="backup-standalone">
+ <title>Recovery Settings</title>
+
+ <para>
+ It is possible to use the existing backup facilities to produce
+ standalone hot backups. These are backups that cannot be used for
+ point-in-time recovery, yet are much faster to backup and restore
+ than <application>pg_dump</>.
+ </para>
+
+ <para>
+ To configure standalone backups you should use a switch file. If the
+ file exists then archives are made, otherwise archiving is ignored.
+ <programlisting>
+ archive_command = 'test -f /var/lib/pgsql/backup_in_progress && cp -i %p /var/lib/pgsql/archive/%f </dev/null'
+ </programlisting>
+ Backup can then be taken using a script like the following:
+ <programlisting>
+ touch /var/lib/pgsql/backup_in_progress
+ psql -c "select pg_start_backup('hot_backup');"
+ tar -cvf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
+ psql -c "select pg_stop_backup();"
+ sleep 20
+ rm /var/lib/pgsql/backup_in_progress
+ tar -rvf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
+ </programlisting>
+ The switch file <filename>/var/lib/pgsql/backup_in_progress</> is
+ created first, allowing archiving to start prior to the backup.
+ After the backup the switch file is removed. Archived WAL files are
+ then added to the backup so that both base backup and all required
+ WAL files are part of the same <application>tar</> file.
+ </para>
+ </sect3>
+
+ <sect3 id="backup-scripts">
+ <title><varname>archive_command</varname> scripts</title>
+
+ <para>
+ Many people choose to use scripts to define their
+ <varname>archive_command</varname>, so that their
+ <filename>postgresql.conf</> looks very simple:
+ <programlisting>
+ archive_command = 'local_backup_script.sh'
+ </programlisting>
+ This allows all complexity to be managed within the script, which
+ can be written in a popular scripting language such as
+ <application>bash</> or <application>perl</>. Statements echoed to
+ <literal>stderr</> will appear in the database server log, allowing
+ complex configurations to be easily diagnosed if they fail.
+ </para>
+ <para>
+ Example of how scripts might be used include:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Copying data to a secure off-site data storage provider
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Batching WAL files so they are transferred every three hours, rather than
+ one at a time as they fill
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Interfacing with other backup and recovery software
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Interfacing with monitoring software to report errors directly
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect3>
+ </sect2>
+
<sect2 id="continuous-archiving-caveats">
<title>Caveats</title>
***************
*** 1435,1441 ****
Pseudocode for a suitable <varname>restore_command</> is:
<programlisting>
triggered = false;
! while (!NextWALFileReady() && !triggered)
{
sleep(100000L); /* wait for ~0.1 sec */
if (CheckForExternalTrigger())
--- 1543,1549 ----
Pseudocode for a suitable <varname>restore_command</> is:
<programlisting>
triggered = false;
! while (!NextWALFileReady() && !triggered)
{
sleep(100000L); /* wait for ~0.1 sec */
if (CheckForExternalTrigger())
* Re: PITR Doc clarifications
@ 2007-11-29 19:11 Simon Riggs <simon@2ndquadrant.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 5+ messages in thread
From: Simon Riggs @ 2007-11-29 19:11 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-docs
On Wed, 2007-11-28 at 17:36 -0500, Bruce Momjian wrote:
> Slightly modified patch attached and applied. Thanks.
Nice additions, thanks.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-11-29 19:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-28 13:08 PITR Doc clarifications Simon Riggs <simon@2ndquadrant.com>
2007-11-28 13:12 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2007-11-28 13:39 ` Simon Riggs <simon@2ndquadrant.com>
2007-11-28 22:36 ` Bruce Momjian <bruce@momjian.us>
2007-11-29 19:11 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox