Re: FATAL: index contains unexpected zero page at block

[Dmitriy Tyugaev <dtyugaev@xxxxxxxxx>]

Thanks for the reply! Help me please how to use the pg_resetxlog? I read the documentation http://www.postgresql.org/docs/8.4/static/app-pgresetxlog.html,  but did not fully understand how to use it. There are many of values...

$ ./pg_resetxlog -n /opt/postgresql/data

pg_control values:

First log file ID after reset:        9083

First log file segment after reset:   166

pg_control version number:            843

Catalog version number:               200904091

Database system identifier:           5573903541801287615

Latest checkpoint's TimeLineID:       1

Latest checkpoint's NextXID:          0/772374449

Latest checkpoint's NextOID:          316882365

Latest checkpoint's NextMultiXactId:  1

Latest checkpoint's NextMultiOffset:  0

Maximum data alignment:               8

Database block size:                  8192

Blocks per segment of large relation: 131072

WAL block size:                       8192

Bytes per WAL segment:                16777216

Maximum length of identifiers:        64

Maximum columns in an index:          32

Maximum size of a TOAST chunk:        1996

Date/time type storage:               64-bit integers

Float4 argument passing:              by value

Float8 argument passing:              by value

On Thu, Nov 15, 2012 at 2:50 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Dmitriy Tyugaev <dtyugaev@xxxxxxxxx> writes:
> Nov 14 18:24:04 uno postgres84[24208]: [6-1] user=,db= LOG:  redo done at
> 237B/90A1DF98
> Nov 14 18:24:04 uno postgres84[24208]: [7-1] user=,db= LOG:  last completed
> transaction was at log time 2012-11-10 10:26:28.484922+04
> Nov 14 18:24:04 uno postgres84[24208]: [8-1] user=,db= FATAL:  index
> "316879235" contains unexpected zero page at block 264
> Nov 14 18:24:04 uno postgres84[24208]: [8-2] user=,db= HINT:  Please
> REINDEX it.

Hm.  Apparently it's hitting the zero page while trying to clean up an
incomplete index page split after reaching the end of WAL.  This is not
good --- it means your filesystem failed to retain data that it claimed
had been written to disk safely.  You should look into fsync-related
system settings after you get out of the immediate problem.

As far as getting out of the immediate problem is concerned, I think
you have little option except to use pg_resetxlog.  This will mean
the loss of whatever actions it was trying to replay, which may well
mean that you end up with data corruption (not just index corruption).
I'd suggest a dump and reload after you get the server to start.

                        regards, tom lane