Обсуждение: 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?
On Thu, Dec 14, 2017 at 4:29 PM, PropAAS DBA <dba@propaas.com> wrote:
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?
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,It is fairly simple.
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?
Before the snapshot, run
SELECT pg_start_backup('label of your choice', TRUE);
After the snapshot, run
SELECT pg_stop_backup();
Yours,
Laurenz Albe
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.