Обсуждение: root page 3 of index "pg_class_oid_index" has level 0, expected 1

Abhijit Gharami wrote:
> During major version upgrade from PostgreSQL v9.4 to v9.6 I facing following error:
> 
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions                                   ok
> 
> connection to database failed: FATAL:  root page 3 of index "pg_class_oid_index" has level 0, expected 1
> 
> 
> could not connect to old postmaster started with the command:
> "/postgresql94/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/data/directory/postgresql" -o "-p 5432 -b  -c
listen_addresses=''-c unix_socket_permissions=0700 -c
 
> unix_socket_directories='/home/postgresql_user'" start
> Failure, exiting
> 
> 
> Again, I was trying to take the pg_dumpall of the same database cluster(PostgreSQL v9.4) and it failed with error:
> pg_dump: [archiver (db)] connection to database "template1" failed: FATAL:  root page 3 of index "pg_class_oid_index"
haslevel 0, expected 1
 
> pg_dumpall: pg_dump failed on database "template1", exiting
> 
> Please suggest how should I proceed with major version upgrade in this case.

You are experiencing catalog corruption.

Try stopping the cluster and starting it with

  postgres -P -D /path/to/data/dir

Then connect and run

  REINDEX TABLE pg_catalog.pg_class;

That should fix the corrupted index.

If you have data corruption, you shouldn't use pg_upgrade to upgrade.
pg_dumpall / psql is the way to go.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Thu, Nov 14, 2019 at 08:26:48AM +0100, Laurenz Albe wrote:
> If you have data corruption, you shouldn't use pg_upgrade to upgrade.
> pg_dumpall / psql is the way to go.

Please refer to this wiki page:
https://wiki.postgresql.org/wiki/Corruption

If you have a cluster in such a state, you have risks of having a
corruption which has spread, and you should not reuse this data folder
as-is.  You can think of this REINDEX as a short-term solution to
retrieve as much of your data as you can so as you can dump it, and
then restore it into a safer location.  For corruptions with system
indexes, you also have the option of using ignore_system_indexes.
--
Michael