Skip site navigation (1) Skip section navigation (2)

Restore Troubles

From: "David A(dot) Leedom" <daleedom(at)hightowergroup(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Restore Troubles
Date: 2003-05-01 01:21:10
Message-ID: 5.2.0.9.0.20030430210537.02283360@mail.hightowergroup.com (view raw or flat)
Thread:
Lists: pgsql-admin
I am using Postgres 7.2.1, the default Debian Woody install.

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

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.


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 

pgsql-admin by date

Next:From: Jean-Michel ChabanneDate: 2003-05-01 06:14:31
Subject: Re: LC_CTYPE
Previous:From: Tom LaneDate: 2003-05-01 00:15:16
Subject: Re: LC_CTYPE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group