Thread: Backup
Hi,
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. We presently run VERITAS ver9.1 on windows2003 server. What is being used by users out there now. We are thinking of upgrading to the latest Symantec backup exec software but am not sure if this version does backup PostgreSQL. When called for support they told me to go to the Symantec site and search the compatibility list. I didn't find anything dealing with PostgreSQL database.
Any answer would help me simplify my backup situation.
Thanks,
Dominic Carlucci
Production Control Analyst
CAE Inc.
8585 Cote de Liesse
St. Laurent, Quebec H4T 1G6
(514)341-2000 #2936
Carlucci@cae.ca
> 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 Jan 24, 2008 1:08 PM, Dominic Carlucci <carlucci@cae.com> wrote: > > > Hi, > 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. We presently run VERITAS ver9.1 on > windows2003 server. What is being used by users out there now. We are > thinking of upgrading to the latest Symantec backup exec software but am not > sure if this version does backup PostgreSQL. When called for support they > told me to go to the Symantec site and search the compatibility list. I > didn't find anything dealing with PostgreSQL database. > Any answer would help me simplify my backup situation. Not sure if there's some kind of special plug in for Symantec, but basically, you want to run pg_dump or pg_dumpall and then point symantec to back up the file thus created.
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
PostgreSQL has its own inbuilt mechanism for backing up the database. you can refer to the postgres manual online for more information.
http://www.postgresql.org/docs/8.2/interactive/backup.html
- Vishal
Live the life in style with MSN Lifestyle. Check out! Try it now!
http://www.postgresql.org/docs/8.2/interactive/backup.html
- Vishal
Subject: [ADMIN] Backup
Date: Thu, 24 Jan 2008 14:08:26 -0500
From: carlucci@cae.com
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
CC: carlucci@cae.ca
Hi,
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. We presently run VERITAS ver9.1 on windows2003 server. What is being used by users out there now. We are thinking of upgrading to the latest Symantec backup exec software but am not sure if this version does backup PostgreSQL. When called for support they told me to go to the Symantec site and search the compatibility list. I didn't find anything dealing with PostgreSQL database.
Any answer would help me simplify my backup situation.
Thanks,
Dominic Carlucci
Production Control Analyst
CAE Inc.
8585 Cote de Liesse
St. Laurent, Quebec H4T 1G6
(514)341-2000 #2936
Carlucci@cae.ca
Live the life in style with MSN Lifestyle. Check out! Try it now!
AFAIK Dominic needs a plug-in certified by Symantec, and that is not the case. As you may have read prior to this mail, the common way is to pg_dump against a file, picking up that file later with BackupExec as a regular file. We are currently (www.globant.com) using it that way, no problems at all. gb.- On Jan 30, 2008 2:15 AM, Vishal Arora <aroravishal22@hotmail.com> wrote: > > PostgreSQL has its own inbuilt mechanism for backing up the database. you > can refer to the postgres manual online for more information. > > http://www.postgresql.org/docs/8.2/interactive/backup.html > > - Vishal > > > > > > ________________________________ > Subject: [ADMIN] Backup > Date: Thu, 24 Jan 2008 14:08:26 -0500 > From: carlucci@cae.com > To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org > CC: carlucci@cae.ca > > > > > Hi, > 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. We presently run VERITAS ver9.1 on > windows2003 server. What is being used by users out there now. We are > thinking of upgrading to the latest Symantec backup exec software but am not > sure if this version does backup PostgreSQL. When called for support they > told me to go to the Symantec site and search the compatibility list. I > didn't find anything dealing with PostgreSQL database. > Any answer would help me simplify my backup situation. > Thanks, > > > Dominic Carlucci > Production Control Analyst > > CAE Inc. > 8585 Cote de Liesse > St. Laurent, Quebec H4T 1G6 > > (514)341-2000 #2936 > Carlucci@cae.ca > > > > > ________________________________ > Live the life in style with MSN Lifestyle. Check out! Try it now! > -- Guido Barosio ----------------------- http://www.globant.com guido.barosio@globant.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
Attachment
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
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 One more reason to consider using Solaris ZFS -- it does consistent snapshots across all file systems. -- Best regards, Hannes Dorbath
Hannes Dorbath wrote: > 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 > > One more reason to consider using Solaris ZFS -- it does consistent > snapshots across all file systems. In general file system snapshots are quite a pain with Linux LVM. Even when using separate spindles for the snapshot COW area the impact on write performance on the origin LV is still huge. You'll have a hard time to write with more than 15MB/sec to the origin LV. Additionally when you lost your COW device there is this nice bug in vgreduce --removemissing that will silently delete your origin LV as well: http://readlist.com/lists/redhat.com/linux-lvm/0/2025.html Not to mention that LVM does not support write barriers and does mess with any kind of stripe alignment of your file system. -- Best regards, Hannes Dorbath