Restore Troubles

I am using Postgres 7.2.1, the default Debian Woody install.

If I do a pg_dump on my database using:

pg_dump superwallsdata --blobs --file=superwallsdata.bak
--format=c --verbose --compress=9 -U postgres -h server002.hightowergroup.com

The, when I try to restore with the following command:

pg_restore superwallsdata.bak -d superwallsdata --no-owner -S
postgres -v -x -U postgres -h server005.hightowergroup.com -W -R

It will fail because of missing objects. This is because many of the
objects are out of order so I have to do a table of contents and
finagle the list before restore will go through.

I find that I must break my restore into three parts (maybe I only need
two). One for schema, one for data, and one for blobs. If I don't the
blobs part will fail with an unexpected termination from the database.

Finally when I do the restore it fails on the view at the end of this post
(sorry for the size) with the error:

pg_restore: creating VIEW v_master_objects
pg_restore: [archiver (db)] could not execute query: ERROR: CREATE TABLE:
attribute "object_order" duplicated

I can add the view to the database after the restore, but that kind of
defeats the purpose of having a restore command.

The Question I have is: Is it possible to backup a database and not have
to mess with the object order afterwards? Secondly, is it possible to
restore all my objects from a backup or will I always have to check for
objects that don't want to restore?

Thanks,

Dave Leedom

CREATE VIEW "v_master_objects" (
production_id, runs_definition_id, object_order,
project_objects_id, projects_id, parts_id,
status_code, location_code_id, type_code, objects_name,
run_number, wall_number, panel_number,
parent_id, child_id, width_size, width_outside_size,
height_size, top_edge_position, left_edge_type,
left_edge_position, left_angle_size, center_position,
right_edge_type, right_edge_position, right_angle_size,
bottom_edge_position, object_count, style_code,
project_object_order, object_size, point_load,
direction_flag, object_guid, style, duid, note_x_position,
note_y_position, note_leader_x_position,
note_leader_y_position, note_box_flag, note_leader_location,
note_text, projects_name, project_number,
note_font_size, note_distance_size)
AS
SELECT tbl_production_run.production_id,
tbl_production_run.runs_definition_id, tbl_production_run.object_order,
tbl_project_objects.project_objects_id, tbl_project_objects.projects_id,
tbl_project_objects.parts_id, tbl_project_objects.status_code,
tbl_project_objects.location_code_id, tbl_project_objects.type_code,
tbl_project_objects.objects_name,
tbl_project_objects.run_number,
tbl_project_objects.wall_number, tbl_project_objects.panel_number,
tbl_project_objects.parent_id, tbl_project_objects.child_id,
tbl_project_objects.width_size,
tbl_project_objects.width_outside_size,
tbl_project_objects.height_size, tbl_project_objects.top_edge_position,
tbl_project_objects.left_edge_type, tbl_production_run.left_position,
tbl_project_objects.left_angle_size,
tbl_project_objects.center_position,
tbl_project_objects.right_edge_type, tbl_production_run.right_position,
tbl_project_objects.right_angle_size,
tbl_project_objects.bottom_edge_position,
tbl_project_objects.object_count, tbl_project_objects.style_code,
tbl_project_objects.object_order, tbl_project_objects.object_size,
tbl_project_objects.point_load, tbl_project_objects.direction_flag,
tbl_project_objects.object_guid, tbl_project_objects.style,
tbl_project_objects.duid, tbl_project_objects.note_x_position,
tbl_project_objects.note_y_position,
tbl_project_objects.note_leader_x_position,
tbl_project_objects.note_leader_y_position,
tbl_project_objects.note_box_flag,
tbl_project_objects.note_leader_location,
tbl_project_objects.note_text, tbl_projects.projects_name,
tbl_projects.project_number,
tbl_project_objects.note_font_size, tbl_project_objects.note_distance_size
FROM ((tbl_production_run JOIN tbl_project_objects ON
((tbl_production_run.objects_id =
tbl_project_objects.project_objects_id)))
JOIN tbl_projects ON ((tbl_projects.projects_id =
tbl_project_objects.projects_id))) UNION
SELECT tbl_production_run.production_id,
tbl_production_run.runs_definition_id, tbl_production_run.object_order,
tbl_project_objects.project_objects_id,
tbl_project_objects.projects_id,
tbl_project_objects.parts_id, tbl_project_objects.status_code,
tbl_project_objects.location_code_id,
tbl_project_objects.type_code,
tbl_project_objects.objects_name, tbl_project_objects.run_number,
tbl_project_objects.wall_number,
tbl_project_objects.panel_number,
tbl_project_objects.parent_id, tbl_project_objects.child_id,
tbl_project_objects.width_size,
tbl_project_objects.width_outside_size,
tbl_project_objects.height_size, (parent.top_edge_position -
tbl_project_objects.top_edge_position),
tbl_project_objects.left_edge_type,
(tbl_project_objects.left_edge_position - parent.left_edge_position),
tbl_project_objects.left_angle_size,
(tbl_project_objects.center_position -
parent.left_edge_position), tbl_project_objects.right_edge_type,
(tbl_project_objects.right_edge_position -
parent.left_edge_position), tbl_project_objects.right_angle_size,
(parent.top_edge_position -
tbl_project_objects.bottom_edge_position),
tbl_project_objects.object_count, tbl_project_objects.style_code,
tbl_project_objects.object_order,
tbl_project_objects.object_size, tbl_project_objects.point_load,
tbl_project_objects.direction_flag,
tbl_project_objects.object_guid,
tbl_project_objects.style, tbl_project_objects.duid,
tbl_project_objects.note_x_position,
tbl_project_objects.note_y_position,
tbl_project_objects.note_leader_x_position,
tbl_project_objects.note_leader_y_position,
tbl_project_objects.note_box_flag,
tbl_project_objects.note_leader_location, tbl_project_objects.note_text,
tbl_projects.projects_name,
tbl_projects.project_number, tbl_project_objects.note_font_size,
tbl_project_objects.note_distance_size
FROM (((tbl_production_run JOIN tbl_project_objects ON
((tbl_production_run.objects_id = tbl_project_objects.parent_id))) JOIN
tbl_projects ON ((tbl_projects.projects_id =
tbl_project_objects.projects_id))) JOIN tbl_project_objects parent ON
((tbl_project_objects.parent_id = parent.project_objects_id)));

The Hightower Group, Inc.
Information Technology Consulting

165 West Airport Road/Lititz, PA 17543
V:717-560-4002, 877-560-4002 x: 114
F:717-560-2825
www.hightowergroup.com