Restore Troubles - Mailing list pgsql-admin

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

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.476 / Virus Database: 273 - Release Date: 4/24/2003