newbie qs; fixing and checking databases

newbie qs; fixing and checking databases

Post by Rusty Wrig » Thu, 17 Oct 2002 08:43:34



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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

newbie qs; fixing and checking databases

Post by Bruce Momji » Thu, 17 Oct 2002 10:21:36



> 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

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

newbie qs; fixing and checking databases

Post by Tom La » Thu, 17 Oct 2002 12:18:31




>> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

newbie qs; fixing and checking databases

Post by Rusty Wrig » Fri, 18 Oct 2002 04:36:31


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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

newbie qs; fixing and checking databases

Post by Bruce Momji » Fri, 18 Oct 2002 04:53:56



> 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

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------

 
 
 

newbie qs; fixing and checking databases

Post by ken » Fri, 18 Oct 2002 05:07:05


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

Ken

-----Original Message-----


Sent: Wednesday, October 16, 2002 12:54 PM
To: Rusty Wright

Subject: Re: [ADMIN] newbie qs; fixing and checking databases


> 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

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

newbie qs; fixing and checking databases

Post by Rusty Wrig » Fri, 18 Oct 2002 05:12:19


Some of us are just paranoid and like to be prepared when there really
is a fire.





   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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

newbie qs; fixing and checking databases

Post by Vincent Janel » Fri, 18 Oct 2002 05:38:46


The rest of us read the documentation..

On Wed, 16 Oct 2002 13:12:07 -0700 (PDT)


> Some of us are just paranoid and like to be prepared when there really
> is a fire.





>    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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

newbie qs; fixing and checking databases

Post by Rusty Wrig » Fri, 18 Oct 2002 06:51:04


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



   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)

   > Some of us are just paranoid and like to be prepared when there really
   > is a fire.
   >




   >    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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

newbie qs; fixing and checking databases

Post by Vincent Janel » Fri, 18 Oct 2002 06:57:16


http://www.postgresql.org/idocs/index.php?wal.html

On Wed, 16 Oct 2002 14:51:00 -0700 (PDT)


> 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



>    Subject: Re: [ADMIN] newbie qs; fixing and checking databases

>    The rest of us read the documentation..

---------------------------(end of broadcast)---------------------------

 
 
 

newbie qs; fixing and checking databases

Post by Nigel J. Andrew » Tue, 22 Oct 2002 22:07:53





> >> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

newbie qs; fixing and checking databases

Post by Shawn Micha » Wed, 23 Oct 2002 04:38:17






> > >> 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 limited understanding of low level database internals, the lack of transactions is the major cause of the need for the tools that Rusty was asking about.  

With PostgreSQL if postmaster cores mid transaction, upon startup because that transaction was not commited, effectively the transaction "never happened"... the corrupt data is just thrown away.  With MySQL it is different,  the transaction will 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.

Quote:

> 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 startup script for mysql (safe_mysqld) is basically a big while true ; do mysqld ; done loop,  total down time for these people is 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

---------------------------(end of broadcast)---------------------------

 
 
 

1. newbie qs; examining databases and tables

I'm someone with some mysql experience and need to set up postgres for
a product we're evaluating.  In mysql I can use SHOW DATABASES to list
all of the databases on the system, USE DB1 to switch to one of the
databases, SHOW TABLES to list all of the table names in the database
I'm USEing, and DESCRIBE TABLE1 to list all of the column names, data
types, etc. for a table.

How do I do the equivalent with postgresql?  Are there equivalent SQL
commands?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

2. Updated MDAC RTM 2.6 Drivers?

3. online logical log tapes only partially fill !!

4. Newbie Qs on instances and tablespaces

5. Free Informix for Students??

6. Newbie Question - Check if database exists

7. CA Tech Support

8. NEWBIE question: how do you check if a combination of values exist in a database

9. diffence between Fixed server role and fixed database role

10. nu-B Qs, linux databases, Mac clients

11. 2 fairly simple database Qs

12. Database link'n'joining Qs