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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce(at)momjian(dot)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. +