Re: Backup of live database - Mailing list pgsql-admin

Hi, Brian

We have been doing PITR backups since the feature first became available
in postgresql.  We first used tar, then, due to the dreadful warning
being emitted by tar (which made us doubt that it was actually archiving
that particular file) we decided to try CPIO, which actually emits much
the same warnings, though not as verbose, so I think that tar will work
as well (we never bothered going back to tar, mostly through laziness,
so I can personally say that it works.)  Actually I have reason to
believe you can use any series of OS commands that create copies or
archives of the files, as long as those commands don't exit prematurely
on warnings.

The important thing is to start archiving the WAL files *prior* to the
first OS backup, or you will end up with an unusable data base.

We have actually tested and used recovered data bases with this scheme.
  We use WAL archiving to replicate a warm standby data base which we
have failed over to (and failed back from) many times, and I've had to
do an actual PITR recovery to to recover several tables that got
accidentally deleted by bad procedures/code/brain burned DBA's :)

Brian Modra wrote:
> Sorry to be hammering this point, but I want to be totally sure its OK,
> rather than 5 months down the line attempt to recover, and it fails...
>
> Are you absolutely certain that the tar backup of the file that changed,
> is OK? (And that even if that file is huge, tar has managed to save the
> file as it was before it was changed - otherwise I'm afraid that the
> first part of the file is saved to tar, and then the file is modified,
> and the last part of the file is saved to tar from the point it was
> modified - and so therefore not consistent with the first part... And
> therefore the file has lost its integrity, so even a WAL restore won't
> help because the base files themselves are corrupt in the tar file?
>
> On 16/01/2008, *Joshua D. Drake* <jd@commandprompt.com
> <mailto:jd@commandprompt.com>> wrote:
>
>     Brian Modra wrote:
>      > The documentation about WAL says that you can start a live backup, as
>      > long as you use WAL backup also.
>      > I'm concerned about the integrity of the tar file. Can someone
>     help me
>      > with that?
>
>     If you are using point in time recovery:
>
>     http://www.postgresql.org/docs/8.2/static/continuous-archiving.html
>
>     You do not have to worry about it.
>
>     Joshua D. Drake
>
>
>
>
>
>
>      >
>      > On 16/01/2008, *Joshua D. Drake* <jd@commandprompt.com
>     <mailto:jd@commandprompt.com>
>      > <mailto: jd@commandprompt.com <mailto:jd@commandprompt.com>>> wrote:
>      >
>      >     Brian Modra wrote:
>      >      > Hi,
>      >      > If tar reports that a file was modified while it was being
>     archived,
>      >      > does that mean that the file was archived correctly, or is it
>      >     corrupted
>      >      > in the archive?
>      >      > Does tar take a snapshot of the file so that even if it is
>      >     modified, at
>      >      > least the archive is safe?
>      >
>      >     You can not use tar to backup postgresql if it is running.
>      >
>      >     http://www.postgresql.org/docs/8.2/static/backup.html
>     <http://www.postgresql.org/docs/8.2/static/backup.html>
>      >     <http://www.postgresql.org/docs/8.2/static/backup.html>
>      >
>      >     Sincerely,
>      >
>      >     Joshua D. Drake
>      >
>      >      > Thanks
>      >      >
>      >      > --
>      >      > Brian Modra   Land line: +27 23 5411 462
>      >      > Mobile: +27 79 183 8059
>      >      > 6 Jan Louw Str, Prince Albert, 6930
>      >      > Postal: P.O. Box 2, Prince Albert 6930
>      >      > South Africa
>      >
>      >
>      >
>      >
>      > --
>      > Brian Modra   Land line: +27 23 5411 462
>      > Mobile: +27 79 183 8059
>      > 6 Jan Louw Str, Prince Albert, 6930
>      > Postal: P.O. Box 2, Prince Albert 6930
>      > South Africa
>
>
>
>
> --
> Brian Modra   Land line: +27 23 5411 462
> Mobile: +27 79 183 8059
> 6 Jan Louw Str, Prince Albert, 6930
> Postal: P.O. Box 2, Prince Albert 6930
> South Africa