Re: Un successful Restoration of DATA using WAL files

Based on your suggestions, I have applied the attached patch to mention
_not_ to use pg_dump or pg_dumpall in two places, and to briefly explain
why.  Thanks.

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

Craig Ringer wrote:
> On Mon, 2009-10-19 at 07:18 -0700, Mitesh51 wrote:
> > I am unable to restore data with the use of WAL files by following procedure.
> >
> > I have done following changes in postgres.conf to enable WAL archiving...
> >
> > archive_mode = on        # allows archiving to be done
> > archive_command = 'copy "%p" "C:\\archivedir\\%f"'
> >
> > I have one database(built in) postgres. I create one table student in it.
> > and take full backup(only of a single database & I am not copying complete
> > data dir..) @ 12:40 pm with the
> >
> > pg_dump.exe -h localhost -p 5432 -U postgres -f C:\test.sql postgres
>
> Any comments from readers on the following suggestion of changes to the
> PITR docs at:
>
> http://www.postgresql.org/docs/current/interactive/continuous-archiving.html
>
>
>         User misunderstandings on the pgsql-general mailing list suggest
>         that a clear and prominent warning needs to be added to this
>         page to prevent people from trying to combine a pg_dump base
>         backup with WAL archiving. People are failing to understand that
>         the base backup must be a file-system-level copy taken after
>         calling pg_start_backup() .
>
>         Suggested changes:
>
>         "maintains a write ahead log" -> "maintains a block-level write
>         ahead log" in the first paragraph.
>
>         "we can combine a file-system-level backup" ->
>         "we can combine a file-system-level backup of the data directory
>         (<b>not</b> a pg_dump backup)"
>
>         Also, somewhere in the introductory section, something like this
>         would be good:
>
>         "IMPORTANT: WAL archiving and PITR cannot be used with an
>         SQL-level base backup taken with pg_dump. See "Making a Base
>         Backup" below for the correct method of backing up your database
>         for WAL archiving and PITR. See "Caveats" for details."
>
>         In "Caveats":
>
>         "You can't use pg_dump to make a base backup, restore that to a
>         different cluster or a new database in the original cluster,
>         then apply archived WAL files to it. WAL archiving records a
>         history of changes to the database cluster at a very low level,
>         rather than recording anything like SQL commands. The WAL files
>         essentially record sequences of writes of byte sequences to
>         offsets within files in the cluster. A new cluster (or a new
>         database created in the original cluster by CREATE DATABASE)
>         will have a different layout in its files, so the WAL archives
>         don't make any sense to apply to the new cluster."
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.143
diff -c -c -r2.143 backup.sgml
*** doc/src/sgml/backup.sgml    22 Feb 2010 11:47:30 -0000    2.143
--- doc/src/sgml/backup.sgml    22 Feb 2010 17:14:02 -0000
***************
*** 484,489 ****
--- 484,499 ----
    </itemizedlist>
    </para>

+   <note>
+    <para>
+     <application>pg_dump</application> and
+     <application>pg_dumpall</application> do not produce file-system-level
+     backups and cannot be used as part of a continuous-archiving solution.
+     Such dumps are <emphasis>logical</> and do not contain enough
+     information to used by WAL reply.
+    </para>
+   </note>
+
    <para>
     As with the plain file-system-backup technique, this method can only
     support restoration of an entire database cluster, not a subset.
***************
*** 744,750 ****
     <listitem>
      <para>
       Perform the backup, using any convenient file-system-backup tool
!      such as <application>tar</> or <application>cpio</>.  It is neither
       necessary nor desirable to stop normal operation of the database
       while you do this.
      </para>
--- 754,762 ----
     <listitem>
      <para>
       Perform the backup, using any convenient file-system-backup tool
!      such as <application>tar</> or <application>cpio</> (not
!      <application>pg_dump</application> or
!      <application>pg_dumpall</application>).  It is neither
       necessary nor desirable to stop normal operation of the database
       while you do this.
      </para>