Thread: newbie qs; fixing and checking databases

newbie qs; fixing and checking databases

From
Rusty Wright
Date:
Are there commands provided with postgresql for checking the databases
and also for repairing them?  I'd like to be able to run something to
check the databases before our nightly backups.

Thanks

Re: newbie qs; fixing and checking databases

From
Bruce Momjian
Date:
Rusty Wright wrote:
> Are there commands provided with postgresql for checking the databases
> and also for repairing them?  I'd like to be able to run something to
> check the databases before our nightly backups.

We don't have checks because thing rarely break.  You can do a VACUUM,
which does some elementary checking.

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

Re: newbie qs; fixing and checking databases

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Rusty Wright wrote:
>> Are there commands provided with postgresql for checking the databases
>> and also for repairing them?

> We don't have checks because thing rarely break.

Or at least: there are seldom any programmable-in-advance patterns to
our failures.  We prefer to put our efforts into preventing problems
rather than cleaning up after 'em...

            regards, tom lane

Re: newbie qs; fixing and checking databases

From
Rusty Wright
Date:
It seems to me that if the machine crashes in the middle of a
transaction that there will be or is a very high chance of database
corruption.  I don't know if and how postgresql would detect and
handle that after the system reboots.  We are running mysql here and
haven't had any data integrity problems.

Re: newbie qs; fixing and checking databases

From
Bruce Momjian
Date:
Rusty Wright wrote:
> It seems to me that if the machine crashes in the middle of a
> transaction that there will be or is a very high chance of database
> corruption.  I don't know if and how postgresql would detect and
> handle that after the system reboots.  We are running mysql here and
> haven't had any data integrity problems.

No chance of corruption.  On restart, the WAL file will bring the system
back to the state before the transaction started.  In fact, the WAL
doesn't even have to be used because the transaction isn't marked as
completed yet.

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

Re: newbie qs; fixing and checking databases

From
"ken"
Date:
Did somebody just yell "fire" in a crowded theatre?

Ken

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Wednesday, October 16, 2002 12:54 PM
To: Rusty Wright
Cc: tgl@sss.pgh.pa.us; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] newbie qs; fixing and checking databases


Rusty Wright wrote:
> It seems to me that if the machine crashes in the middle of a
> transaction that there will be or is a very high chance of database
> corruption.  I don't know if and how postgresql would detect and
> handle that after the system reboots.  We are running mysql here and
> haven't had any data integrity problems.

No chance of corruption.  On restart, the WAL file will bring the system
back to the state before the transaction started.  In fact, the WAL
doesn't even have to be used because the transaction isn't marked as
completed yet.

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

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


Re: newbie qs; fixing and checking databases

From
Rusty Wright
Date:
Some of us are just paranoid and like to be prepared when there really
is a fire.

   From: "ken" <kenzo@kennethambrose.com>
   To: "Bruce Momjian" <pgman@candle.pha.pa.us>,
      "Rusty Wright" <rusty@socrates.Berkeley.EDU>
   Cc: <tgl@sss.pgh.pa.us>, <pgsql-admin@postgresql.org>
   Subject: RE: [ADMIN] newbie qs; fixing and checking databases
   Date: Wed, 16 Oct 2002 13:04:57 -0700

   Did somebody just yell "fire" in a crowded theatre?

   Ken

Re: newbie qs; fixing and checking databases

From
Vincent Janelle
Date:
The rest of us read the documentation..

On Wed, 16 Oct 2002 13:12:07 -0700 (PDT)
Rusty Wright <rusty@socrates.Berkeley.EDU> wrote:

> Some of us are just paranoid and like to be prepared when there really
> is a fire.
>
>    From: "ken" <kenzo@kennethambrose.com>
>    To: "Bruce Momjian" <pgman@candle.pha.pa.us>,
>       "Rusty Wright" <rusty@socrates.Berkeley.EDU>
>    Cc: <tgl@sss.pgh.pa.us>, <pgsql-admin@postgresql.org>
>    Subject: RE: [ADMIN] newbie qs; fixing and checking databases
>    Date: Wed, 16 Oct 2002 13:04:57 -0700
>
>    Did somebody just yell "fire" in a crowded theatre?
>
>    Ken

Re: newbie qs; fixing and checking databases

