Help - postgresql 7.0 and cannot read block 7959 of pg_log

[Lukasz Piskorz]

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




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Lukasz Piskorz
When I check all tables only one (very important in my database) is
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.

Post by Lukasz Piskorz
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.

Post by Lukasz Piskorz
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

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

[Jeremy Smith]

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

[Matt Clark]

BEGIN;
DELETE FROM mytable;
!!! OOOPS !!!!
ROLLBACK;

-----Original Message-----
Sent: 20 February 2004 06:06
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Dave Ewart]

Post by Jeremy Smith
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
***@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 ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Yuji Shinozaki]

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

Post by Dave Ewart

Post by Jeremy Smith
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
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
message can get through to the mailing list cleanly

Yuji Shinozaki Computer Systems Senior Engineer
***@virginia.edu Advanced Technologies Group
[phone] Information Technology & Communication
http://www.people.virginia.edu/~ys2n University of Virginia


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruce Momjian]

Post by Yuji Shinozaki
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.
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.

[Jeremy Smith]

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-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org]On Behalf Of Bruce Momjian
Sent: Friday, February 20, 2004 1:27 PM
To: Yuji Shinozaki
Cc: Dave Ewart; pgsql-***@postgresql.org
Subject: Re: [ADMIN] "DELETE FROM" protection

Post by Yuji Shinozaki
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.
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
***@candle.pha.pa.us | [phone]
+ 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 ***@postgresql.org)



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