I've had a couple of Postgresql corruption problems I've been working through lately so I figured I'd share some method so that you don't have to go through the trial and error process I did in getting to the bottom of it. The first thing you have to do is figure out exactly where the corruption is, and what sort of corruption you have. Most of the time corruption is going to be do to an errant action by the admin, or hardware conditions, or a corrupt index in your table.
So first figure out where in your table the corruption is. You can do this using a select query and using the ctid value to figure out where it begins. You will want to set the following two options when doing this:
=>\set FETCH_COUNT 1
=>\pset pager off
Next we will want to run the query itself a sample of this would be as follows:
=>select ctid, * from table;
This will scroll and scroll until you hit the nasty portion where it will crash the instance. Once this occurs you can start to narrow down the affected area. You may wish to attempt a reindex first before you continue on, to make sure that the corruption just isn't a bad index.
=>reindex table tablename
The next step if the reindex doesn't work it is likely you'll need to 'cut out the bruise'. The first thing you can try is a query to delete the values based on the ctid with something like the following:
=> delete from table where ctid >= '(12312,0)' and ctid < '(12313,0)'
This may fail with an error message similar to:
ERROR: could not open segment [n] of relation [x]/[y]/[z] (target block [b]): No such file or directory
If that occurs we are going to have to zero out the block ourselves. To do this we can use the dd utility.
First lets take a look at the file in question which we can do using a command similar to:
dd if=data/postgresql/base/x/y bs=8192 skip=17897 count=1 | hexdump -C | less
The values in 'if=<value>' are going to match up as base/y/z (with the y and z being from the error message above). The value in 'skip=<number>' is going to match the first value in the page ctid we discovered as corrupt earlier. You will likely see some junk data in here such as a file name written in or a time stamp or what have you. To fix this we are merely going to write an 8k block in at that page as it would be rather unlikely that we could recover rows at this point. To do this we'll use the following:
dd if=/dev/zero of=data/postgresql/base/x/y bs=8192 seek=17897 count=1 conv=notrunc
And now if you run a query looking in that page you'll find a count of zero rows. Now you should be able to do a successful pg_dump.
Ryan Koch
3X Systems
ryan.koch@3x.com
So first figure out where in your table the corruption is. You can do this using a select query and using the ctid value to figure out where it begins. You will want to set the following two options when doing this:
=>\set FETCH_COUNT 1
=>\pset pager off
Next we will want to run the query itself a sample of this would be as follows:
=>select ctid, * from table;
This will scroll and scroll until you hit the nasty portion where it will crash the instance. Once this occurs you can start to narrow down the affected area. You may wish to attempt a reindex first before you continue on, to make sure that the corruption just isn't a bad index.
=>reindex table tablename
The next step if the reindex doesn't work it is likely you'll need to 'cut out the bruise'. The first thing you can try is a query to delete the values based on the ctid with something like the following:
=> delete from table where ctid >= '(12312,0)' and ctid < '(12313,0)'
This may fail with an error message similar to:
ERROR: could not open segment [n] of relation [x]/[y]/[z] (target block [b]): No such file or directory
If that occurs we are going to have to zero out the block ourselves. To do this we can use the dd utility.
First lets take a look at the file in question which we can do using a command similar to:
dd if=data/postgresql/base/x/y bs=8192 skip=17897 count=1 | hexdump -C | less
The values in 'if=<value>' are going to match up as base/y/z (with the y and z being from the error message above). The value in 'skip=<number>' is going to match the first value in the page ctid we discovered as corrupt earlier. You will likely see some junk data in here such as a file name written in or a time stamp or what have you. To fix this we are merely going to write an 8k block in at that page as it would be rather unlikely that we could recover rows at this point. To do this we'll use the following:
dd if=/dev/zero of=data/postgresql/base/x/y bs=8192 seek=17897 count=1 conv=notrunc
And now if you run a query looking in that page you'll find a count of zero rows. Now you should be able to do a successful pg_dump.
Ryan Koch
3X Systems
ryan.koch@3x.com
No comments:
Post a Comment