Thread: Backup

Backup

From
"Dominic Carlucci"
Date:
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

 

 

Re: Backup

From
"Phillip Smith"
Date:
> 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.

Re: Backup

From
"Scott Marlowe"
Date:
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.

Re: Backup

From
Simon Riggs
Date:
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


Re: Backup

From
Vishal Arora
Date:
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!

Re: Backup

From
"Guido Barosio"
Date:
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

Re: [GENERAL] Backup

From
Chander Ganesan
Date:
Simon Riggs wrote:
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.

Re: [GENERAL] Backup

From
Simon Riggs
Date:
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


Re: [GENERAL] Backup

From
Martijn van Oosterhout
Date:
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

Re: [GENERAL] Backup

From
Magnus Hagander
Date:
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

Re: [GENERAL] Backup

From
Chander Ganesan
Date:
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


Re: [GENERAL] Backup

From
Chander Ganesan
Date:
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


Re: [GENERAL] Backup

From
Alvaro Herrera
Date:
> 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

Re: [GENERAL] Backup

From
Simon Riggs
Date:
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


Re: [GENERAL] Backup

From
Simon Riggs
Date:
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


Re: [GENERAL] Backup

From
Hannes Dorbath
Date:
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

Re: [GENERAL] Backup

From
Hannes Dorbath
Date:
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