Re: Backups from the standby (Incrementally Updated Backups), open item - Mailing list pgsql-hackers

Bruce Momjian wrote:
> Josh Berkus wrote:
> > All,
> >
> > I've been looking at the open item which belongs with this doc:
> >
> > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
> >
> > ... and my viewpoint is that the procedure described should be *cut*
> > from the official docs and put on the Wiki, with warnings.  It's simply
> > far too hackish and dependant on DBA understanding of PostgreSQL
> > internals to belong in the main docs.
> >
> > In 9.1 we'll probably have some machinery to make taking snapshots of
> > the standby more reliable (like running pg_start_backup on the master),
> > and then that procedure will belong in the main docs.
>
> FYI, I am working on a doc update that will use pg_start/stop backup on
> the master, as suggested by Fujii Masao:
>
>     http://archives.postgresql.org/pgsql-bugs/2010-08/msg00237.php

I have created a doc patch (attached) to document a clean way to take a
backup of the standby. This can be used for pitr, and to create
additional slaves, so I do think this will be used more during 9.0.  You
can see the output here:

    http://momjian.us/tmp/pgsql/backup-incremental-updated.html

The actual list of steps only appeared in 9.0 so I suggest this only be
applied to HEAD and 9.0.X.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
Index: doc/src/sgml/high-availability.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/high-availability.sgml,v
retrieving revision 1.80
diff -c -c -r1.80 high-availability.sgml
*** doc/src/sgml/high-availability.sgml    24 Aug 2010 15:22:12 -0000    1.80
--- doc/src/sgml/high-availability.sgml    24 Aug 2010 19:05:06 -0000
***************
*** 1913,1921 ****
     <para>
      If we take a file system backup of the standby server's data
      directory while it is processing
!     logs shipped from the primary, we will be able to reload that backup and
!     restart the standby's recovery process from the last restart point.
!     We no longer need to keep WAL files from before the standby's restart point.
      If recovery is needed, it will be faster to recover from the incrementally
      updated backup than from the original base backup.
     </para>
--- 1913,1922 ----
     <para>
      If we take a file system backup of the standby server's data
      directory while it is processing
!     logs shipped from the primary, we will be able to use that new
!     backup to create a new standby or for <link
!     linkend="backup-pitr-recovery">continuous archive recovery</>.
!     We then no longer need to keep WAL files from before the new backup.
      If recovery is needed, it will be faster to recover from the incrementally
      updated backup than from the original base backup.
     </para>
***************
*** 1926,1984 ****
     <orderedlist>
      <listitem>
       <para>
!       Perform the backup, without using <function>pg_start_backup</> and
!       <function>pg_stop_backup</>. Note that the <filename>pg_control</>
!       file must be backed up <emphasis>first</>, as in:
! <programlisting>
! cp /var/lib/pgsql/data/global/pg_control /tmp
! cp -r /var/lib/pgsql/data /path/to/backup
! mv /tmp/pg_control /path/to/backup/data/global
! </programlisting>
!       <filename>pg_control</> contains the location where WAL replay will
!       begin after restoring from the backup; backing it up first ensures
!       that it points to the last restartpoint when the backup started, not
!       some later restartpoint that happened while files were copied to the
!       backup.
       </para>
      </listitem>
      <listitem>
       <para>
!       Make note of the backup ending WAL location by calling the <function>
!       pg_last_xlog_replay_location</> function at the end of the backup,
!       and keep it with the backup.
  <programlisting>
! psql -c "select pg_last_xlog_replay_location();" > /path/to/backup/end_location
  </programlisting>
-       When recovering from the incrementally updated backup, the server
-       can begin accepting connections and complete the recovery successfully
-       before the database has become consistent. To avoid that, you must
-       ensure the database is consistent before users try to connect to the
-       server and when the recovery ends. You can do that by comparing the
-       progress of the recovery with the stored backup ending WAL location:
-       the server is not consistent until recovery has reached the backup end
-       location. The progress of the recovery can also be observed with the
-       <function>pg_last_xlog_replay_location</> function, though that requires
-       connecting to the server while it might not be consistent yet, so
-       care should be taken with that method.
       </para>
       <para>
       </para>
      </listitem>
     </orderedlist>
     </para>
-
-    <para>
-     Since the standby server is not <quote>live</>, it is not possible to
-     use <function>pg_start_backup()</> and <function>pg_stop_backup()</>
-     to manage the backup process; it will be up to you to determine how
-     far back you need to keep WAL segment files to have a recoverable
-     backup. That is determined by the last restartpoint when the backup
-     was taken, any WAL older than that can be deleted from the archive
-     once the backup is complete. You can determine the last restartpoint
-     by running <application>pg_controldata</> on the standby server before
-     taking the backup, or by using the <varname>log_checkpoints</> option
-     to print values to the standby's server log.
-    </para>
    </sect1>

  </chapter>
--- 1927,1975 ----
     <orderedlist>
      <listitem>
       <para>
!       Perform <function>pg_start_backup()</> on the master, noting the WAL
!       filename and offset reported.
       </para>
      </listitem>
      <listitem>
       <para>
!       Copy the <filename>backup_label</> file from the primary's data directory to
!       a temporary location:
  <programlisting>
! cp /path/to/master/backup_label /tmp
  </programlisting>
       </para>
+     </listitem>
+     <listitem>
       <para>
+      Run <application>pg_controldata</> on the standby and wait for
+      <quote>Latest checkpoint's REDO location</> to equal or exceed
+      the WAL information reported by <function>pg_stop_backup()</>.
+      You might need to call <function>pg_switch_xlog()</> to force
+      the required WAL file to be sent to the standby.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Perform a file system backup of the standby's data directory.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Perform <function>pg_stop_backup()</> on the master.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Move the saved <filename>backup_label</> file to the new backup
+       directory:
+ <programlisting>
+ mv /tmp/backup_label /path/to/new_backup
+ </programlisting>
       </para>
      </listitem>
     </orderedlist>
     </para>
    </sect1>

  </chapter>