Thread: how do I clear a page, or set an item in a page to 'free'?
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
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
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
Wouldn't zero_damaged_pages help here? http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-DEVELOPER 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
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
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
ahh, ok. I just know I'm much more nervous about zeroing stuff by hand than letting the backend do it for me. 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
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
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