Обсуждение: invalid page header in pg_statistic

Поиск
Список
Период
Сортировка

invalid page header in pg_statistic

От
"Ed L."
Дата:
How do I fix this 7.4.6 issue short of initdb?

    invalid page header in block 110 of relation "pg_statistic"

I looked at the block via pg_filedump (included below), and it
does not appear to me to be corrupted, so not sure what I would
zero out, if anything.

TIA.
Ed


*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 3.0
*
* File: 16408.backup-20070207-01:11:58PST
* Options used: -y -f -R 110 110
*
* Dump created on: Wed Feb  7 01:56:04 2007
*******************************************************************

Block  110
********************************************************
<Header> -----
 Block Offset: 0x000dc000         Offsets: Lower     160 (0x00a0)
 Block: Size 8192  Version    1            Upper    8192 (0x2000)
 LSN:  logid    178 recoff 0xf577b4e8      Special  8192 (0x2000)
 Items:   35                   Free Space: 8032
 Length (including item array): 164

  0000: 000000b2 f577b4e8 00000028 00a02000  .....w.....(.. .
  0010: 20002001 3da00000 3ae00000 37300000   . .=...:...70..
  0020: 34d00000 32100000 2e600000 2c000000  4...2....`..,...
  0030: 29400000 25900000 23300000 20700000  )@..%...#0.. p..
  0040: 1cc00000 1a600000 17a00000 13f00000  .....`..........
  0050: 11900000 0ed00000 0b200000 08c00000  ......... ......
  0060: 06000000 02500000 1bc00000 19c00000  .....P..........
  0070: 18900000 17400000 15f00000 14a00000  .....@..........
  0080: 12c00000 3da00000 3ba00000 3a700000  ....=...;...:p..
  0090: 39200000 37d00000 36800000 34a00000  9 ..7...6...4...
  00a0: 00000000                             ....

<Data> ------
 Item   1 -- Length:    0  Offset: 7888 (0x1ed0)  Flags: 0x00
 Item   2 -- Length:    0  Offset: 7536 (0x1d70)  Flags: 0x00
 Item   3 -- Length:    0  Offset: 7064 (0x1b98)  Flags: 0x00
 Item   4 -- Length:    0  Offset: 6760 (0x1a68)  Flags: 0x00
 Item   5 -- Length:    0  Offset: 6408 (0x1908)  Flags: 0x00
 Item   6 -- Length:    0  Offset: 5936 (0x1730)  Flags: 0x00
 Item   7 -- Length:    0  Offset: 5632 (0x1600)  Flags: 0x00
 Item   8 -- Length:    0  Offset: 5280 (0x14a0)  Flags: 0x00
 Item   9 -- Length:    0  Offset: 4808 (0x12c8)  Flags: 0x00
 Item  10 -- Length:    0  Offset: 4504 (0x1198)  Flags: 0x00
 Item  11 -- Length:    0  Offset: 4152 (0x1038)  Flags: 0x00
 Item  12 -- Length:    0  Offset: 3680 (0x0e60)  Flags: 0x00
 Item  13 -- Length:    0  Offset: 3376 (0x0d30)  Flags: 0x00
 Item  14 -- Length:    0  Offset: 3024 (0x0bd0)  Flags: 0x00
 Item  15 -- Length:    0  Offset: 2552 (0x09f8)  Flags: 0x00
 Item  16 -- Length:    0  Offset: 2248 (0x08c8)  Flags: 0x00
 Item  17 -- Length:    0  Offset: 1896 (0x0768)  Flags: 0x00
 Item  18 -- Length:    0  Offset: 1424 (0x0590)  Flags: 0x00
 Item  19 -- Length:    0  Offset: 1120 (0x0460)  Flags: 0x00
 Item  20 -- Length:    0  Offset:  768 (0x0300)  Flags: 0x00
 Item  21 -- Length:    0  Offset:  296 (0x0128)  Flags: 0x00
 Item  22 -- Length:    0  Offset: 3552 (0x0de0)  Flags: 0x00
 Item  23 -- Length:    0  Offset: 3296 (0x0ce0)  Flags: 0x00
 Item  24 -- Length:    0  Offset: 3144 (0x0c48)  Flags: 0x00
 Item  25 -- Length:    0  Offset: 2976 (0x0ba0)  Flags: 0x00
 Item  26 -- Length:    0  Offset: 2808 (0x0af8)  Flags: 0x00
 Item  27 -- Length:    0  Offset: 2640 (0x0a50)  Flags: 0x00
 Item  28 -- Length:    0  Offset: 2400 (0x0960)  Flags: 0x00
 Item  29 -- Length:    0  Offset: 7888 (0x1ed0)  Flags: 0x00
 Item  30 -- Length:    0  Offset: 7632 (0x1dd0)  Flags: 0x00
 Item  31 -- Length:    0  Offset: 7480 (0x1d38)  Flags: 0x00
 Item  32 -- Length:    0  Offset: 7312 (0x1c90)  Flags: 0x00
 Item  33 -- Length:    0  Offset: 7144 (0x1be8)  Flags: 0x00
 Item  34 -- Length:    0  Offset: 6976 (0x1b40)  Flags: 0x00
 Item  35 -- Length:    0  Offset: 6736 (0x1a50)  Flags: 0x00


*** End of Requested Range Encountered. Last Block Read: 110 ***

Re: invalid page header in pg_statistic

От
Martijn van Oosterhout
Дата:
On Wed, Feb 07, 2007 at 03:00:20AM -0700, Ed L. wrote:
> How do I fix this 7.4.6 issue short of initdb?
>
>     invalid page header in block 110 of relation "pg_statistic"

Take a copy of the file, then you should be able to truncate it.
There's also the zero_damaged_pages option, though I don't know whether
that applies here.

> I looked at the block via pg_filedump (included below), and it
> does not appear to me to be corrupted, so not sure what I would
> zero out, if anything.

I don't see anything odd expect that the header has both a lot of items
and a lot of free space...

>  Items:   35                   Free Space: 8032
>  Length (including item array): 164

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Вложения

Re: invalid page header in pg_statistic

От
Tom Lane
Дата:
"Ed L." <pgsql@bluepolka.net> writes:
> How do I fix this 7.4.6 issue short of initdb?
>     invalid page header in block 110 of relation "pg_statistic"
> I looked at the block via pg_filedump (included below), and it
> does not appear to me to be corrupted, so not sure what I would
> zero out, if anything.

Seems odd to me too ... but pg_statistic doesn't contain any
irreplaceable data.  See if you can TRUNCATE it.  If so, do
an ANALYZE to repopulate it.  I don't recall if 7.4 has any
special hoops you'd have to jump through to truncate a system
catalog ...

            regards, tom lane

Re: invalid page header in pg_statistic

От
"Ed L."
Дата:
On Wednesday February 7 2007 9:01 am, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > How do I fix this 7.4.6 issue short of initdb?
> >     invalid page header in block 110 of relation "pg_statistic"
> > I looked at the block via pg_filedump (included below), and
> > it does not appear to me to be corrupted, so not sure what I
> > would zero out, if anything.
>
> Seems odd to me too ... but pg_statistic doesn't contain any
> irreplaceable data.  See if you can TRUNCATE it.  If so, do
> an ANALYZE to repopulate it.  I don't recall if 7.4 has any
> special hoops you'd have to jump through to truncate a system
> catalog ...

$ psql -c "truncate table pg_statistic"
ERROR:  permission denied: "pg_statistic" is a system catalog

I suppose this means single-user mode is required?  Any other
trick to do this during uptime?

TIA.
Ed

Re: invalid page header in pg_statistic

От
"Ed L."
Дата:
On Wednesday February 7 2007 9:01 am, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > How do I fix this 7.4.6 issue short of initdb?
> >     invalid page header in block 110 of relation "pg_statistic"
> > I looked at the block via pg_filedump (included below), and
> > it does not appear to me to be corrupted, so not sure what I
> > would zero out, if anything.
>
> Seems odd to me too ... but pg_statistic doesn't contain any
> irreplaceable data.  See if you can TRUNCATE it.  If so, do
> an ANALYZE to repopulate it.  I don't recall if 7.4 has any
> special hoops you'd have to jump through to truncate a system
> catalog ...

Scheduled downtime finally arrived last night, and I tried
unsuccessfully to truncate this table:

    postgres -D $PGDATA -O -o standalone_log mydb
        > truncate pg_statistic;
    > ^D

        pg_ctl start
        psql -d mydb -c "vacuum analyze"

The truncate showed no errors.  The vacuum analyze showed the
same error in block 110 of the pg_statistic table.  Any ideas
what I'm missing here?  Is there another acceptable way to
truncate that table, perhaps "rm file; touch file"?

Ed

Re: invalid page header in pg_statistic

От
Tom Lane
Дата:
"Ed L." <pgsql@bluepolka.net> writes:
> The truncate showed no errors.  The vacuum analyze showed the
> same error in block 110 of the pg_statistic table.

Really!?  Hm, I wonder if you have a reproducible problem.  Would it be
possible for you to send me the physical pg_statistic file (off-list)?
I'm curious to see exactly what's wrong with it.

            regards, tom lane