pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Wall <d.wall@computer.org>
To: Brian Modra <epailty@googlemail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Backup of live database
Date: Wed, 16 Jan 2008 09:28:42 -0800
Message-ID: <478E3ECA.2010806@computer.org> (raw)
In-Reply-To: <5a9699850801160024y7abdcd2fjdb1035c2070797eb@mail.gmail.com>
References: <5a9699850801152257g6ee9513i6c4fc88a5f20a785@mail.gmail.com>
	<478DAC1D.3010909@commandprompt.com>
	<5a9699850801152310g39fa9bf3y3926892145ab3dcf@mail.gmail.com>
	<478DBAC3.8050802@commandprompt.com>
	<5a9699850801160024y7abdcd2fjdb1035c2070797eb@mail.gmail.com>


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?
Not sure if the answers you got answered your question or not.  Here's 
my take:

1) If the database is not running, tar works fine.

2) If the database is running, you can ONLY use tar if you also use WAL 
archiving since the database will not only need the tar files, which 
will be inconsistent, but also the WAL files (in your $PGDATA/pg_xlog) 
in order to recover from those inconsistencies.  I find this is best if 
you are creating a warm standby that is keeping a backup database in 
sync with a primary.

3) If the database is running, use pg_dump to create a consistent backup.

4) No matter what, as previously mentioned, you should test your backup 
procedures to ensure you can reliably restore.

Good luck,
David



view thread (18+ messages)  latest in thread

Message-ID: <478E3ECA.2010806@computer.org>
Permalink:  ../478E3ECA.2010806@computer.org/
Also on:    postgresql.org/message-id/478E3ECA.2010806@computer.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: d.wall@computer.org, epailty@googlemail.com
  Subject: Re: Backup of live database
  In-Reply-To: <478E3ECA.2010806@computer.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox