Thread: ERROR: could not read block 3 in file "base/12511/12270"

I have been having disk errors that have corrupted something in
my postgres database.  Other databases work ok:

Running on Ubuntu 10.04.

paul@kitanglad:~$ psql -U postgres
psql (9.4.5)
Type "help" for help.

postgres=# SELECT pg_catalog.pg_is_in_recovery();
ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 of 8192 bytes
postgres=# \c pjtest
You are now connected to database "pjtest" as user "postgres".
pjtest=# SELECT pg_catalog.pg_is_in_recovery();
 pg_is_in_recovery
 -------------------
  f
(1 row)


Since this is the "postgres" database, dropping and re-creating it
doesn't seem possible.  pg_dump also gets the same error when I run
it on "postgres" so pg_basebackup will probably get the same error.

The only thing I can think of is to create a completely new data directory
with initdb and pg_dump/restore all the databases in the cluter to the
new data directory.

Is this my only option?

PJ

Paul Jones <pbj@cmicdo.com> writes:
> I have been having disk errors that have corrupted something in
> my postgres database.  Other databases work ok:

> postgres=# SELECT pg_catalog.pg_is_in_recovery();
> ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 of 8192 bytes

Hm.  Evidently you've got a partially truncated file for some system
catalog or index.  It's fairly hard to estimate the consequences of
that without knowing which one it is.  Please see if this works:

$ export PGOPTIONS="-c ignore_system_indexes=true"
$ psql -U postgres

# show ignore_system_indexes;
(should say "on")

# select relname, relkind from pg_class where pg_relation_filenode(oid) = 12270;

If that works, and it tells you filenode 12270 is an index, you're in
luck: just REINDEX that index and you're done (at least with this problem,
there might be more lurking behind it).  Don't forget to unset PGOPTIONS
afterwards.

> Since this is the "postgres" database, dropping and re-creating it
> doesn't seem possible.

Sure it is, as long as you issue the commands from a non-broken database:

# drop database postgres;
DROP DATABASE
# create database postgres with template template0;
CREATE DATABASE

If you don't have any custom objects in the postgres database, this would
be by far the easiest way out.

            regards, tom lane

On 12/23/2015 04:17 PM, Paul Jones wrote:
> I have been having disk errors that have corrupted something in
> my postgres database.  Other databases work ok:
>
> Running on Ubuntu 10.04.
>
> paul@kitanglad:~$ psql -U postgres
> psql (9.4.5)
> Type "help" for help.
>
> postgres=# SELECT pg_catalog.pg_is_in_recovery();
> ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 of 8192 bytes
> postgres=# \c pjtest
> You are now connected to database "pjtest" as user "postgres".
> pjtest=# SELECT pg_catalog.pg_is_in_recovery();
>   pg_is_in_recovery
>   -------------------
>    f
> (1 row)
>
>
> Since this is the "postgres" database, dropping and re-creating it
> doesn't seem possible.  pg_dump also gets the same error when I run
> it on "postgres" so pg_basebackup will probably get the same error.
>
> The only thing I can think of is to create a completely new data directory
> with initdb and pg_dump/restore all the databases in the cluter to the
> new data directory.
>
> Is this my only option?

No, the 'postgres' database is one of the system databases created from
a template

First make sure you have backups of the databases that are not corrupted.

Second have you done any modifications to the 'postgres' database?

If not, then from here:

http://www.postgresql.org/docs/9.4/static/manage-ag-templatedbs.html

"

     Note: template1 and template0 do not have any special status beyond
the fact that the name template1 is the default source database name for
CREATE DATABASE. For example, one could drop template1 and recreate it
from template0 without any ill effects. This course of action might be
advisable if one has carelessly added a bunch of junk in template1. (To
delete template1, it must have pg_database.datistemplate = false.)

     The postgres database is also created when a database cluster is
initialized. This database is meant as a default database for users and
applications to connect to. It is simply a copy of template1 and can be
dropped and recreated if necessary.

"

Have you corrected the disk error situation?
If not, then make plans to create a new cluster somewhere else..

>
> PJ
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Wednesday, December 23, 2015 6:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

 > Paul Jones <pbj@cmicdo.com> writes:
 > > I have been having disk errors that have corrupted something in
 > > my postgres database.  Other databases work ok:
 >
 > > postgres=# SELECT pg_catalog.pg_is_in_recovery();
 > > ERROR:  could not read block 3 in file "base/12511/12270": read only 4096 of 8192 bytes
 >
 > Hm.  Evidently you've got a partially truncated file for some system
 > catalog or index.  It's fairly hard to estimate the consequences of
 > that without knowing which one it is.  Please see if this works:
 >
 > $ export PGOPTIONS="-c ignore_system_indexes=true"
 > $ psql -U postgres
 >
 > # show ignore_system_indexes;
 > (should say "on")
 >
 > # select relname, relkind from pg_class where pg_relation_filenode(oid) = 12270;

