Subject: Re: [GENERAL] Do we need more emphasis on backup?

From: Bruce Momjian <[email-masked]>
Date: Mon, 5 Jul 2004 15:09:17 -0400 (EDT)

pgman wrote:
> Jim Seymour wrote:
> > Tom Lane <[email-masked]> wrote:
> > > 
> > [snip]
> > > 
> > > We do need to point out that you're only as reliable as your last
> > > backup.  I'm not sure exactly where to say this.
> > [snip]
> > > 
> > 
> > Hmph.  Backups are for mitigation against a catastrophic failure
> > destroying or corrupting main storage.  And even then: Subtle errors
> > can induce data corruption that may go un-noticed until it's too late.
> > (I.e.:  The last correct backups have been over-written, retired, so
> > old they've become unreadable, so old the data's no longer useful,
> > etc.)
> > 
> > My position is that your data is only as reliable as your hardware,
> > period.  Use cheap (usually PC, sorry) hardware and, well...  I wonder
> > how many people are aware of the fact that the cheaper PCs don't even
> > have parity memory anymore?  Then there are the issues with IDE
> > drives.  (Don't recall those, exactly - don't use 'em.)
> 
> There is a basic misconception that all PC hardware is created equal ---
> that hard drives, mother boards, and RAM are all the same because they
> are all PC-compatible.  Compatible != Similar Quality.
> 
> Not sure where we would document this.  :-(
> 
> Running BSD, I have always had to buy server-class hardware for my home
> machines, and I never regretted it nor had a problem.

Should I add an FAQ discussing hardware selection and the importance of
reliable hardware?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---

From: [email-masked] (Jim Seymour)
Date: Tue, 6 Jul 2004 08:32:15 -0400 (EDT)

[snip]
> > > period.  
[snip]
[snip]
> Should I add an FAQ discussing hardware selection and the importance of

Scary to think that people who don't know enough about running a proper
server, and thus have to be *told* this, are admin'ing databases.

Start with: Gotta have ECC, or at least parity-checking, RAM.

Mention the (some kinds of?) IDE drives issue.

Mention that hardware RAID systems must have battery-backed write
cache.  (I actually had a RAID vendor, whose products no longer had
batter-backed write cache, tell me "Just use a UPS.")

Jim

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 6 Jul 2004 10:00:58 -0400

> server, and thus have to be *told* this, are admin'ing databases.

Maybe it's scary for people in the US or the western/wealthier Europe.
But people also live in other parts of the world (I could even mention
that real people do live in places like Iraq and they even have
computers there).

For us rest-of-the-worlders, it's not always possible to get top of the
line hardware.  I am somewhat lucky because I live in a country where
people can afford an UPS.  Some people are not.

Of course you can talk about the value of the data and how much good
hardware can help you not lose that data -- but truth is, people need to
make an economical analysis on the worthiness of such protection, and
while some of them will be able to afford redundant battery backed SCSI
RAID controllers, some others won't be able to buy an UPS.

So I think it's a good idea to mention that better hardware can be
helpful.  But don't make that sound like if you don't have it, it's the
end of the world or the admin is an uneducated moron.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Hoy es el primer día del resto de mi vida"

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 6 Jul 2004 12:04:05 -0400 (EDT)

> end of the world or the admin is an uneducated moron.

Good point. I will write up some text for the FAQ and post it.  I will
emphasize that there is great variation in the quality of PC-compatible
hardware, and mention a few specific examples.

---

From: [email-masked] (Jim Seymour)
Date: Tue, 6 Jul 2004 12:11:28 -0400 (EDT)

[snip] 
> line hardware.
[snip]

There's a difference between "not knowing your should" and "not being
able to do it."  I addressed cluelessness, not economic ability.

[snip]
> helpful.  

"Helpful?"  Hmmm...  You know, recently there was a "consumer alert"
item during one of the major U.S. TV networks' news shows.  It seems
that all kinds of counterfeit products are making it into the stores
here.  Even after-market automotive brake pads made of nothing but
compressed and painted/dyed vegetable matter.  Needless to say, using
brake bads made of proper brake pad material is "helpful." ;)

Somebody's *ability* to employ proper hardware does not affect whether 
or not it's advisable to do so.  This isn't a "rich" vs. "poor" issue.
If you cannot do it, you cannot do it, and that's that.  But that does
not lessen the importance of knowing you should.

>           But don't make that sound like if you don't have it, it's the

If your db destructs due to an undetected RAM problem (undetected
because your computer has not even parity error detection) and the data
in that db is "life or death" critical to your business, it might well
be the end of the world as far as your business is concerned.

Jim

---

From: Anony Mous <[email-masked]>
Date: Tue, 06 Jul 2004 11:25:07 -0600

Scary if the person has a DBA background.  But there are lots of people,
like myself, who find themselves doing this sort of work and do not have the
formal training (small business, small db, but still an important part of
the business IS).  Now, I know my limits and I would not take on the task of
administering a high-volume, mission-critical DB.  Nonetheless, there are
many tips, tricks, and gotcha's that I have yet to learn.

I welcome an FAQ on this subject and genuinely appreciate the efforts of
those who create it.  It will make me a better "wanna-be"!

-----Original Message-----
From: Jim Seymour [mailto:[email-masked]] 
Sent: July 6, 2004 6:32 AM
To: [email-masked]
Subject: Re: Do we need more emphasis on backup?

[snip]
[snip]
[snip]

Jim

---

From: Christopher Petrilli <[email-masked]>
Date: Tue, 6 Jul 2004 16:48:14 -0400

> those who create it.  It will make me a better "wanna-be"!

One thing this strikes me as is an opportunity to help shape the
perception of DBAs and "real" databases versus "toy" databases. 
Various products have gotten a lot of people out there running
databases with lots of mission criticle data in them, but they're
trusting a lot of slip-shod software and not putting into place any
proceedures to deal with backups and other things.

For example, I recently dealt with a customer who was using another
database, and had been running backups religiously for the past year.
The problem? The backups were of a live database, and it turned out
when they tried to restore, it was unable to get the tables back into
a position where they were usable without dropping a huge amount of
data from the tables, and then rebuilding indexes. A pain. All because
they'd not taken the right steps.

If the PostgreSQL community can help put together a "best practices"
guide, that will help all database people, but it will also help show
the PostgreSQL community as being more "serious." I'm more than happy
to contribute practices, and things, as well as help edit other
people's work, but I can't "lead" such an effort right now.

Chris
-- 
| Christopher Petrilli
| [email-masked]

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 7 Jul 2004 00:01:24 +0200

Bruce Momjian wrote:
> Should I add an FAQ discussing hardware selection and the importance
> of reliable hardware?

I'm not a fan of having N "FAQs" for N topics.  Just put it into the 
documentation.

---

From: [email-masked] (Jim Seymour)
Date: Tue, 6 Jul 2004 18:36:18 -0400 (EDT)

Christopher Petrilli <[email-masked]> wrote:
[snip]
> the PostgreSQL community as being more "serious." 
[snip]

*That* would be most welcome!

Jim

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 11 Jul 2004 20:19:22 -0400 (EDT)

New FAQ added:

    <H4><A name="3.11">3.11</A>) What computer hardware should I use?</H4>

    <P>Because PC hardware is mostly compatible, people tend to believe that
    all PC hardware is of equal quality.  It is not.  ECC RAM, SCSI, and
    quality motherboards are more reliable and have better performance than
    less expensive hardware.  PostgreSQL will run on almost any hardware,
    but if you are building a server where reliability and performance are
    concerns, it is wise to research your hardware options thoroughly.  Our
    email lists can be used to discuss hardware options and tradeoffs.</P>

