Thread: Re: Hot Backup

MvO> http://www.postgresql.org/idocs/index.php?wal.html
- The URL you refer to is the ch11 I was refering to. It seems that this chapter is not as easily understandable as it
should...
It says that with WAL, "pg is able to garantee consistency in the case of a crash".
OK, but I think  is about /consistency/.
For what I understand, it just says that in the case of a core dump of a server process (improbable) or a power cut
(probable)or an unwanted kill -9 (may happen), Pg will not have any corrupted table or index.  

Cool, but not enough.

As Timur pointed out, I was refering to a disk crash or total loss of a server.
In this case, you loose up to 1 day of data.

MvO> I've never lost any data with postgres, even if it's crashed, even without
MvO> WAL.
Your're a lucky guy, but Pg may not be the weakest part of your information system.
In my short DBA life ( 3 years, Oracle & MS SQL ), I have already seen twice the case when a WHOLE raid5 array was
brokenand all the data lost. 
One time, it was the controller which wrote ch... on the disks, the other time, a power failure/peak/else crashed all
ofthe 8 disks of the array. 
Both times, the incremental backup method reduced the data loss to almost nothing.

> There is a need in "incremental" backup, which backs up only those
> transactions which has been fulfilled after last "full dump" or last
> "incremental dump".  These backups should be done quite painlessly -
> just copy some part of WAL, and should be small enough (compared to
> full dump), so they can be done each hour or even more frequently..
>
> I hope sometime PostgreSQL will support that. :-)

So do I.
I think this would be on top of my "missing features" list.

As someone said, Replication may be a way to reduce the risks.

E.D.

-------------------------------------------------------------------------------
Erwan DUROSELLE    //    SEAFRANCE DSI
Responsable Bases de Données  //  Databases Manager
Tel: +33 (0)1 55 31 59 70    //    Fax: +33 (0)1 55 31 85 28
email: eduroselle@seafrance.fr
-------------------------------------------------------------------------------


>>> "Timur V. Irmatov" <itvthor@sdf.lonestar.org> 08/10/2002 13:00 >>>
Martijn!

Tuesday, October 08, 2002, 3:45:13 PM, you wrote:

>>  -  No, PostgreSQL does NOT provide a way to restore a database up to the
>> last commited transaction, with a reapply of the WAL, as Oracle or SQL
>> Server ( and others, I guess) do. That would be a VERY good feature. See
>> Administrator's guide ch11

MvO> Umm, I thought the whole point of WAL was that if the database crashed, the
MvO> WAL would provide the info to replay to the last committed transaction.


MvO> ... because we know that in the event of a crash we will be able to recover
MvO> the database using the log: ...

MvO> These docs seem to corrobrate this.

>> So, with Pg, if you backup your db every night with pg_dump, and your
>> server crashes during the day, you will loose up to one day of work.

MvO> I've never lost any data with postgres, even if it's crashed, even without
MvO> WAL.

Suppose you made your nightly backup, and then after a day of work
the building where your server is located disappears in flames..

That's it - you lost one day of work (of course, if your dumps where
stored outside that building otherwise you lost everything)..

There is a need in "incremental" backup, which backs up only those
transactions which has been fulfilled after last "full dump" or last
"incremental dump".  These backups should be done quite painlessly -
just copy some part of WAL, and should be small enough (compared to
full dump), so they can be done each hour or even more frequently..

I hope sometime PostgreSQL will support that. :-)

Sincerely Yours,
Timur
mailto:itvthor@sdf.lonestar.org


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On 8 Oct 2002 at 14:17, Erwan DUROSELLE wrote:

>
> MvO> http://www.postgresql.org/idocs/index.php?wal.html
> - The URL you refer to is the ch11 I was refering to. It seems that this chapter is not as easily understandable as
itshould... 
> It says that with WAL, "pg is able to garantee consistency in the case of a crash".
> OK, but I think  is about /consistency/.
> For what I understand, it just says that in the case of a core dump of a server process (improbable) or a power cut
(probable)or an unwanted kill -9 (may happen), Pg will not have any corrupted table or index.  
>
> Cool, but not enough.
>
> As Timur pointed out, I was refering to a disk crash or total loss of a server.
> In this case, you loose up to 1 day of data.
> > There is a need in "incremental" backup, which backs up only those
> > transactions which has been fulfilled after last "full dump" or last
> > "incremental dump".  These backups should be done quite painlessly -
> > just copy some part of WAL, and should be small enough (compared to
> > full dump), so they can be done each hour or even more frequently..
> >
> > I hope sometime PostgreSQL will support that. :-)

