Re: snapshot backup with WAL files

On Fri, Jul 29, 2011 at 11:53 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Isabella Ghiurea <isabella(dot)ghiurea(at)nrc-cnrc(dot)gc(dot)ca> wrote:
>
>> I would like to configure  our Pg db for OS  file system snapshot
>> backup with Pg  WAL files, need to know if would be enough in pg
>> db to call :
>> |*SELECT* *pg_start_backup*('pitr_backup');
>> and next take the snapshot  and after
>> pg_stop the backup and close the WAL files . During this time  the
>> full pg db read/writes will continue.
>> Are any loss of data we should may be aware during this backup
>> procedure?
>> env :RHES-64 bits with LVM directories, PG 9.0, the db size >150GB
>
> I'm not clear what you mean by "snapshot".  If you're talking about
> something which takes an *atomic* snapshot of the entire PostgreSQL
> data space, including WAL files, then just taking the snapshot is
> enough without any other actions.  Assuming that you are using safe
> settings for fsync, full_page_writes, and synchronous_commit, it
> will recover all successfully committed transactions and roll back
> all others.
>
> If you're talking about tar, cpio, or some other software which is
> not atomic, you should follow the instructions in the documentation
> for one of the supported techniques completely and carefully:  For
> PITR-style backups, that means ensuring that you have a working flow
> of WAL files being captured *before* you run pg_start_backup().
>
> http://www.postgresql.org/docs/9.0/interactive/backup.html
>

Just to chime in, if it's the latter you are trying to do, you can use
the omnipitr scripts to manage this, see
https://github.com/omniti-labs/omnipitr/ for details.

Robert Treat
play: xzilla.net
work: omniti.com