Re: Un successful Restoration of DATA using WAL files

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."