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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Bruce Momjian <bruce@xxxxxxxxxx> 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>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
