how do I clear a page, or set an item in a page to 'free'?

Started by Eric Paruselabout 21 years ago9 messagesgeneral
Jump to latest
#1Eric Parusel
lists@globalrelay.net

I've got a mucked up page in my db, and I can't complete a database dump
until a particular page is fixed/removed from the table. :(

Could someone let me know the proper way to go about making a change
such as this?:

1) Clear the page
or
2) Set the bad items in the page to 'free'

I can see the page with pg_filedump. (I know the page number)
Either method would suffice, I just need to get the job done :/

Thanks for any help you can offer,
Eric

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Eric Parusel (#1)
Re: how do I clear a page, or set an item in a page to 'free'?

Eric Parusel <lists@globalrelay.net> writes:

I've got a mucked up page in my db, and I can't complete a database dump
until a particular page is fixed/removed from the table. :(

Could someone let me know the proper way to go about making a change
such as this?:

1) Clear the page
or
2) Set the bad items in the page to 'free'

Zeroing the page is the most painless way. dd from /dev/zero will get
it done --- but note that you have to shut down the postmaster meanwhile
to ensure Postgres will see your change.

regards, tom lane

#3Eric Parusel
lists@globalrelay.net
In reply to: Tom Lane (#2)
Re: how do I clear a page, or set an item in a page to

Tom Lane wrote:

Eric Parusel <lists@globalrelay.net> writes:

I've got a mucked up page in my db, and I can't complete a database dump
until a particular page is fixed/removed from the table. :(

Zeroing the page is the most painless way. dd from /dev/zero will get
it done --- but note that you have to shut down the postmaster meanwhile
to ensure Postgres will see your change.

ok, I can dd, if=/dev/zero of=base/dbnum/tablenum, but how do I figure
how where and how much to write over?
Is there an easy to see or calculate offset value for a particular page
number? (28393)

from pg_filedump:
Block 28393 ********************************************************
<Header> -----
Block Offset: 0x0ddd2000 Offsets: Lower 56 (0x0038)
Block: Size 8192 Version 2 Upper 1064 (0x0428)
LSN: logid 242 recoff 0x9387bd78 Special 8192 (0x2000)
Items: 9 Free Space: 1008
Length (including item array): 60

So I could take the block offset, convert it from hex (to 232595456...
oh, /8192 = 28393 :) ), and the block size (which is default: 8192) to do:

dd if=/dev/zero of=base/17760/18804 obs=8192 seek=28393
?

Should I, or do I need to REINDEX after this?

If you could confirm that I answered my own question, that would be
great :)

Thanks,
Eric

#4Scott Marlowe
smarlowe@g2switchworks.com
In reply to: Eric Parusel (#3)
Re: how do I clear a page, or set an item in a page to

Wouldn't zero_damaged_pages help here?

http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-DEVELOPER

Show quoted text

On Mon, 2005-03-21 at 13:28, Eric Parusel wrote:

Tom Lane wrote:

Eric Parusel <lists@globalrelay.net> writes:

I've got a mucked up page in my db, and I can't complete a database dump
until a particular page is fixed/removed from the table. :(

Zeroing the page is the most painless way. dd from /dev/zero will get
it done --- but note that you have to shut down the postmaster meanwhile
to ensure Postgres will see your change.

ok, I can dd, if=/dev/zero of=base/dbnum/tablenum, but how do I figure
how where and how much to write over?
Is there an easy to see or calculate offset value for a particular page
number? (28393)

from pg_filedump:
Block 28393 ********************************************************
<Header> -----
Block Offset: 0x0ddd2000 Offsets: Lower 56 (0x0038)
Block: Size 8192 Version 2 Upper 1064 (0x0428)
LSN: logid 242 recoff 0x9387bd78 Special 8192 (0x2000)
Items: 9 Free Space: 1008
Length (including item array): 60

So I could take the block offset, convert it from hex (to 232595456...
oh, /8192 = 28393 :) ), and the block size (which is default: 8192) to do:

dd if=/dev/zero of=base/17760/18804 obs=8192 seek=28393
?

Should I, or do I need to REINDEX after this?

If you could confirm that I answered my own question, that would be
great :)

Thanks,
Eric

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

http://archives.postgresql.org

#5Tom Lane
tgl@sss.pgh.pa.us
In reply to: Eric Parusel (#3)
Re: how do I clear a page, or set an item in a page to

Eric Parusel <lists@globalrelay.net> writes:

ok, I can dd, if=/dev/zero of=base/dbnum/tablenum, but how do I figure
how where and how much to write over?
Is there an easy to see or calculate offset value for a particular page
number? (28393)

dd bs=8k seek=28393 count=1

regards, tom lane

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Scott Marlowe (#4)
Re: how do I clear a page, or set an item in a page to

Scott Marlowe <smarlowe@g2switchworks.com> writes:

Wouldn't zero_damaged_pages help here?

Only if there's detectable corruption in the page header, which there
seems not to be.

regards, tom lane

#7Scott Marlowe
smarlowe@g2switchworks.com
In reply to: Tom Lane (#6)
Re: how do I clear a page, or set an item in a page to

ahh, ok. I just know I'm much more nervous about zeroing stuff by hand
than letting the backend do it for me.

Show quoted text

On Mon, 2005-03-21 at 13:54, Tom Lane wrote:

Scott Marlowe <smarlowe@g2switchworks.com> writes:

Wouldn't zero_damaged_pages help here?

Only if there's detectable corruption in the page header, which there
seems not to be.

regards, tom lane

#8Tom Lane
tgl@sss.pgh.pa.us
In reply to: Scott Marlowe (#7)
Re: how do I clear a page, or set an item in a page to

Scott Marlowe <smarlowe@g2switchworks.com> writes:

ahh, ok. I just know I'm much more nervous about zeroing stuff by hand
than letting the backend do it for me.

Well, I certainly hope Eric is gonna save aside a copy of the file
(if not the whole database) before he hacks it ;-)

BTW, I missed the point about REINDEX. Yeah, that's probably a good
idea to get rid of any index entries pointing at the removed rows.

regards, tom lane

#9Eric Parusel
lists@globalrelay.net
In reply to: Tom Lane (#5)
Re: how do I clear a page, or set an item in a page to

Tom Lane wrote:

Eric Parusel <lists@globalrelay.net> writes:

ok, I can dd, if=/dev/zero of=base/dbnum/tablenum, but how do I figure
how where and how much to write over?
Is there an easy to see or calculate offset value for a particular page
number? (28393)

dd bs=8k seek=28393 count=1

shutdown postgres
cp base/dbnum/tablenum* /somewhere/else
dd if=/dev/zero of=base/dbnum/tablenum bs=8k seek=28393 count=1
startup postgres

It worked...! select count(*) from table; now works fine!
I'm currently doing a vacuum then a db dump to confirm there's no other
page issues...

Vacuum is aptly reporting:
WARNING: relation "table" page 28393 is uninitialized --- fixing

Thanks for your assistance, hopefully this helps someone else in the
future a little bit.

I don't know why the problem occurred, but I don't think it's realistic
to figure that out easily. (disk, raid, server, os, pgsql, or some
combination!)

Eric