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

Re: Backup of live database

From: David Wall <d(dot)wall(at)computer(dot)org>
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:28:42
Message-ID: 478E3ECA.2010806@computer.org (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: Tom LaneDate: 2008-01-16 17:33:32
Subject: Re: postgresql in FreeBSD jails: proposal
Previous:From: Mischa SandbergDate: 2008-01-16 17:13:23
Subject: Re: postgresql in FreeBSD jails: proposal

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