Well, there are replication solutions which rsyncs WAL files after they are
rotated so two database instances are upto sync with each other at a difference
of one WAL file. If you are interested I can post the pdf.

I guess that takes care of scenario you plan to avoid..

Bye
 Shridhar

--
Truthful, adj.:    Dumb and illiterate.        -- Ambrose Bierce, "The Devil's
Dictionary"

On Tue, 2002-10-08 at 08:58, Shridhar Daithankar wrote:
> On 8 Oct 2002 at 14:17, Erwan DUROSELLE wrote:
> > MvO> http://www.postgresql.org/idocs/index.php?wal.html
> > - The URL you refer to is the ch11 I was refering to. It seems that this chapter is not as easily understandable as
itshould... 
> > It says that with WAL, "pg is able to garantee consistency in the case of a crash".
> > OK, but I think  is about /consistency/.
> > For what I understand, it just says that in the case of a core dump of a server process (improbable) or a power cut
(probable)or an unwanted kill -9 (may happen), Pg will not have any corrupted table or index.  
> >
> > Cool, but not enough.
> >
> > As Timur pointed out, I was refering to a disk crash or total loss of a server.
> > In this case, you loose up to 1 day of data.

Is it me or do doomsdays scenarios sometimes seem a little silly? I'd
like to ask just where are you storing your "incremental backups" with
Oracle/m$ sql ?? If it's on the same drive, then when you drive craps
out you've lost the incremental backups as well.  Are you putting them
on a different drive (you can do that with the WAL) you'd still have the
problem that if the building went up in smoke you'd lose that
incremental backup. Unless you are doing "incremental backups" to a
computer in another physical location, you still fail all of your
scenarios.

> > > There is a need in "incremental" backup, which backs up only those
> > > transactions which has been fulfilled after last "full dump" or last
> > > "incremental dump".  These backups should be done quite painlessly -
> > > just copy some part of WAL, and should be small enough (compared to
> > > full dump), so they can be done each hour or even more frequently..
> > >
> > > I hope sometime PostgreSQL will support that. :-)
>
> Well, there are replication solutions which rsyncs WAL files after they are
> rotated so two database instances are upto sync with each other at a difference
> of one WAL file. If you are interested I can post the pdf.
>
> I guess that takes care of scenario you plan to avoid..
>

This type of scenario sounds as good as the above mentioned methods for
oracle/m$ server. Could you post your pdf? Seems like it might be worth
adding to the techdocs site.

Robert Treat

On 8 Oct 2002 at 9:40, Robert Treat wrote:
> Is it me or do doomsdays scenarios sometimes seem a little silly? I'd
> like to ask just where are you storing your "incremental backups" with
> Oracle/m$ sql ?? If it's on the same drive, then when you drive craps
> out you've lost the incremental backups as well.  Are you putting them
> on a different drive (you can do that with the WAL) you'd still have the
> problem that if the building went up in smoke you'd lose that
> incremental backup. Unless you are doing "incremental backups" to a
> computer in another physical location, you still fail all of your
> scenarios.

Well, all I can say is having a sync'ed and replicated database ssytem is good.
Either from load sharing point of view or from fail over point.

Forget backup. If you are upto restoring from backup, doesn't matter most of
the times whether you are restoring from dump or cycling thr. thousands of WAL
files..

> This type of scenario sounds as good as the above mentioned methods for
> oracle/m$ server. Could you post your pdf? Seems like it might be worth
> adding to the techdocs site.

Well, I found it googling around. Se if this helps...I have posted this before
dunno which list. This crosposting habit of PG lists made me to lose tracks of
where things originated and where they ended.. Not that it's bad..

HTH
Bye
 Shridhar

--
like:    When being alive at the same time is a wonderful coincidence.

Hi Erwan,

Erwan DUROSELLE wrote:
<snip>
> As someone said, Replication may be a way to reduce the risks.

