"could not read block 0... " error followed by "database does not exist"

Janet S Jacobsen <JSJacobsen@lbl.gov> writes:

Hi. I am running Postgres 8.2.7 on a Linux system for over
a year now with no problems.

Today one of the database users reported the following error:

psql: FATAL: could not read block 0 of relation 1664/0/1262: read
only 0 of 8192 bytes

Ugh. 1262 is pg_database --- apparently something has truncated your
pg_database table to zero bytes :-(. Which certainly explains the
"no such database" errors.

Have you got any chance of pulling that physical file from a backup?
The one bright spot here is that pg_database is pretty static in most
installations, so you could probably use even a not-very-current copy.
The file you want is $PGDATA/global/1262.

I don't offhand know of any bugs in 8.2.7 that could cause this,
though that is rather an old version ... you might want to think
about an update to 8.2.something-recent.

regards, tom lane

On Fri, Feb 12, 2010 at 7:11 PM, Janet S Jacobsen <JSJacobsen@lbl.gov> wrote:

Hi.  I am running Postgres 8.2.7 on a Linux system for over
a year now with no problems.

Today one of the database users reported the following error:

  psql: FATAL:  could not read block 0 of relation 1664/0/1262: read
  only 0 of 8192 bytes

Sounds like a bad sector on your hard drive. Got any recent backups?

Hi. What I see when I do ls on the current (corrupt)
$PGDATA/global is

...
- rw------- 1 jsjacobs deepsky 0 Feb 8 18:51 1262
...
-rw------- 1 jsjacobs deepsky 602 Feb 12 17:42 pg_auth
-rw------- 1 jsjacobs deepsky 8192 Feb 12 17:42 pg_control
-rw------- 1 jsjacobs deepsky 0 Feb 12 17:42 pg_database
-rw------- 1 jsjacobs deepsky 10927 Feb 12 21:57 pgstat.stat

I have a pgdump from a month ago. Are you saying to restore
that to a different location and then copy over
$PGDATA/global/1262? Do I also need to copy over
$PGDATA/global/pg_database?

Thanks,
Janet

Tom Lane wrote:

Janet S Jacobsen <JSJacobsen@lbl.gov> writes:

Hi. What I see when I do ls on the current (corrupt)
$PGDATA/global is

...
- rw------- 1 jsjacobs deepsky 0 Feb 8 18:51 1262
...
-rw------- 1 jsjacobs deepsky 602 Feb 12 17:42 pg_auth
-rw------- 1 jsjacobs deepsky 8192 Feb 12 17:42 pg_control
-rw------- 1 jsjacobs deepsky 0 Feb 12 17:42 pg_database
-rw------- 1 jsjacobs deepsky 10927 Feb 12 21:57 pgstat.stat

Looks about as I'd expect from your description. Something clobbered
1262, and then the "flat" file pg_database got updated from that.
You might want to look around at what was happening Feb 8 18:51.

I have a pgdump from a month ago. Are you saying to restore
that to a different location and then copy over
$PGDATA/global/1262? Do I also need to copy over
$PGDATA/global/pg_database?

Right on both. Of course, it'd be a good idea to first make a backup of
what you have in $PGDATA now (all of it) --- you want to be able to get
back to where you are if this makes things worse.

regards, tom lane

Thanks, Tom. I will give this a try and let you know what happens.

I don't see anything in the logfile prior to the first "could not read
block 0..." error.

Thanks,
Janet

Janet S Jacobsen <JSJacobsen@lbl.gov> writes:

Hi. What I see when I do ls on the current (corrupt)
$PGDATA/global is

...
- rw------- 1 jsjacobs deepsky 0 Feb 8 18:51 1262
...
-rw------- 1 jsjacobs deepsky 602 Feb 12 17:42 pg_auth
-rw------- 1 jsjacobs deepsky 8192 Feb 12 17:42 pg_control
-rw------- 1 jsjacobs deepsky 0 Feb 12 17:42 pg_database
-rw------- 1 jsjacobs deepsky 10927 Feb 12 21:57 pgstat.stat

Looks about as I'd expect from your description. Something clobbered
1262, and then the "flat" file pg_database got updated from that.
You might want to look around at what was happening Feb 8 18:51.

I have a pgdump from a month ago. Are you saying to restore
that to a different location and then copy over
$PGDATA/global/1262? Do I also need to copy over
$PGDATA/global/pg_database?

Right on both. Of course, it'd be a good idea to first make a backup of
what you have in $PGDATA now (all of it) --- you want to be able to get
back to where you are if this makes things worse.

regards, tom lane