I am trying to restore a database backup of a Postgresql version 9.2 onto Postgresql 13.5, but while restoring I am getting this message:

index row size 2712 exceeds btree version 4 maximum 2704 for index

DB restore shows successfull but data is not available once restore is done.

CodePudding user response：

My guess is that the SELECT current_setting('block_size'); must have been bigger on your old instance compared to the new one, which causes a non-key column included in an index to exceed the limit when pg_restore inserts the rows. You can restore only the schema using --schema-only pg_restore option, inspect your indices, temporarily drop them and then safely restore just the data with --data-only. Indices are not really in the backup, they are rebuilt from scratch during restore - so you can just do it later yourself.

Here is a thread on changing PostgreSQL block size.

CodePudding user response：

Unless your old database was using a non-standard block size (which is unusual and not recommended), you must be falling victim to this change in PostgreSQL v12:

In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handling of duplicate entries (Peter Geoghegan)

This means that a REINDEX operation on an index pg_upgrade'd from a previous release could potentially fail.

You will have to modify the data in your table to be a bit shorter.

An alternative, but not commendable solution is to use pg_upgrade rather than dump and restore. That would leave the index as it is. But then you can never REINDEX or dump and restore that database.