Yes, it can, but it all depends on how much the data is worth, what kind
of load happens, etc.

Something as simple as a master->slave replication setup will do,
because the master would generally be the beefy box doing processing,
and the slave database server only receives changes, etc.

Useful for lots of circumstances, although not the only solution.

:-)

Regards and best wishes,

Justin Clift


> E.D.
>
> -------------------------------------------------------------------------------
> Erwan DUROSELLE    //    SEAFRANCE DSI
> Responsable Bases de Données  //  Databases Manager
> Tel: +33 (0)1 55 31 59 70    //    Fax: +33 (0)1 55 31 85 28
> email: eduroselle@seafrance.fr
> -------------------------------------------------------------------------------

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

On Tue, Oct 08, 2002 at 09:40:44AM -0400, Robert Treat wrote:

> Is it me or do doomsdays scenarios sometimes seem a little silly? I'd

Not if your contract requires five-nines reliablility and no more
than 180 minutes of downtime _ever_.  Is five-nines realistic?  For
most purposes, probably not, according to recent pronouncements (see,
e.g. <http://www.bcr.com/bcrmag/2002/05/p22.asp>).  But it's in
lots of contracts anyway.

> like to ask just where are you storing your "incremental backups" with
> Oracle/m$ sql ?? If it's on the same drive, then when you drive craps

The more or less standard way of doing this is to stream the
PITR-required stuff to another device on another controller -- lots
of people stream to tape.  People have been doing this for ages,
partly because disks used to be (a) expensive and (b) unreliable.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

I think you missed the part of the thread where the nuclear bomb hit the
data center.  hmm... maybe it wasn't a nuclear bomb, but it was getting
there. :-)

BTW - I believe we'll have real PITR in 7.4, about 6 months away.

Robert Treat

On Tue, 2002-10-22 at 10:34, Andrew Sullivan wrote:
> On Tue, Oct 08, 2002 at 09:40:44AM -0400, Robert Treat wrote:
>
> > Is it me or do doomsdays scenarios sometimes seem a little silly? I'd
>
> Not if your contract requires five-nines reliablility and no more
> than 180 minutes of downtime _ever_.  Is five-nines realistic?  For
> most purposes, probably not, according to recent pronouncements (see,
> e.g. <http://www.bcr.com/bcrmag/2002/05/p22.asp>).  But it's in
> lots of contracts anyway.
>
> > like to ask just where are you storing your "incremental backups" with
> > Oracle/m$ sql ?? If it's on the same drive, then when you drive craps
>
> The more or less standard way of doing this is to stream the
> PITR-required stuff to another device on another controller -- lots
> of people stream to tape.  People have been doing this for ages,
> partly because disks used to be (a) expensive and (b) unreliable.
>

On Thu, Oct 24, 2002 at 10:42:00AM -0400, Robert Treat wrote:
> I think you missed the part of the thread where the nuclear bomb hit the
> data center.  hmm... maybe it wasn't a nuclear bomb, but it was getting
> there. :-)

No, I didn't miss it.  Have a look at the Internet Society bid to run
.org -- it's available for public consumption on ICANN's site.  One may
belive that, if people are launching nuclear attacks, suicide
bombings, and anthrax releases, the disposition of some set of data
one looks after is unlikely to be of tremendous importance.  But
lawyers and insurers don't think that way, and if you really want
PostgreSQL to be taken seriously in the "enterprise market", you have
to please lawyers and insurers.

Having undertaken the exercise, I really can say that it is a little
strange to think about what would happen to data I am in charge of in
case a fairly large US centre were completely blown off the map.  But
with a little careful planning, you actually _can_ think about that,
and provide strong assurances that things won't get lost.  But it
doesn't pay to call such questions "silly", because they are
questions that people will demand answers to before they entrust you
with their millions of dollars of data.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

I must agree Andrew. Having worked in the Financial sector these kinds
of questions are actually put forward with genuine concern, and when
people are paying the "big bucks" to here the answers, nothing is ever
"yeah, right", it's always answered with a costed solution to meet the
needs.
Given the events of Sept 11th, the serious consideration of a building
being destroyed is actually a potential reality. However, rather than
being a terrorist attack, it could easily be a natural disaster, like an
earthquake or a tornado. Admittedly nuclear strikes are remote ;-) Well,
I'd like to think that, seeing I live in New Zealand.

