Re: Pg_upgrade and toast tables bug discovered

On Wed, Sep 3, 2014 at 05:12:30PM -0600, Noah Yetter wrote:
> I'm not sure it's fixed.  I am attempting a pg_upgrade from 9.2.8 to 9.3.5 and
> it dies like so:
>
> (...many relations restoring successfully snipped...)
> pg_restore: creating SEQUENCE address_address_id_seq
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1410; 1259 17670 SEQUENCE
> address_address_id_seq javaprod
> pg_restore: [archiver (db)] could not execute query: ERROR:  could not create
> file "base/16414/17670": File exists
>
> Inspecting a copy of the source cluster, OID 17670 does indeed correspond to
> address_address_id_seq, but inspecting the partially-upgraded cluster that OID
> is taken by pg_toast_202359_index.  Again conferring with a copy of the source
> (9.2.8) cluster, the relation corresponding to filenode 202359 does not have a
> toast table.
>
> (I know pg-hackers isn't the right place to discuss admin issues, but this
> thread is the only evidence of this bug I can find.  If anyone can suggest a
> workaround I would be infinitely grateful.)

Actually, there was a pg_upgrade fix _after_ the release of 9.3.5 which
explains this failure:

commit 4c6780fd17aa43ed6362aa682499cc2f9712cc8b
Author: Bruce Momjian <bruce(at)momjian(dot)us>
Date: Thu Aug 7 14:56:13 2014 -0400

pg_upgrade: prevent oid conflicts with new-cluster TOAST tables

Previously, TOAST tables only required in the new cluster could cause
oid conflicts if they were auto-numbered and a later conflicting oid had
to be assigned.

Backpatch through 9.3

Any chance you can download the 9.3.X source tree and try that? You
need an entire install, not just a new pg_upgrade binary. I am
disapointed I could not fix this before 9.3.5 was released.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +