Wim>> I have a postgres db version 8.2.15 (Yes, I know it's rather old version).
Wim>> After correcting some disk and file system problems the postgres table seems to be corrupt, returning:
Wim>>
Wim>> ERROR: xlog flush request B67/44479CB8 is not satisfied --- flushed only to B67/429EB150
Wim>> CONTEXT: writing block 33652 of relation 1663/18776/21757
Wim>>
Wim>> on a simple select statement.
Wim>> What is the best way to get past this?
Tom> Well, *first*, stop the database and take a filesystem-level backup of
Tom> the $PGDATA directory tree. That will at least let you get back to
Tom> where you are now if experimentation makes it worse.
Tom> You could suppress this particular class of complaints by using
Tom> pg_resetxlog to advance the WAL endpoint past whatever's in the
Tom> database. That won't do anything to fix corruption, it'll just
Tom> silence this particular consistency check. But it might be
Tom> enough to let you run pg_dump.
Thx for the quick answer.
Copy of PGDATA directory was already taken
After studying http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html
I ran:
su <db-user> -c 'pg_resetxlog -f -x 0x9A00000 -m 0x10000 -O 0x10000 -l 0x1,0xB67,0x58 <PGDATA-directory>'
and yes, it worked perfectly!
After that I could pg_dump, drop database, create database and import the dump.
Now, I'm verifying the consistency on the level of my application.
Thx, again, Tom.
For other people, looking for info about recovering postgresql DBs:
I first did:
su <db-user> -c 'pg_resetxlog -n <PGDATA-directory>'
That returned:
Current log file ID: 2919 (= 0xB67)
Next log file segment: 67 (= 0x43)
Latest checkpoint's TimeLineID: 1 (= 0x1)
Latest checkpoint's NextXID: 0/161392765 -> (= 0x99EA87D)
Latest checkpoint's NextOID: 136502229
Latest checkpoint's NextMultiXactId: 4129 (= 0x1021)
Latest checkpoint's NextMultiOffset: 16888 (= 0x41F8)
Then I checked
$PGDATA/pg_xlog/ -> 'highest' filename: 0000000100000B6700000057
$PGDATA/pg_clog/ -> 'highest' filename: 0099
$PGDATA/pg_multixact/offsets/ -> 'highest' filename: 0000
$PGDATA/pg_multixact/members/ -> 'highest' filename: 0000
No more magic needed, then what is under:
http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html
For completeness: next I did:
pg_dump -U <db-user> <my-db-name> >dump.sql
psql -U <db-user> template0 -c "drop database <my-db-name>"
psql -U <db-user> template0 -c "create database <my-db-name>"
psql -U <db-user> <my-db-name> -c -f dump.sql