From: Simon Riggs <simon@2ndquadrant.com>
To: Toru SHIMOGAKI <shimogaki.toru@oss.ntt.co.jp>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dan Gorman <dgorman@hi5.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PITR Backups
Date: Fri, 22 Jun 2007 11:55:47 +0100
Message-ID: <1182509748.9276.178.camel@silverbirch.site> (raw)
In-Reply-To: <467B3459.9030808@oss.ntt.co.jp>
References: <7E0807EE-B970-4869-A248-B838E42AD9EE@hi5.com>
<9761.1182472005@sss.pgh.pa.us>
<467B3459.9030808@oss.ntt.co.jp>
On Fri, 2007-06-22 at 11:30 +0900, Toru SHIMOGAKI wrote:
> Tom Lane wrote:
> > Dan Gorman <dgorman@hi5.com> writes:
> >> All of our databases are on NetApp storage and I have been looking
> >> at SnapMirror (PITR RO copy ) and FlexClone (near instant RW volume
> >> replica) for backing up our databases. The problem is because there
> >> is no write-suspend or even a 'hot backup mode' for postgres it's
> >> very plausible that the database has data in RAM that hasn't been
> >> written and will corrupt the data.
>
> > Alternatively, you can use a PITR base backup as suggested here:
> > http://www.postgresql.org/docs/8.2/static/continuous-archiving.html
>
> I think Dan's problem is important if we use PostgreSQL to a large size database:
>
> - When we take a PITR base backup with hardware level snapshot operation
> (not filesystem level) which a lot of storage vender provide, the backup data
> can be corrupted as Dan said. During recovery we can't even read it,
> especially if meta-data was corrupted.
>
> - If we don't use hardware level snapshot operation, it takes long time to take
> a large backup data, and a lot of full-page-written WAL files are made.
>
> So, I think users need a new feature not to write out heap pages during taking a
> backup.
Your worries are unwarranted, IMHO. It appears Dan was taking a snapshot
without having read the procedure as clearly outlined in the manual.
pg_start_backup() flushes all currently dirty blocks to disk as part of
a checkpoint. If you snapshot after that point, then you will have all
the data blocks required from which to correctly roll forward. On its
own, the snapshot is an inconsistent backup and will give errors as Dan
shows. It is only when the snapshot is used as the base backup in a full
continuous recovery that the inconsistencies are removed and the
database is fully and correctly restored.
pg_start_backup() is the direct analogue of Oracle's ALTER DATABASE
BEGIN BACKUP. Snapshots work with Oracle too, in much the same way.
After reviewing the manual, if you honestly think there is a problem,
please let me know and I'll work with you to investigate.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
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-performance@postgresql.org
Cc: simon@2ndquadrant.com, shimogaki.toru@oss.ntt.co.jp, tgl@sss.pgh.pa.us, dgorman@hi5.com
Subject: Re: PITR Backups
In-Reply-To: <1182509748.9276.178.camel@silverbirch.site>
* 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