Thread: snapshot backup with WAL files
Hello Gurus,
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
Thank you
Isabella
|
--
-----------------------------------------------------------
Isabella A. Ghiurea
Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre |http://www.nrc-cnrc.gc.ca/eng/services/hia/data-centre.html
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045
Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.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
-Kevin
On Fri, Jul 29, 2011 at 11:53 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.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
On 07/29/2011 11:35 AM, Isabella Ghiurea 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 .
This procedure works fine. But note that doing this does not eliminate
the need to save log files using the archive_command. You will end up
with a small number of them, which is an advantage compared to
non-snapshot backup techniques where hundreds of archive logs might
occur during the backup time. But even with using a snapshot backup,
the small number that are referenced in the backup log are still
necessary for the backup to be useful.
As always, your database backup should not be considered useful until
you've done a successful restore using it. That would have discovered
this problem during testing.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us