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
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): 60So 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?
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.
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
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