Adjustments?

---------------------------------------------------------------------------

Jim Seymour wrote:
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

---

From: [email-masked] (Jim Seymour)
Date: Sun, 11 Jul 2004 20:54:34 -0400 (EDT)

>     email lists can be used to discuss hardware options and tradeoffs.</P>

Looks good to me.  Succinct and non-judgemental.  Well done!

Oh, if you're accepting punctuation nits ;), in most cases, the comma
should come after "but," not before it.  So your sentence should read
"PostgreSQL will run on almost any hardware but, if you are..."

[snip]
> > batter-backed write cache, tell me "Just use a UPS.")

Also desirable are a quality UPS, with monitoring on the server for
graceful shutdown of the server on battery exhaustion, and redundant
power supplies.

Jim

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 11 Jul 2004 21:09:25 -0400 (EDT)

OK, comma moved.  I didn't add those other items because I didn't want
the list to be anything near a complete list.

Jim Seymour wrote:
> TIP 7: don't forget to increase your free space map settings
>

---

From: Doug McNaught <[email-masked]>
Date: Sun, 11 Jul 2004 21:10:09 -0400

> "PostgreSQL will run on almost any hardware but, if you are..."

Wrong.  :)

You are sentenced to go read Strunk and White again.

-Doug

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 11 Jul 2004 21:37:07 -0400 (EDT)

