Обсуждение: Re: [ADMIN] Backup
> We have a center in Europe who has just started to use PostgreSQL and was > asking me if there are any Symantec product or other products that backup > this type of database. It doesn't appear to. I've just been through the whole rigmarole of BackupExec for some Windows Servers, and I couldn't find anything to deal with PostgreSQL. Just dump Postgres to the File System and backup that dump. Depends on what your Recovery Point requirements are. THINK BEFORE YOU PRINT - Save paper if you don't really need to print this *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail.
On Fri, 2008-01-25 at 11:34 +1100, Phillip Smith wrote: > > We have a center in Europe who has just started to use PostgreSQL and was > > asking me if there are any Symantec product or other products that backup > > this type of database. > > It doesn't appear to. The design of the PITR system allows a product-agnostic backup. Anything that can backup a file can backup PostgreSQL. There is no need for special certifications of hologram logos. You may need to write a few lines of script to do it, but that's not a problem surely? So you can use pg_dump or PITR, as you choose. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs wrote:
So long as the product you are using can snapshot the file system prior to performing a backup, you can use just about any product (without having to really do much/any work). When you restore a snapshotted file system that contains your postgresql database, postgresql (on restart) will enter auto-recovery mode and recover (as it does in the case of the crash) to the last transaction that was completed successfully prior to the snapshot being created. Note that this would only work if all your tablespaces were on the same file system, and would be unable to "roll forward" using WAL files generated after the backup.
However, you should keep in mind that - like a pg_dump - you won't be able to perform PITR recovery from such a backup. Also, the recovery time may be non-trivial depending on your WAL settings.
On Fri, 2008-01-25 at 11:34 +1100, Phillip Smith wrote:We have a center in Europe who has just started to use PostgreSQL and was asking me if there are any Symantec product or other products that backup this type of database.It doesn't appear to.The design of the PITR system allows a product-agnostic backup. Anything that can backup a file can backup PostgreSQL. There is no need for special certifications of hologram logos. You may need to write a few lines of script to do it, but that's not a problem surely? So you can use pg_dump or PITR, as you choose.
If you don't mind if you lose some transactions you can also use file system snapshotting....which would work just as well as pg_dump, and probably have less impact on the running database (assuming you had decent enough hardware).
So long as the product you are using can snapshot the file system prior to performing a backup, you can use just about any product (without having to really do much/any work). When you restore a snapshotted file system that contains your postgresql database, postgresql (on restart) will enter auto-recovery mode and recover (as it does in the case of the crash) to the last transaction that was completed successfully prior to the snapshot being created. Note that this would only work if all your tablespaces were on the same file system, and would be unable to "roll forward" using WAL files generated after the backup.
However, you should keep in mind that - like a pg_dump - you won't be able to perform PITR recovery from such a backup. Also, the recovery time may be non-trivial depending on your WAL settings.
-- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com As me about Expert PostgreSQL & PostGIS Training delivered worldwide.
On Thu, 2008-01-31 at 07:21 -0500, Chander Ganesan wrote: > If you don't mind if you lose some transactions That sentence has no place in any discussion about "backup" because the risk is not just a few transactions, it is a corrupt and inconsistent database from which both old and new data would be inaccessible. As far as I am concerned, if any Postgres user loses data then we're all responsible. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Thu, Jan 31, 2008 at 01:28:48PM +0000, Simon Riggs wrote: > That sentence has no place in any discussion about "backup" because the > risk is not just a few transactions, it is a corrupt and inconsistent > database from which both old and new data would be inaccessible. Hmm? I thought the whole point of a filesystem snapshot was that it's the same as if the system crashed. And I was fairly sure we could recover from that... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Вложения
On Thu, Jan 31, 2008 at 03:34:05PM +0100, Martijn van Oosterhout wrote: > On Thu, Jan 31, 2008 at 01:28:48PM +0000, Simon Riggs wrote: > > That sentence has no place in any discussion about "backup" because the > > risk is not just a few transactions, it is a corrupt and inconsistent > > database from which both old and new data would be inaccessible. > > Hmm? I thought the whole point of a filesystem snapshot was that it's > the same as if the system crashed. And I was fairly sure we could > recover from that... That was my assumption as well. *Assuming* that the filesystem snapshot is consistent. There are a bunch of solutions that don't do consistent snapshots between different partitions, so if your WAL or one tablespace is on a different partition, you'll get corruption anyway... (seen this in Big Commercial Database, so that's not a pg problem) //Magnus
Simon Riggs wrote: > On Thu, 2008-01-31 at 07:21 -0500, Chander Ganesan wrote: > >> If you don't mind if you lose some transactions >> > > That sentence has no place in any discussion about "backup" because the > risk is not just a few transactions, it is a corrupt and inconsistent > database from which both old and new data would be inaccessible. > > As far as I am concerned, if any Postgres user loses data then we're all > responsible. > I understand your point, but indicating that you can't trust a point-in-time snapshot of the database is, IMHO, the same as saying you can't trust PostgreSQL's automatic crash recovery, since the two are essentially the same thing... -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com
Magnus Hagander wrote: > On Thu, Jan 31, 2008 at 03:34:05PM +0100, Martijn van Oosterhout wrote: > >> On Thu, Jan 31, 2008 at 01:28:48PM +0000, Simon Riggs wrote: >> >>> That sentence has no place in any discussion about "backup" because the >>> risk is not just a few transactions, it is a corrupt and inconsistent >>> database from which both old and new data would be inaccessible. >>> >> Hmm? I thought the whole point of a filesystem snapshot was that it's >> the same as if the system crashed. And I was fairly sure we could >> recover from that... >> > > That was my assumption as well. *Assuming* that the filesystem snapshot is > consistent. There are a bunch of solutions that don't do consistent > snapshots between different partitions, so if your WAL or one tablespace is > on a different partition, you'll get corruption anyway... (seen this in > Big Commercial Database, so that's not a pg problem) > Agreed. That's why I made it a point to mention that all of your tablespaces should be on the same file system... In hindsight, I should have also stated that your WAL logs should be on the same file system as well... -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com
> Simon Riggs wrote: >> As far as I am concerned, if any Postgres user loses data then we're all >> responsible. Remember, our license says this software is given without any warranty whatsoever, implicit or explicit, written or implied, given or sold, alive or deceased. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Thu, 2008-01-31 at 12:09 -0300, Alvaro Herrera wrote: > > Simon Riggs wrote: > > >> As far as I am concerned, if any Postgres user loses data then we're all > >> responsible. > > Remember, our license says this software is given without any warranty > whatsoever, implicit or explicit, written or implied, given or sold, > alive or deceased. Yes! ...I meant via the free press, not via the courts. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Thu, 2008-01-31 at 10:02 -0500, Chander Ganesan wrote: > Magnus Hagander wrote: > > On Thu, Jan 31, 2008 at 03:34:05PM +0100, Martijn van Oosterhout wrote: > > > >> On Thu, Jan 31, 2008 at 01:28:48PM +0000, Simon Riggs wrote: > >> > >>> That sentence has no place in any discussion about "backup" because the > >>> risk is not just a few transactions, it is a corrupt and inconsistent > >>> database from which both old and new data would be inaccessible. > >>> > >> Hmm? I thought the whole point of a filesystem snapshot was that it's > >> the same as if the system crashed. And I was fairly sure we could > >> recover from that... > >> > > > > That was my assumption as well. *Assuming* that the filesystem snapshot is > > consistent. There are a bunch of solutions that don't do consistent > > snapshots between different partitions, so if your WAL or one tablespace is > > on a different partition, you'll get corruption anyway... (seen this in > > Big Commercial Database, so that's not a pg problem) > > > Agreed. That's why I made it a point to mention that all of your > tablespaces should be on the same file system... In hindsight, I should > have also stated that your WAL logs should be on the same file system as > well... I think we all understand and agree, I just start twitching when anyone talks about it being OK to lose transactions when backing up. You meant the ones currently in progress, not the ones already committed and on disk. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com