Search Postgresql Archives

Re: Un successful Restoration of DATA using WAL files

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux