Troubleshooting an upgrade from postgres 9.2 to 9.4
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_restore commands that
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.