Thread: FATAL: index contains unexpected zero page at block
Hi list,
After planned rebooting the server dropped the database server PostgreSQL 8.4
When it start the server writes to the log:
Nov 14 18:24:01 uno postgres84[24207]: [1-1] user=,db= LOG: could not bind IPv6 socket: Cannot assign requested address
Nov 14 18:24:01 uno postgres84[24207]: [1-2] user=,db= HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
Nov 14 18:24:01 uno postgres84[24208]: [2-1] user=,db= LOG: database system was interrupted while in recovery at 2012-11-14 18:18:42 MSK
Nov 14 18:24:01 uno postgres84[24208]: [2-2] user=,db= HINT: This probably means that some data is corrupted and you will have to use the last backup for re
covery.
Nov 14 18:24:01 uno postgres84[24208]: [3-1] user=,db= LOG: database system was not properly shut down; automatic recovery in progress
Nov 14 18:24:01 uno postgres84[24208]: [4-1] user=,db= LOG: redo starts at 237B/78806EC8
Nov 14 18:24:04 uno postgres84[24208]: [5-1] user=,db= LOG: unexpected pageaddr 237B/50A1E000 in log file 9083, segment 144, offset 10608640
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.
Nov 14 18:24:04 uno postgres84[24207]: [2-1] user=,db= LOG: startup process (PID 24208) exited with exit code 1
Nov 14 18:24:04 uno postgres84[24207]: [3-1] user=,db= LOG: aborting startup due to startup process failure
When I trying to run in a single mode:
postgres --single -D /opt/postgresql/data -P postgres
user=,db= FATAL: index "316879235" contains unexpected zero page at block 264
user=,db= HINT: Please REINDEX it.
Please tell me what can I do to recover?
--
Cheers,
Dmitriy
user=,db= FATAL: index "316879235" contains unexpected zero page at block 264user=,db= HINT: Please REINDEX it.Please tell me what can I do to recover?
Did you try re-building the index ? Re-Indexing or re-creating an new index should resolve this.
Regards,
VBN
Dmitriy Tyugaev <dtyugaev@gmail.com> 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
How can I execute rebulding the index, if it does not start and I can't connect?
On Thu, Nov 15, 2012 at 2:34 AM, VB N <vbnpgc@gmail.com> wrote:
user=,db= FATAL: index "316879235" contains unexpected zero page at block 264user=,db= HINT: Please REINDEX it.Please tell me what can I do to recover?
Did you try re-building the index ? Re-Indexing or re-creating an new index should resolve this.
Regards,
VBN
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@sss.pgh.pa.us> wrote:
Dmitriy Tyugaev <dtyugaev@gmail.com> writes:Hm. Apparently it's hitting the zero page while trying to clean up an
> 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.
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