Thread: Help - postgresql 7.0 and cannot read block 7959 of pg_log

Help - postgresql 7.0 and cannot read block 7959 of pg_log

From
"Lukasz Piskorz"
Date:
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




Re: Help - postgresql 7.0 and cannot read block 7959 of pg_log

From
Tom Lane
Date:
"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

"DELETE FROM" protection

From
"Jeremy Smith"
Date:
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


Re: "DELETE FROM" protection

From
"Matt Clark"
Date:
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


Re: "DELETE FROM" protection

From
Dave Ewart
Date:
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


Re: "DELETE FROM" protection

From
Yuji Shinozaki
Date:
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


Re: "DELETE FROM" protection

From
Bruce Momjian
Date:
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

Re: "DELETE FROM" protection

From
"Jeremy Smith"
Date:
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)