Thread: Re: "could not read block 0... " error followed by "database does not exist"
Re: "could not read block 0... " error followed by "database does not exist"
From
Janet S Jacobsen
Date:
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. 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
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