Thread: virtualized snapshots and PITR

Hi all;


we have a client using veeam to do vmware snapshots of the db servers, 
unfortunately as far as I can tell any restore of a veeam snapshot also 
restores the state, which means the db gets restored in an online state. 
As far as I know, once the db is online we cannot force it back into 
recovery mode to do a PITR recovery, correct?

Anyone know of a way to do PITR recoveries with veeam?


Thanks in advance

PropAAS DBA wrote:
> we have a client using veeam to do vmware snapshots of the db servers, 
> unfortunately as far as I can tell any restore of a veeam snapshot also 
> restores the state, which means the db gets restored in an online state. 
> As far as I know, once the db is online we cannot force it back into 
> recovery mode to do a PITR recovery, correct?
> 
> Anyone know of a way to do PITR recoveries with veeam?

It is fairly simple.

Before the snapshot, run

   SELECT pg_start_backup('label of your choice', TRUE);

After the snapshot, run

   SELECT pg_stop_backup();

Yours,
Laurenz Albe

As I was not aware of this command I searched for it and found this interesting blog.

https://blog.2ndquadrant.com/what-does-pg_start_backup-do/

Olivier

-----Message d'origine-----
De : Laurenz Albe [mailto:laurenz.albe@cybertec.at]
Envoyé : jeudi 14 décembre 2017 09:19
À : PropAAS DBA; pgsql-admin
Objet : Re: virtualized snapshots and PITR

PropAAS DBA wrote:
> we have a client using veeam to do vmware snapshots of the db servers,
> unfortunately as far as I can tell any restore of a veeam snapshot
> also restores the state, which means the db gets restored in an online state.
> As far as I know, once the db is online we cannot force it back into
> recovery mode to do a PITR recovery, correct?
>
> Anyone know of a way to do PITR recoveries with veeam?

It is fairly simple.

Before the snapshot, run

   SELECT pg_start_backup('label of your choice', TRUE);

After the snapshot, run

   SELECT pg_stop_backup();

Yours,
Laurenz Albe


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus

On 14/12/2017 10:19, Laurenz Albe wrote:
> PropAAS DBA wrote:
>> we have a client using veeam to do vmware snapshots of the db servers,
>> unfortunately as far as I can tell any restore of a veeam snapshot also
>> restores the state, which means the db gets restored in an online state.
>> As far as I know, once the db is online we cannot force it back into
>> recovery mode to do a PITR recovery, correct?
>>
>> Anyone know of a way to do PITR recoveries with veeam?
> It is fairly simple.
>
> Before the snapshot, run
>
>     SELECT pg_start_backup('label of your choice', TRUE);
>
> After the snapshot, run
>
>     SELECT pg_stop_backup();
This will create the data/backup_label file necessary for a minimal consistent recover, but wouldn't he also have a
recovery.confin place, so that the server will keep reading wals till it reaches 
 
the recovery target? And if the system comes up live he won't have the chance to create this directory.
Basically what we do ourselves is exactly what you suggest. I'll have to ask if state (mem/cpu/registers/etc) is
restoredas well.
 
>
> Yours,
> Laurenz Albe
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Achilleas Mantzios wrote:
> > > Anyone know of a way to do PITR recoveries with veeam?
> > 
> > It is fairly simple.
> > 
> > Before the snapshot, run
> > 
> >      SELECT pg_start_backup('label of your choice', TRUE);
> > 
> > After the snapshot, run
> > 
> >      SELECT pg_stop_backup();
> 
> This will create the data/backup_label file necessary for a minimal consistent recover,
> but wouldn't he also have a recovery.conf in place, so that the server will keep
> reading wals till it reaches the recovery target?

Yes, for point-in-time recovery you would need a recovery.conf file
with at least a "restore_command" in it.

> And if the system comes up live he won't have the chance to create this directory.

That's an interesting point; I didn't think of it.

One thing I can think of is to disable PostgreSQL autostart
while you take the snapshot.

> Basically what we do ourselves is exactly what you suggest.
> I'll have to ask if state (mem/cpu/registers/etc) is restored as well.

Such a kind of snapshot wouldn't be useful for a database backup.
You only want the storage.

Yours,
Laurenz Albe

On 12/14/2017 01:19 AM, Laurenz Albe wrote:
> PropAAS DBA wrote:
>> we have a client using veeam to do vmware snapshots of the db servers,
>> unfortunately as far as I can tell any restore of a veeam snapshot also
>> restores the state, which means the db gets restored in an online state.
>> As far as I know, once the db is online we cannot force it back into
>> recovery mode to do a PITR recovery, correct?
>>
>> Anyone know of a way to do PITR recoveries with veeam?
> It is fairly simple.
>
> Before the snapshot, run
>
>     SELECT pg_start_backup('label of your choice', TRUE);
>
> After the snapshot, run
>
>     SELECT pg_stop_backup();
>
> Yours,
> Laurenz Albe

However restoring the snapshot with veeam will restore the db to a 
running/online state, which means we cannot force it to go back into 
recovery mode yes?