> You are sentenced to go read Strunk and White again.

Text adjusted:

    <P>Because PC hardware is mostly compatible, people tend to believe that
    all PC hardware is of equal quality.  It is not.  ECC RAM, SCSI, and
    quality motherboards are more reliable and have better performance than
    less expensive hardware.  PostgreSQL will run on almost any hardware,
    but if you are building a server where you are concerned about reliability
    and performance it is wise to research your hardware options thoroughly.  Our
    email lists can be used to discuss hardware options and tradeoffs.</P>

---

From: "Scott Marlowe" <[email-masked]>
Date: Sun, 11 Jul 2004 20:11:25 -0600

>     email lists can be used to discuss hardware options and tradeoffs.</P>

Further, a system should be burned in and have all the components 
thoroughly tested.  Brand new computers can, and often do, have broken
or misbehaving hardware.  While relatively simple tasks like installing
an operating system or browsing the web only use a small portion of a
computer's resources, running a database relies on all of it working
properly.  Basically, it all boils down to to this, if you're not sure
of your hardware, you shouldn't be building a database server with it,
and you can't be sure of your hardware without testing it.

---

From: Joel <[email-masked]>
Date: Mon, 12 Jul 2004 12:02:03 +0900

>     ...  PostgreSQL will run on almost any hardware, 
>     and performance it is wise to research your hardware options thoroughly.  ...

That's okay, of course. But I'll suggest the following as food for
thought

          PostgreSQL will run on almost any hardware, but, 
          if you are building a server where you are concerned 
          about reliability and performance, it is wise to 
          research your hardware options thoroughly.

or

          PostgreSQL will run on almost any hardware, but, 
          especially where reliability and performance are
          required, it is wise to research your 
          hardware options thoroughly.

(where/when)

-- 
Joel <[email-masked]>

---

From: [email-masked] (Jim Seymour)
Date: Sun, 11 Jul 2004 23:32:18 -0400 (EDT)

> You are sentenced to go read Strunk and White again.

I stand corrected.  I always used to put the comma before "but," but
changed after reading somewhere, or thinking I had, that was incorrect
for certain sentence structures.

Neither of my style guides supports what I said earlier, so I cannot
imagine whence I got that idea.

Jim

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 11 Jul 2004 23:59:52 -0400 (EDT)

>           hardware options thoroughly.

New text:

    <P>Because PC hardware is mostly compatible, people tend to believe that
    all PC hardware is of equal quality.  It is not.  ECC RAM, SCSI, and
    quality motherboards are more reliable and have better performance than
    less expensive hardware.  PostgreSQL will run on almost any hardware,
    but if reliability and performance are important it is wise to
    research your hardware options thoroughly.  Our email lists can be used
    to discuss hardware options and tradeoffs.</P>

---

From: Joel <[email-masked]>
Date: Mon, 12 Jul 2004 14:12:57 +0900

>     to discuss hardware options and tradeoffs.</P>

I like that, too.

(And now, after a break with real-world grammars, back to the cold,
cruel world of mechanical parsers. ;)

-- 
Joel <[email-masked]>

---

From: Richard A Lough <[email-masked]>
Date: Mon, 12 Jul 2004 07:31:08 +0000

> New FAQ added:

I would suggest the following: 

     <P>PostgreSQL will run on almost any hardware, but if you are building 
     a server where reliability and performance are concerns, it is wise to 
     research your hardware options thoroughly. ECC RAM, SCSI, and quality 
     motherboards have proven to be more reliable and to offer better 
     performance than less expensive hardware. Where cost of hardware is 
     your primary concern, proven and frequent backups may provide 
     reassurance that valued information can be retreived after component 
     or system failure. With inexpensive hardware there is greater risk of 
     introducing subtle errors which degrade the credibility of your data. 
     Our email lists can be used to discuss hardware options and tradeoffs.
     </P>
 
---------------------------------------//--------------------------------

IMHO the guy with cheapo hardware is the one with most need of advice. It
may be prudent to put together some figures to give some notion of a cost 
effective backup for him. 

Following on from this, I hear that serial ATA drives are likely to 
replace IDE drives and will offer performance similar to SCSI. 

But, I am still a novice in database matters, just my 2d.

HTH

Richard A Lough