Most of the upper management don't have a clue as to what can be done on
a lower level to protect that data that is being gathered. In protecting
data, having a reliable OS and Database application is only part of the
solution. I've worked on Enterprise solutions that contain massive RAID
5 volumes with hard mirroring occuring across a WAN to a remote site,
physically located in another building, JUST, to cover the what if
scenarios, in addition, there was log shipping occuring every 30mins to
remote servers in other centres and then nightly backups of those logs.

I tend to take the view that doing database work is best taken from a
holistic approach. I feel happy once I have an overall level of
satisfaction that not only will the database and OS perform to
expectation, but that the "data", the one thing I am really concerned
about is always recoverable.

Basically, backup is available to N degrees of complexity, what kills it
is how much you're willing to spend and at what level you feel satisfied
that you could put your hand over your heart and say, "Yes, this data is
100% recoverable from this point in time, regardless of the following
circumstances...".

My 5c.

Hadley


On Fri, 2002-10-25 at 09:11, Andrew Sullivan wrote:
> On Thu, Oct 24, 2002 at 10:42:00AM -0400, Robert Treat wrote:
> > I think you missed the part of the thread where the nuclear bomb hit the
> > data center.  hmm... maybe it wasn't a nuclear bomb, but it was getting
> > there. :-)
>
> No, I didn't miss it.  Have a look at the Internet Society bid to run
> .org -- it's available for public consumption on ICANN's site.  One may
> belive that, if people are launching nuclear attacks, suicide
> bombings, and anthrax releases, the disposition of some set of data
> one looks after is unlikely to be of tremendous importance.  But
> lawyers and insurers don't think that way, and if you really want
> PostgreSQL to be taken seriously in the "enterprise market", you have
> to please lawyers and insurers.
>
> Having undertaken the exercise, I really can say that it is a little
> strange to think about what would happen to data I am in charge of in
> case a fairly large US centre were completely blown off the map.  But
> with a little careful planning, you actually _can_ think about that,
> and provide strong assurances that things won't get lost.  But it
> doesn't pay to call such questions "silly", because they are
> questions that people will demand answers to before they entrust you
> with their millions of dollars of data.
>
> A
>
> --
> ----
> Andrew Sullivan                         204-4141 Yonge Street
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M2P 2A8
>                                          +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Hadley Willan > Systems Development > Deeper Design Limited.
hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463

On Thu, 2002-10-24 at 16:11, Andrew Sullivan wrote:
> On Thu, Oct 24, 2002 at 10:42:00AM -0400, Robert Treat wrote:
> > I think you missed the part of the thread where the nuclear bomb hit the
> > data center.  hmm... maybe it wasn't a nuclear bomb, but it was getting
> > there. :-)
>
> No, I didn't miss it.  Have a look at the Internet Society bid to run
> .org -- it's available for public consumption on ICANN's site.  One may
> belive that, if people are launching nuclear attacks, suicide
> bombings, and anthrax releases, the disposition of some set of data
> one looks after is unlikely to be of tremendous importance.  But
> lawyers and insurers don't think that way, and if you really want
> PostgreSQL to be taken seriously in the "enterprise market", you have
> to please lawyers and insurers.
>
> Having undertaken the exercise, I really can say that it is a little
> strange to think about what would happen to data I am in charge of in
> case a fairly large US centre were completely blown off the map.  But
> with a little careful planning, you actually _can_ think about that,
> and provide strong assurances that things won't get lost.  But it
> doesn't pay to call such questions "silly", because they are
> questions that people will demand answers to before they entrust you
> with their millions of dollars of data.
>

If someone tries to argue that PostgreSQL isn't viable as a database
solution because it doesn't have PITR, but their PITR solution is
storing all that data on the same machine, well, that's silly. I'm not
saying PITR isn't a good thing, but I can always come up with some
far-fetched way to show you why your backup schema isn't sufficient, the
point is to determine how much data loss is acceptable and then plan
accordingly. (And yes, I have had to plan the what if the data center
gets blown up scenario before, but that doesn't mean I require my
database can withstand global thermal-nuclear war on every project I do)

Robert Treat