Thread: UFS2 Snapshots and Postgres

UFS2 Snapshots and Postgres

From
Eduardo Morras
Date:
Hi everyone,

I'm using FreeBSD 9 for Postgres and want to know if these actions
are safe for make a backup of the database:

a) call pg_start_backup('b1')
b) take an UFS2 snapshot of data files
c) call pg_stop_backup()
d) change to the snapshot dir and rsync/dd/dump/transfer it to backup
file server

Is it safe to call pg_start_backup('b1',true)?

Thanks in advance

Re: UFS2 Snapshots and Postgres

From
Karl Denninger
Date:


On 6/25/2012 7:35 AM, Eduardo Morras wrote:

Hi everyone,

I'm using FreeBSD 9 for Postgres and want to know if these actions are safe for make a backup of the database:

a) call pg_start_backup('b1')
b) take an UFS2 snapshot of data files
c) call pg_stop_backup()
d) change to the snapshot dir and rsync/dd/dump/transfer it to backup file server

Is it safe to call pg_start_backup('b1',true)?

Thanks in advance

Snapshots are "safe" (but will result in a roll-forward on restart) IF AND ONLY IF the log data and database table spaces are all on the same snapshotted volume.

IF THEY ARE NOT then it will probably work 95% of the time, and the other 5% it will be unrecoverable.  Be very, very careful -- the snapshot must in fact snapshot ALL of the involved database volumes (log data included!) at the same instant.

--
Karl Denninger
karl@denninger.net
The Market Ticker


Re: UFS2 Snapshots and Postgres

From
Eduardo Morras
Date:
At 15:16 25/06/2012, you wrote:


>On 6/25/2012 7:35 AM, Eduardo Morras wrote:
>>
>>Hi everyone,
>>
>>I'm using FreeBSD 9 for Postgres and want to know if these actions
>>are safe for make a backup of the database:
>>
>>a) call pg_start_backup('b1')
>>b) take an UFS2 snapshot of data files
>>c) call pg_stop_backup()
>>d) change to the snapshot dir and rsync/dd/dump/transfer it to
>>backup file server
>>
>>Is it safe to call pg_start_backup('b1',true)?
>>
>>Thanks in advance
>Snapshots are "safe" (but will result in a roll-forward on restart)
>IF AND ONLY IF the log data and database table spaces are all on the
>same snapshotted volume.
>
>IF THEY ARE NOT then it will probably work 95% of the time, and the
>other 5% it will be unrecoverable.  Be very, very careful -- the
>snapshot must in fact snapshot ALL of the involved database volumes
>(log data included!) at the same instant.

Even if i do a pg_start_backup()? I thought it set db data/ files in
a consistent state and puts in wal files the new transactions and
apply them when call pg_stop_backup().

I must do it other way then :(

Thanks

Re: UFS2 Snapshots and Postgres

From
Magnus Hagander
Date:
On Mon, Jun 25, 2012 at 4:34 PM, Eduardo Morras <nec556@retena.com> wrote:
> At 15:16 25/06/2012, you wrote:
>
>
>> On 6/25/2012 7:35 AM, Eduardo Morras wrote:
>>>
>>>
>>> Hi everyone,
>>>
>>> I'm using FreeBSD 9 for Postgres and want to know if these actions are
>>> safe for make a backup of the database:
>>>
>>> a) call pg_start_backup('b1')
>>> b) take an UFS2 snapshot of data files
>>> c) call pg_stop_backup()
>>> d) change to the snapshot dir and rsync/dd/dump/transfer it to backup
>>> file server
>>>
>>> Is it safe to call pg_start_backup('b1',true)?
>>>
>>> Thanks in advance
>>
>> Snapshots are "safe" (but will result in a roll-forward on restart) IF AND
>> ONLY IF the log data and database table spaces are all on the same
>> snapshotted volume.
>>
>> IF THEY ARE NOT then it will probably work 95% of the time, and the other
>> 5% it will be unrecoverable.  Be very, very careful -- the snapshot must in
>> fact snapshot ALL of the involved database volumes (log data included!) at
>> the same instant.
>
>
> Even if i do a pg_start_backup()? I thought it set db data/ files in a
> consistent state and puts in wal files the new transactions and apply them
> when call pg_stop_backup().
>
> I must do it other way then :(

No, if you use pg_start_backup() and pg_stpo_backup() (and verify all
their return codes...), *and* you use log archiving using
archive_command, then snapshots are safe even if they are on different
filesystems.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: UFS2 Snapshots and Postgres

From
Vibhor Kumar
Date:
On Jun 25, 2012, at 10:34 AM, Eduardo Morras wrote:

>>> Thanks in advance
>> Snapshots are "safe" (but will result in a roll-forward on restart) IF AND ONLY IF the log data and database table
spacesare all on the same snapshotted volume. 
>>
>> IF THEY ARE NOT then it will probably work 95% of the time, and the other 5% it will be unrecoverable.  Be very,
verycareful -- the snapshot must in fact snapshot ALL of the involved database volumes (log data included!) at the same
instant.
>
> Even if i do a pg_start_backup()? I thought it set db data/ files in a consistent state and puts in wal files the new
transactionsand apply them when call pg_stop_backup(). 
>
> I must do it other way then :(
>
> Thanks

If you are doing pg_start_backup(), taking snapshot and pg_stop_backup with archived WAL file backups. Then its safe.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Blog: http://vibhork.blogspot.com


Re: UFS2 Snapshots and Postgres

From
Eduardo Morras
Date:
At 15:16 25/06/2012, you wrote:


>On 6/25/2012 7:35 AM, Eduardo Morras wrote:
>>
>>Hi everyone,
>>
>>I'm using FreeBSD 9 for Postgres and want to know if these actions
>>are safe for make a backup of the database:
>>
>>a) call pg_start_backup('b1')
>>b) take an UFS2 snapshot of data files
>>c) call pg_stop_backup()
>>d) change to the snapshot dir and rsync/dd/dump/transfer it to
>>backup file server
>>
>>Is it safe to call pg_start_backup('b1',true)?
>>
>>Thanks in advance
>Snapshots are "safe" (but will result in a roll-forward on restart)
>IF AND ONLY IF the log data and database table spaces are all on the
>same snapshotted volume.
>
>IF THEY ARE NOT then it will probably work 95% of the time, and the
>other 5% it will be unrecoverable.  Be very, very careful -- the
>snapshot must in fact snapshot ALL of the involved database volumes
>(log data included!) at the same instant.

Even if i do a pg_start_backup()? I thought it set db data/ files in
a consistent state and puts in wal files the new transactions and
apply them when call pg_stop_backup().

I must do it other way then :(

Thanks