Troubleshooting an upgrade from postgres 9.2 to 9.4
Posted on
When attempting to upgrade from Postgres 9.2.8 to 9.4.5 I encountered a bizarre error that was difficult to troubleshoot:
pg_restore: creating VIEW tables
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1206; 1259 10190710 VIEW tables postgres
pg_restore: [archiver (db)] could not execute query: ERROR: column pg_class.reltoastidxid does not exist
LINE 19: ELSE ( SELECT "pg_class"."reltoastidxid"
^
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT binary_upgrade.set_next_pg_type_oid('10190712'::pg_catalog.oid);
I found a message in the postgresql.org message board here, but there were no answers posted. Also – it doesn’t seem possible to respond to that mailing-list….
The solution ended up being to drop the pg_repack extension, then performing the pg_upgrade after it was dropped.
This got rid of the problematic views that couldn’t successfully recreate themslves through the respective pg_dump and pg_restore commands that pg_upgrade performs.
The upgrade worked successfully. We don’t use pg_repack anymore, so I was just able to uninstall and upgrade, but the postgresql pg_upgrade documentation even states if the problem is a contrib module, it may need to be uninstalled, then postgresql upgraded, then the extension reinstalled again. This appears to have been the case.