Обсуждение: virtualized snapshots and PITR

Поиск
Список
Период
Сортировка

virtualized snapshots and PITR

От
PropAAS DBA
Дата:
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




Re: virtualized snapshots and PITR

От
Laurenz Albe
Дата:
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


RE: virtualized snapshots and PITR

От
Olivier Leprêtre
Дата:
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



Re: virtualized snapshots and PITR

От
Achilleas Mantzios
Дата:
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



Re: virtualized snapshots and PITR

От
Laurenz Albe
Дата:
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


Re: virtualized snapshots and PITR

От
PropAAS DBA
Дата:

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?



Re: virtualized snapshots and PITR

От
Magnus Hagander
Дата:
On Thu, Dec 14, 2017 at 4:29 PM, PropAAS DBA <dba@propaas.com> wrote:


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?

If your snapshots include the memory/cpu state, then you cannot use them for point in time recovery. It only works if what you snapshot is the disk.

If the snapshots include memory/cpu state they should be usable without the WAL. *should* being the operative word. But not for PITR.

I don't know veeam specifically, though, so I don't know how it works there. Unless they specifically document support for it, I wouldn't use it for PITR under any conditions.


--