Thread: pg_dumpall + restore = missing view
During last night's "maintenance window" (er, unplanned loss of connectivity due to workers diamond-core-drilling through an OC12 as part of, I kid you not, installation of a new toilet) I upgraded from 7.4.1 to 7.4.6. Basic method: Build 7.4.6 Backup db: pg_dumpall > dumpfile Install: stop 7.4.1/move datadir/install 7.4.6/copy configs Start: initdb/start PG Restore: psql -f dumpfile template1 This appears to have all gone well execpt that one view is missing. I've restored that view by hand but am curious if this is a PG bug or failure of the nut behind the wheel. The view involves the union of many tables and its creation failed because creation of one of the tables does not take place until later in the dump file. Ideas? Cheers, Steve
Steve Crawford <scrawford@pinpointresearch.com> writes:
> This appears to have all gone well execpt that one view is missing.
> I've restored that view by hand but am curious if this is a PG bug or
> failure of the nut behind the wheel.
> The view involves the union of many tables and its creation failed
> because creation of one of the tables does not take place until later
> in the dump file.
This is a longstanding pg_dump bug: it's not very bright about order of
creation of objects. (In this case I surmise that you created the view,
and later altered it to reference a table that didn't exist when the
view was originally created.)
As of 8.0 pg_dump examines dependency information and should theoretically
always get this right, but in prior versions it's a real hazard.
regards, tom lane
For the record, you shouldn't have needed to do a dump restore between 7.4.1 and 7.4.6 should you? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 19, 2004, at 7:24 PM, Tom Lane wrote: > Steve Crawford <scrawford@pinpointresearch.com> writes: >> This appears to have all gone well execpt that one view is missing. >> I've restored that view by hand but am curious if this is a PG bug or >> failure of the nut behind the wheel. > >> The view involves the union of many tables and its creation failed >> because creation of one of the tables does not take place until later >> in the dump file. > > This is a longstanding pg_dump bug: it's not very bright about order of > creation of objects. (In this case I surmise that you created the > view, > and later altered it to reference a table that didn't exist when the > view was originally created.) > > As of 8.0 pg_dump examines dependency information and should > theoretically > always get this right, but in prior versions it's a real hazard. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
"Thomas F.O'Connell" <tfo@sitening.com> wrote: > > For the record, you shouldn't have needed to do a dump restore between > 7.4.1 and 7.4.6 should you? IIRC, it was 7.4.1 -> 7.4.2 that required either that or some manual fixing-up. Jim