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:
Show quoted text
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?