paul@kitanglad:~$ export PGOPTIONS="-c ignore_system_indexes=true"
paul@kitanglad:~$ psql -U postgres
psql (9.4.5)
Type "help" for help.

postgres=# show ignore_system_indexes;
 ignore_system_indexes
-----------------------
 on
(1 row)

postgres=# select relname, relkind from pg_class where pg_relation_filenode(oid) = 12270;
      relname      | relkind
-------------------+---------
 pg_proc_oid_index | i

(1 row)

postgres=# reindex index pg_proc_oid_index;
REINDEX
postgres=# \q
paul@kitanglad:~$ unset PGOPTIONS
paul@kitanglad:~$ psql -U postgres
psql (9.4.5)
Type "help" for help.

postgres=# SELECT pg_catalog.pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)


So, it was an index and was quickly fixed.

Thanks!

>
 >
 > If that works, and it tells you filenode 12270 is an index, you're in
 > luck: just REINDEX that index and you're done (at least with this problem,
 > there might be more lurking behind it).  Don't forget to unset PGOPTIONS
 > afterwards.
 >
 >
 > > Since this is the "postgres" database, dropping and re-creating it
 > > doesn't seem possible.
 >
 > Sure it is, as long as you issue the commands from a non-broken database:
 >
 > # drop database postgres;
 > DROP DATABASE
 > # create database postgres with template template0;
 > CREATE DATABASE
 >
 > If you don't have any custom objects in the postgres database, this would
 > be by far the easiest way out.

Good to know!  I thought there was something special about "postgres".
I have not modified it from what initdb put there.

 >
 >             regards, tom lane

 > On 12/23/2015 04:17 PM, Paul Jones wrote:
 > >
 > >I have been having disk errors that have corrupted something in
 > >>my postgres database.  Other databases work ok:
 >
 > This isn't the best characterization...the "postgres" data is not a "system" database but rather a convenient
defaultuser database.  Maybe I'm being overly picky here but seeing "system" in this context does have a connotation
thatwe really don't want to impart onto the "postgres" database. 
 >
 > It is named such because the default user is likewise "postgres" and most utilities when not provided with a
databasename will use the O/S user's name which, for administrative tasks, is likely to be "postgres" (you really
shouldn'tuse root for DB-admin stuff) and thus those commands will be able to connect without much, if any, additional
optionssupplied. 
 >
 > Its presence, absence, or modification in now way alters the fundamental operation of PostgreSQL; though its lack
mayfrustrate users acclimated to using said defaults. 
 >

This was one of the big lessons I learned from this.  All this time I was
under the mistaken impression that it was special.

 > David J.

<pbj@cmicdo.com> writes:
>>> On 12/23/2015 04:17 PM, Paul Jones wrote:
>>> This isn't the best characterization...the "postgres" data is not a "system" database but rather a convenient
defaultuser database.  Maybe I'm being overly picky here but seeing "system" in this context does have a connotation
thatwe really don't want to impart onto the "postgres" database. 

> This was one of the big lessons I learned from this.  All this time I was
> under the mistaken impression that it was special.

FWIW, there really aren't any special databases in a Postgres
installation.  As Paul already explained, "postgres" exists mainly to
provide a convenient default landing point for utility applications.
The only thing magic about it is the name --- you can drop it, and
create a new database with that same name, and be none the worse for
wear.  Or do without, if you don't mind specifying a different landing
point.

The other two created-by-default databases, template0 and template1,
are similarly not really hardwired in.  template1 is hardwired to
the extent that CREATE DATABASE operates by copying whatever database
is named "template1" (in the absence of a different TEMPLATE option).
That's about it.  There is a convention that template0 should be an
absolutely unmodified virgin copy of the state created by initdb,
but it's only a convention not something that the core database code
either enforces or depends on.

Now, if you were to try to drop either of those databases, you'd get
a Bronx cheer:

regression=# drop database template1;
ERROR:  cannot drop a template database
regression=# drop database template0;
ERROR:  cannot drop a template database

but that's only driven by the fact that pg_database.datistemplate is set
for them (and that flag does little more than allow nonprivileged users
to select them as sources for CREATE DATABASE).  If you were up against
the wall in trying to reconstitute a broken installation, you could
manually unset the datistemplate flag for either, drop it, and clone it
from the other one.

In short, there's less magic here than meets the eye.

            regards, tom lane