Обсуждение: recover as much as possible (xlog flush request not satisfied)

Wim Goedertier <Wim_Goedertier@symantec.com> writes:
> I have a postgres db version 8.2.15 (Yes, I know it's rather old version).
> After correcting some disk and file system problems the postgres table seems to be corrupt, returning:

> ERROR:  xlog flush request B67/44479CB8 is not satisfied --- flushed only to B67/429EB150

> CONTEXT:  writing block 33652 of relation 1663/18776/21757

> on a simple select statement.
> What is the best way to get past this?

Well, *first*, stop the database and take a filesystem-level backup of
the $PGDATA directory tree.  That will at least let you get back to
where you are now if experimentation makes it worse.

You could suppress this particular class of complaints by using
pg_resetxlog to advance the WAL endpoint past whatever's in the
database.  That won't do anything to fix corruption, it'll just
silence this particular consistency check.  But it might be
enough to let you run pg_dump.

            regards, tom lane

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

Wim Goedertier <Wim_Goedertier@symantec.com> writes:
> 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.

Great!  But a word of warning: personally, I'd have re-initdb'd the
whole installation.  When you have a problem like this, there's no good
reason to assume that only the active database is broken.

            regards, tom lane