Thread: Help - postgresql 7.0 and cannot read block 7959 of pg_log
Hello,
I need very fast help.
When I worked in postgresql 7.0 I saw message:
database=>NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend died
abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am going to terminate
your database system connection and exit.
Please reconnect to the database system and repeat your query.
The connection to the server was lost. Attempting reset: Succeeded.
When I check all tables only one (very important in my database) is
corrupted:
database=>select * from table;
ERROR: cannot read block 7959 of pg_log: Success
Could you help me? What can I do? Can I remove this table and restore from
backup? What do you think about software
http://svana.org/kleptog/pgsql/pgfsck.html ?
Best,
Lukas
"Lukasz Piskorz" <lpiskorz@ists.pwr.wroc.pl> writes: > When I check all tables only one (very important in my database) is > corrupted: > database=>select * from table; > ERROR: cannot read block 7959 of pg_log: Success This looks like something is trying to access the commit status of an out-of-range transaction number, which is usually the first failure you get when a row header has been corrupted. (In more recent PG versions, you'd get a complaint about trying to access a nonexistent pg_clog file.) The odds are that the corruption has damaged an entire page of that table. I'm afraid there's no question that you've lost some data. You might be able to partially recover by zeroing out the broken page --- that would at least let you dump the data in the remaining pages of the table. See the mail list archives concerning methods for locating data corruption more precisely. > Could you help me? What can I do? Can I remove this table and restore from > backup? If you have a recent backup that might be the way to go. > What do you think about software > http://svana.org/kleptog/pgsql/pgfsck.html ? I dunno if that works on versions as old as 7.0. One thing you should definitely do as soon as you've extracted as much data as you can is to update to some newer version. 7.0.* was a long time and a lot of bug fixes ago. regards, tom lane
This may be an all-time idiotic question, but when I used phpmysql, when I would type in a "DELETE FROM" query in the SQL window, it would make me confirm it before I allowed it to go through. I don't think in all of the presumably thousands of times that I used it that I ever canceled out of the statement, but I always liked that it is there. So now with pgsql, when I am typing "DELETE FROM...." until I get to the "WHERE" part of the statement, I get a little nervous because I know hitting Enter by mistake will wipe out that table. Of course, I have backups, but it is a live site with alot of traffic and I would hate to have to shut things down while I restored the DB. Anyway, this may all seem silly, but is there a setting in pgsql to do this? Thanks, Jeremy
BEGIN; DELETE FROM mytable; !!! OOOPS !!!! ROLLBACK; > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Jeremy Smith > Sent: 20 February 2004 06:06 > To: pgsql-admin@postgresql.org > Subject: [ADMIN] "DELETE FROM" protection > > > > This may be an all-time idiotic question, but when I used phpmysql, when I > would type in a "DELETE FROM" query in the SQL window, it would make me > confirm it before I allowed it to go through. I don't think in all of the > presumably thousands of times that I used it that I ever canceled out of the > statement, but I always liked that it is there. > > So now with pgsql, when I am typing "DELETE FROM...." until I get to the > "WHERE" part of the statement, I get a little nervous because I know hitting > Enter by mistake will wipe out that table. Of course, I have backups, but > it is a live site with alot of traffic and I would hate to have to shut > things down while I restored the DB. > > Anyway, this may all seem silly, but is there a setting in pgsql to do this? > > Thanks, > Jeremy > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
On Friday, 20.02.2004 at 10:12 +0000, Matt Clark wrote: > > So now with pgsql, when I am typing "DELETE FROM...." until I get to > > the "WHERE" part of the statement, I get a little nervous because I > > know hitting Enter by mistake will wipe out that table. [...] How about typing the "WHERE" part of the statement first, then 'left-arrowing' back to the start of the statement and do "DELETE FROM ..." *last*? Dave. -- Dave Ewart Dave.Ewart@cancer.org.uk Computing Manager, Epidemiology Unit, Oxford Cancer Research UK PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370
I've gotten myself into the habit of always writing out a
SELECT ... FROM ... WHERE ...;
first, and then command-line editing it to
DELETE FROM ... WHERE ...;
Putting it in a transaction (BEGIN, COMMIT or ROLLBACK) is probably the
best practice.
yuji
----
On Fri, 20 Feb 2004, Dave Ewart wrote:
> On Friday, 20.02.2004 at 10:12 +0000, Matt Clark wrote:
>
> > > So now with pgsql, when I am typing "DELETE FROM...." until I get to
> > > the "WHERE" part of the statement, I get a little nervous because I
> > > know hitting Enter by mistake will wipe out that table. [...]
>
> How about typing the "WHERE" part of the statement first, then
> 'left-arrowing' back to the start of the statement and do "DELETE FROM
> ..." *last*?
>
> Dave.
> --
> Dave Ewart
> Dave.Ewart@cancer.org.uk
> Computing Manager, Epidemiology Unit, Oxford
> Cancer Research UK
> PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
Yuji Shinozaki Computer Systems Senior Engineer
ys2n@virginia.edu Advanced Technologies Group
(434)924-7171 Information Technology & Communication
http://www.people.virginia.edu/~ys2n University of Virginia
Yuji Shinozaki wrote:
>
> I've gotten myself into the habit of always writing out a
>
> SELECT ... FROM ... WHERE ...;
>
> first, and then command-line editing it to
>
> DELETE FROM ... WHERE ...;
>
> Putting it in a transaction (BEGIN, COMMIT or ROLLBACK) is probably the
> best practice.
I used to do this with Informix before a DELETE:
> SELECT COUNT(*) FROM ... WHERE ...;
^^^^^^^^
and Informix had that "Are your sure" check in dbaccess too, but after I
did the COUNT(*), the prompt was just annoying.
Also, what interfaces allow you to just press ENTER to send a command?
With psql, you have to terminate it with a semicolon or nothing happens.
I think there is justification for an "Are you sure" only if a single
keystroke sends the command.
--
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
Great point Bruce,
I hadn't really thought of the semi-colon as a safety mechanism, but I guess
it is.
Jeremy
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Friday, February 20, 2004 1:27 PM
To: Yuji Shinozaki
Cc: Dave Ewart; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] "DELETE FROM" protection
Yuji Shinozaki wrote:
>
> I've gotten myself into the habit of always writing out a
>
> SELECT ... FROM ... WHERE ...;
>
> first, and then command-line editing it to
>
> DELETE FROM ... WHERE ...;
>
> Putting it in a transaction (BEGIN, COMMIT or ROLLBACK) is probably the
> best practice.
I used to do this with Informix before a DELETE:
> SELECT COUNT(*) FROM ... WHERE ...;
^^^^^^^^
and Informix had that "Are your sure" check in dbaccess too, but after I
did the COUNT(*), the prompt was just annoying.
Also, what interfaces allow you to just press ENTER to send a command?
With psql, you have to terminate it with a semicolon or nothing happens.
I think there is justification for an "Are you sure" only if a single
keystroke sends the command.
--
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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)