BUG #4626: postgresql recovery problem - Mailing list pgsql-bugs

From Steven Mezzadri
Subject BUG #4626: postgresql recovery problem
Date
Msg-id 200901231529.n0NFTeXE063068@wwwmaster.postgresql.org
Whole thread Raw
Responses Re: BUG #4626: postgresql recovery problem
List pgsql-bugs
The following bug has been logged online:

Bug reference:      4626
Logged by:          Steven Mezzadri
Email address:      smezzadr@livonia.k12.mi.us
PostgreSQL version: 8.1.11
Operating system:   CentOS 5.2
Description:        postgresql recovery problem
Details:

The database for our moodle web site (uses PHP/postgres 8.1.11/Linux) has
stopped working.  The server froze yesterday and we had to cycle power to
resolve the issue.  Now the postgresql service will not start.  I tried a
backup from last week and get the same result.  I have no other backups or
dumps.  I have tried running fsck.  The disks are in a RAID-1 array and
neither disk has failed.  I did not run out of RAM or disk space.

Here are the last several log entries and the info from pg_controldata:

-bash-3.2$ psql moodle
psql: FATAL:  the database system is starting up

-bash-3.2$ tail postgresql-Fri.log
CONTEXT:  writing block 1 of relation 1663/16384/435720012
WARNING:  could not write block 1 of 1663/16384/435720012
DETAIL:  Multiple failures --- write error may be permanent.
ERROR:  could not open relation 1663/16384/435720012: No such file or
directory
CONTEXT:  writing block 1 of relation 1663/16384/435720012
WARNING:  could not write block 1 of 1663/16384/435720012
DETAIL:  Multiple failures --- write error may be permanent.
ERROR:  could not open relation 1663/16384/435720012: No such file or
directory
CONTEXT:  writing block 1 of relation 1663/16384/435720012
WARNING:  could not write block 1 of 1663/16384/435720012
DETAIL:  Multiple failures --- write error may be permanent.
LOG:  database system was interrupted at 2009-01-16 17:53:25 EST
LOG:  checkpoint record is at 4B/E4008100
LOG:  redo record is at 4B/E3008EA0; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 2696622343; next OID: 438960796
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  redo starts at 4B/E3008EA0
FATAL:  the database system is starting up
FATAL:  the database system is starting up
LOG:  could not open file "pg_xlog/000000010000005300000011" (log file 83,
segment 17): No such file or directory
LOG:  redo done at 53/10FFEA24
PANIC:  btree level 3 not found
LOG:  startup process (PID 5704) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  logger shutting down

-bash-3.2$ pg_controldata
pg_control version number:            812
Catalog version number:               200510211
Database system identifier:           5221770481769643191
Database cluster state:               in recovery
pg_control last modified:             Fri 23 Jan 2009 09:08:53 AM EST
Current log file ID:                  83
Next log file segment:                17
Latest checkpoint location:           4B/E4008100
Prior checkpoint location:            4B/E02E9494
Latest checkpoint's REDO location:    4B/E3008EA0
Latest checkpoint's UNDO location:    0/0
Latest checkpoint's TimeLineID:       1
Latest checkpoint's NextXID:          2696622343
Latest checkpoint's NextOID:          438960796
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Time of latest checkpoint:            Tue 23 Dec 2008 04:07:10 AM EST
Maximum data alignment:               4
Database block size:                  8192
Blocks per segment of large relation: 131072
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Date/time type storage:               floating-point numbers
Maximum length of locale name:        128
LC_COLLATE:                           en_US.UTF-8
LC_CTYPE:                             en_US.UTF-8

----------------------------------------------------------------------------
-------------
top - 10:00:53 up 18:04,  2 users,  load average: 1.62, 1.73, 1.63
Tasks:  84 total,   1 running,  83 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.3%us,  0.5%sy,  0.0%ni, 74.9%id, 23.2%wa,  0.0%hi,  0.0%si,
0.0%st
Mem:   4148532k total,  3998900k used,   149632k free,    11132k buffers
Swap:  6617752k total,     2648k used,  6615104k free,  3743264k cached

 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
5704 postgres  18   0 32220  20m 8912 D    5  0.5   5:52.92 postmaster

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: make of 8.3.5 failed on Linux
Next
From: Alvaro Herrera
Date:
Subject: Re: BUG #4626: postgresql recovery problem