From
Rusty Wright
Date:
Ok, I'll bite; where is it documented what postgresql does after an
unplanned power outage or system shutdown?  My question came about
because I was searching for documentation on some sort of db check
utility and didn't find anything.

   Date: Wed, 16 Oct 2002 16:40:21 -0400
   From: Vincent Janelle <random@goblinstudios.com>
   To: Rusty Wright <rusty@socrates.Berkeley.EDU>
   Cc: pgsql-admin@postgresql.org
   Subject: Re: [ADMIN] newbie qs; fixing and checking databases

   The rest of us read the documentation..

   On Wed, 16 Oct 2002 13:12:07 -0700 (PDT)
   Rusty Wright <rusty@socrates.Berkeley.EDU> wrote:

   > Some of us are just paranoid and like to be prepared when there really
   > is a fire.
   >
   >    From: "ken" <kenzo@kennethambrose.com>
   >    To: "Bruce Momjian" <pgman@candle.pha.pa.us>,
   >       "Rusty Wright" <rusty@socrates.Berkeley.EDU>
   >    Cc: <tgl@sss.pgh.pa.us>, <pgsql-admin@postgresql.org>
   >    Subject: RE: [ADMIN] newbie qs; fixing and checking databases
   >    Date: Wed, 16 Oct 2002 13:04:57 -0700
   >
   >    Did somebody just yell "fire" in a crowded theatre?
   >
   >    Ken

Re: newbie qs; fixing and checking databases

From
Vincent Janelle
Date:
http://www.postgresql.org/idocs/index.php?wal.html

On Wed, 16 Oct 2002 14:51:00 -0700 (PDT)
Rusty Wright <rusty@socrates.Berkeley.EDU> wrote:

> Ok, I'll bite; where is it documented what postgresql does after an
> unplanned power outage or system shutdown?  My question came about
> because I was searching for documentation on some sort of db check
> utility and didn't find anything.
>
>    Date: Wed, 16 Oct 2002 16:40:21 -0400
>    From: Vincent Janelle <random@goblinstudios.com>
>    To: Rusty Wright <rusty@socrates.Berkeley.EDU>
>    Cc: pgsql-admin@postgresql.org
>    Subject: Re: [ADMIN] newbie qs; fixing and checking databases
>
>    The rest of us read the documentation..
>

Re: newbie qs; fixing and checking databases

From
"Nigel J. Andrews"
Date:
On Tue, 15 Oct 2002, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Rusty Wright wrote:
> >> Are there commands provided with postgresql for checking the databases
> >> and also for repairing them?
>
> > We don't have checks because thing rarely break.
>
> Or at least: there are seldom any programmable-in-advance patterns to
> our failures.  We prefer to put our efforts into preventing problems
> rather than cleaning up after 'em...

Does this question mean that there known, detectable data integrity (not
referential integrity) problems with MySQL?

Wondering because it seems an odd question to me and it could be a factor in a
descision, although the number of MySQL installations out there one would have
to there is no problem or all those web sites surely would be terribly unhappy.

[Note, I have no intention of starting a DB war this is a genuine interest from
a complete MySQL newbie who's got to look at it on a best tool for the project
basis.]

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants


Re: newbie qs; fixing and checking databases

From
Shawn Michael
Date:
On 2002.10.16 00:34 Nigel J. Andrews wrote:
> On Tue, 15 Oct 2002, Tom Lane wrote:
>
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Rusty Wright wrote:
> > >> Are there commands provided with postgresql for checking the databases
> > >> and also for repairing them?
> >
> > > We don't have checks because thing rarely break.
> >
> > Or at least: there are seldom any programmable-in-advance patterns to
> > our failures.  We prefer to put our efforts into preventing problems
> > rather than cleaning up after 'em...
>
> Does this question mean that there known, detectable data integrity (not
> referential integrity) problems with MySQL?


With the isam and myisam table formats you are subject to corruption due to sudden crashes of the database.  From my
limitedunderstanding of low level database internals, the lack of transactions is the major cause of the need for the
toolsthat Rusty was asking about.   

With PostgreSQL if postmaster cores mid transaction, upon startup because that transaction was not commited,
effectivelythe transaction "never happened"... the corrupt data is just thrown away.  With MySQL it is different,  the
transactionwill have been half written to disk when the core happened and that row will be corrupt within the database
files. Upon startup of mysqld no rollback is performed and the data remains in a half written state (corrupt).  The
tools(isamchk, myisamchk and CHECK TABLE table;) exist to fix the corruption (read: delete the row) in that specific
table,at the possible cost of referential integrity. 


>
> Wondering because it seems an odd question to me and it could be a factor in a
> descision, although the number of MySQL installations out there one would have
> to there is no problem or all those web sites surely would be terribly unhappy.
>


Most people don't notice these problems with MySQL because there are not that many sites out there that are "high
volume"enough to have MySQL core in one thread while another is performing a write.  When these cores do happen the
startupscript for mysql (safe_mysqld) is basically a big while true ; do mysqld ; done loop,  total down time for these
peopleis typically less than a second and only 1 web page gets an error.  Also as part of the startup otions for mysql
is/was"auto repair", which I think is effectively some form of "CHECK TABLE table; if corrupt REPAIR TABLE table". 

Shawn