Thread: newbie qs; fixing and checking databases
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
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
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
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.
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
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
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
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
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
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.. >
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
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