Skip site navigation (1) Skip section navigation (2)

Re: Backup of live database

From: Tom Arthurs <tarthurs(at)jobflash(dot)com>
To: Brian Modra <epailty(at)googlemail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Backup of live database
Date: 2008-01-16 17:42:44
Message-ID: 478E4214.6030805@jobflash.com (view raw or flat)
Thread:
Lists: 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(at)commandprompt(dot)com 
> <mailto:jd(at)commandprompt(dot)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(at)commandprompt(dot)com
>     <mailto:jd(at)commandprompt(dot)com>
>      > <mailto: jd(at)commandprompt(dot)com <mailto:jd(at)commandprompt(dot)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

In response to

Responses

pgsql-admin by date

Next:From: Stephen FrostDate: 2008-01-16 17:50:41
Subject: Re: postgresql in FreeBSD jails: proposal
Previous:From: Tom LaneDate: 2008-01-16 17:33:32
Subject: Re: postgresql in FreeBSD jails: proposal

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group