Обсуждение: [GENERAL] VM-Ware Backup of VM safe?
We run a PostgreSQL 9.6 server in a virtual machine. The virtual machine is managed by the customer. He does backup the VM. Is this enough, is this safe? Regards, Thomas Güttler -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli/programming-guidelines -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Thomas Güttler wrote: > We run a PostgreSQL 9.6 server in a virtual machine. > > The virtual machine is managed by the customer. > > He does backup the VM. > > Is this enough, is this safe? I don't know about VMware, but the general rule is that if the backup is truly atomic (it is guaranteed to capture a consistent state of the file system), you can use it to backup the database. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, 20 Sep 2017 10:03:15 +0200 Thomas Güttler <guettliml@thomas-guettler.de> wrote: > We run a PostgreSQL 9.6 server in a virtual machine. > > The virtual machine is managed by the customer. > > He does backup the VM. > > Is this enough, is this safe? There are so many variables involved with doing that ... I don't think anyone can reliably answer that question. I recommend you put together a periodic test schedule where you restore a machine from the backup and ensure everything works. To be honest, you should be doing that anyway. -- PT <wmoran@potentialtech.com> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 9/20/17 7:36 AM, PT wrote: > On Wed, 20 Sep 2017 10:03:15 +0200 > Thomas Güttler <guettliml@thomas-guettler.de> wrote: > >> We run a PostgreSQL 9.6 server in a virtual machine. >> >> The virtual machine is managed by the customer. >> >> He does backup the VM. >> >> Is this enough, is this safe? > > There are so many variables involved with doing that ... I don't think > anyone can reliably answer that question. > > I recommend you put together a periodic test schedule where you restore > a machine from the backup and ensure everything works. To be honest, you > should be doing that anyway. Restore testing is as must, but a bad backup scheme can result in subtle errors that are very hard to detect. If you can't find specific documentation that your VM backup solution is safe to use with a DBMS then it is almost certainly not safe. Even if it says it is there are potential gotchas. For example, the backup may not be consistent if you are using multiple volumes. In addition, data loss on restore will be greater if there is no WAL archive to play forward from. -- -David david@pgmasters.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Il 20/09/2017 13:36, PT ha scritto: > On Wed, 20 Sep 2017 10:03:15 +0200 > Thomas Güttler <guettliml@thomas-guettler.de> wrote: > >> We run a PostgreSQL 9.6 server in a virtual machine. >> >> The virtual machine is managed by the customer. >> >> He does backup the VM. >> >> Is this enough, is this safe? > There are so many variables involved with doing that ... I don't think > anyone can reliably answer that question. > > I recommend you put together a periodic test schedule where you restore > a machine from the backup and ensure everything works. To be honest, you > should be doing that anyway. > In addition, can you afford to lose data? You take a snapshot, say, nightly at 3am, if you have an incident at 6pm and nothing is recoverable, you need to recover from backup and doing so you lose 15 hours of data updates. If you can't afford that, then you should implement something like WAL shipping (with tools like barman) that, given a backup made via pg_basebackup(), it's able to rewind all WAL files and bring you in the last state (that's Point-In-Time Recovery feature). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, 20 Sep 2017 10:03:15 +0200, Thomas Güttler <guettliml@thomas-guettler.de> wrote: >We run a PostgreSQL 9.6 server in a virtual machine. >The virtual machine is managed by the customer. >He does backup the VM. > >Is this enough, is this safe? It is "safe" if the VM is shut down first or if the backup is by copying a point-in-time snapshot of the VM. Whether it is "enough" is a matter of opinion. Backing up a VM basically is the equivalent of imaging a hard drive: it's great if the filesystem is intact and the image is clean ... otherwise it's not so good. If the database files on the VM's "drive" develop errors, those errors will be preserved in the VM backup. George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> -----Ursprüngliche Nachricht----- > Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] Im Auftrag von Thomas Güttler > Gesendet: Mittwoch, 20. September 2017 10:03 > An: pgsql-general@postgresql.org > Betreff: [GENERAL] VM-Ware Backup of VM safe? > > We run a PostgreSQL 9.6 server in a virtual machine. > > The virtual machine is managed by the customer. > > He does backup the VM. > > Is this enough, is this safe? When you run MS SQL Server on Hyper-V / Windows, the SQL Server provides ist own VSS writer responding to a taking a snapshot.This ensures that the image oft he SQL database is in a safe stake when the backup is written. I am not sure about VM-Ware, but in any case I don't think that PostgreSQL provides anything similar to a VSS writer (neitheron Windows nor on Linux), so the answer is most likely "no, it is not safe". Regards, Klaus --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. https://www.avast.com/antivirus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Il 20/09/2017 17:15, Klaus P. Pieper ha scritto: >> -----Ursprüngliche Nachricht----- >> Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >> owner@postgresql.org] Im Auftrag von Thomas Güttler >> Gesendet: Mittwoch, 20. September 2017 10:03 >> An: pgsql-general@postgresql.org >> Betreff: [GENERAL] VM-Ware Backup of VM safe? >> >> We run a PostgreSQL 9.6 server in a virtual machine. >> >> The virtual machine is managed by the customer. >> >> He does backup the VM. >> >> Is this enough, is this safe? > When you run MS SQL Server on Hyper-V / Windows, the SQL Server provides ist own VSS writer responding to a taking a snapshot.This ensures that the image oft he SQL database is in a safe stake when the backup is written. I may be wrong, as I don't know MS SQL Server, but in the way you describe it, you make me come in mind pg_basebackup(). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, 20 Sep 2017 17:15:36 +0200, "Klaus P. Pieper" <kpi6288@gmail.com> wrote: >> -----Ursprüngliche Nachricht----- >> Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >> owner@postgresql.org] Im Auftrag von Thomas Güttler >> Gesendet: Mittwoch, 20. September 2017 10:03 >> An: pgsql-general@postgresql.org >> Betreff: [GENERAL] VM-Ware Backup of VM safe? >> >> We run a PostgreSQL 9.6 server in a virtual machine. >> >> The virtual machine is managed by the customer. >> >> He does backup the VM. >> >> Is this enough, is this safe? > >When you run MS SQL Server on Hyper-V / Windows, the SQL Server >provides ist own VSS writer responding to a taking a snapshot. >This ensures that the image oft he SQL database is in a safe >stake when the backup is written. > >I am not sure about VM-Ware, but in any case I don't think that >PostgreSQL provides anything similar to a VSS writer >(neither on Windows nor on Linux), so the answer is most likely >"no, it is not safe". Vmware does not provide VSS support in the base product - it is available if you install the Data Recovery extensions. FWIW, Virtualbox doesn't provide VSS support either. But VSS is needed only to copy VM files *while* they are in use. If you snapshot the VM, the snapshot files then are read-only and can be freely copied. As long as the backup avoids the currently active files, there is no danger. George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] Im Auftrag von George Neuner > > But VSS is needed only to copy VM files *while* they are in use. If you snapshot > the VM, the snapshot files then are read-only and can be freely copied. As long > as the backup avoids the currently active files, there is no danger. But if you take a snapshot without VSS writer functions, I tend to believe that there is a serious potential that relevantinformation is still only in RAM and not written to disk. This would get lost in the snapshot. I may be wrong, but my understanding of a VSS writer is that all transaction and log files are flushed to disk prior tothe snapshot. Klaus --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. https://www.avast.com/antivirus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> -----Ursprüngliche Nachricht----- > Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] Im Auftrag von Moreno Andreo > Gesendet: Mittwoch, 20. September 2017 17:42 > I may be wrong, as I don't know MS SQL Server, but in the way you describe it, > you make me come in mind pg_basebackup(). A VSS writer just prepares a snapshot of the (virtual) machine while pg_basebackup make actually a (safe) copy of the cluster- important differences are that VSS writer 1) is called by the operating system, 2) is much faster and 3) does notneed space for a complete backup. But you are right: if you can ensure that pg_basebackup has finished prior to making a backup of the virtual machine, thisis certainly safe. Klaus --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. https://www.avast.com/antivirus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, Sep 20, 2017 at 5:45 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > Thomas Güttler wrote: >> We run a PostgreSQL 9.6 server in a virtual machine. >> >> The virtual machine is managed by the customer. >> >> He does backup the VM. >> >> Is this enough, is this safe? > > I don't know about VMware, I heard about one or two things :) > but the general rule is that > if the backup is truly atomic (it is guaranteed to capture > a consistent state of the file system), you can use it > to backup the database. There are two types of snapshots: quiesced and non-quiesced. You definitely want a quiesced snapshot when taking a backup so as the system gets into a consistent state when working on it. There should be an option related to that on the vSphere client managing the VM, so make sure that quiesced is enabled. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, 20 Sep 2017 20:24:05 +0200, "Klaus P. Pieper" <kpi6288@gmail.com> wrote: >> Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >> owner@postgresql.org] Im Auftrag von George Neuner >> >> But VSS is needed only to copy VM files *while* they are in >> use. If you snapshot the VM, the snapshot files then are read-only >> and can be freely copied. As long as the backup avoids the >> currently active files, there is no danger. > >But if you take a snapshot without VSS writer functions, I tend >to believe that there is a serious potential that relevant >information is still only in RAM and not written to disk. This > would get lost in the snapshot. I can't speak for all VM managers, but Vmware's standard static snapshots *do* capture both the memory and power states of the machine. If a snapshot is taken while a machine is running, restarting from that snapshot is the same as if the machine woke up from suspension. >I may be wrong, but my understanding of a VSS writer is that all >transaction and log files are flushed to disk prior tot he snapshot. You understanding is correct - but I think you are maybe misapplying it to this case. VSS operates in the *host*, not in the virtual machine. And VSS is purely a Windows mechanism - it does not apply in Unix or Linux. VSS is a protocol to enable backing up open application files without disruptions like share locks. A backup program can request an aware application to produce demand snapshots of its open files. The backup then copies the snapshots while the application continues to use the original files. VSS snapshots themselves are ephemeral: they are unnamed temporary files that are deleted when closed. In principle, a VM manager could respond to a VSS request by signaling aware applications in running VMs to dump state (if relevant). But that would only make a VSS snapshot of the machine's "disk" a little more up to date than one taken statically via the standard mechanism. And it requires that applications running in the VM, under whatever guest OS, know how to catch and respond to the VM manager's signal. In reality, the VSS aware VM managers I know of [including Vmware] don't do that. Instead, when requested by backup, they simply take ar standard snapshot of each running machine. But instead of saving the snapshots statically, they create VSS ephemeral snapshots that disappear when the backup is finished with them. The long-winded point is that you don't need VSS unless your host is running Windows, you want to backup machines while they are running, and you want the backups to be as "fresh" as possible. Bringing this discussion back to Postgresql, there is no difference between restoring your database from a saved backup vs rolling back the virtual machine running Postgresql to a saved snapshot. The end result is you have lost whatever was done after the save point. If you are happy with your VM manager's standard snapshots, there is no need for additional complexity. My point initially was simply that the VM manager's snapshot mechanism saves a disk image, which will just as happily preserve a damaged disk as a good one. To be safe(st) I think it's smart to back up your databases using Postgresql's own mechanisms in addition to backing up your VMs. George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> -----Ursprüngliche Nachricht----- > Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] Im Auftrag von George Neuner > Gesendet: Donnerstag, 21. September 2017 18:35 > > I can't speak for all VM managers, but Vmware's standard static snapshots > *do* capture both the memory and power states of the machine. If a snapshot > is taken while a machine is running, restarting from that snapshot is the same > as if the machine woke up from suspension. Thanks for clarification. > > > >I may be wrong, but my understanding of a VSS writer is that all > >transaction and log files are flushed to disk prior tot he snapshot. > > You understanding is correct - but I think you are maybe misapplying it to this > case. VSS operates in the *host*, not in the virtual machine. And VSS is purely > a Windows mechanism - it does not apply in Unix or Linux. [...] I am aware that VSS is purely Windows, and your comment about VSS aware application is true. Backup programs / VM managers like Data Protection Manager trigger these applications to put the files into a safe state prior to the snapshot. As far as I am aware, MS SQL server provides this mechanism while PostgreSQL does not. And for this reason, I would certainly encourage using PostgreSQL's backup mechanisms for business critical applications. I just spoke with several admins who were not aware of these differences between several databases. Klaus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Fri, 22 Sep 2017 16:14:23 +0200, "Klaus P. Pieper" <kpi6288@gmail.com> wrote: >I am aware that VSS is purely Windows, and your comment about VSS aware >application is true. Backup programs / VM managers like Data Protection >Manager trigger these applications to put the files into a safe state >prior to the snapshot. The standard snapshot mechanism suspends all processes while the memory is saved. The writable disk image swap occurs in the hypervisor and is transparent to the VM. Data Protection adds the ability to snapshot on demand for backups and signal aware programs in the VM to save state before being suspended. WRT shapshot integrity, I don't think the latter really adds much, but YMMV. George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] VM-Ware Backup of VM safe?: snapshots: quiesced andnon-quiesced
От
Thomas Güttler
Дата:
Thank you for "quiesced vs non-quiesced". I will ask the company providing the VM. Regards, Thomas Güttler Am 21.09.2017 um 03:48 schrieb Michael Paquier: > On Wed, Sep 20, 2017 at 5:45 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: >> Thomas Güttler wrote: >>> We run a PostgreSQL 9.6 server in a virtual machine. >>> >>> The virtual machine is managed by the customer. >>> >>> He does backup the VM. >>> >>> Is this enough, is this safe? >> >> I don't know about VMware, > > I heard about one or two things :) > >> but the general rule is that >> if the backup is truly atomic (it is guaranteed to capture >> a consistent state of the file system), you can use it >> to backup the database. > > There are two types of snapshots: quiesced and non-quiesced. You > definitely want a quiesced snapshot when taking a backup so as the > system gets into a consistent state when working on it. There should > be an option related to that on the vSphere client managing the VM, so > make sure that quiesced is enabled. > -- Thomas Guettler http://www.thomas-guettler.de/ I am looking for feedback: https://github.com/guettli/programming-guidelines -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general