Обсуждение: Re: [ADMIN] Backup

Поиск
Список
Период
Сортировка

Re: [ADMIN] Backup

От
"Phillip Smith"
Дата:
> 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: [ADMIN] Backup

От
Simon Riggs
Дата:
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: [ADMIN] Backup

От
Chander Ganesan
Дата:
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: [ADMIN] Backup

От
Simon Riggs
Дата:
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: [ADMIN] Backup

От
Martijn van Oosterhout
Дата:
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

Вложения

Re: [ADMIN] Backup

От
Magnus Hagander
Дата:
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: [ADMIN] Backup

От
Chander Ganesan
Дата:
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: [ADMIN] Backup

От
Chander Ganesan
Дата:
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: [ADMIN] Backup

От
Alvaro Herrera
Дата:
> 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: [ADMIN] Backup

От
Simon Riggs
Дата:
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: [ADMIN] Backup

От
Simon Riggs
Дата:
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