Обсуждение: RESTORE Error
Hi List!
I'm attempting to do a restore of a database and I'm getting the following
error the I'm not familiar with:
COPY workorder (work_order_id, projectname, request_date, requestor,
request_detail, request_completion_date, request_a...
pg_restore: restoring data for table "worksite"
pg_restore: [archiver (db)] Error from TOC entry 2584; 0 6536819 TABLE DATA
worksite postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at
or near "255454" at character 1
Command was: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it
2006-05-12 17:00:00-05 W6037 Ruth Lane Onalaska Wisconsin 54650 ...
pg_restore: restoring data for table "BLOBS"
pg_restore: restored 0 large objects
WARNING: errors ignored on restore: 73
Process returned exit code 1.
The '255454' here is in the OID column of the table...
And here's the CREATE stmt for the workorder table:
CREATE TABLE workorder.workorder
(
work_order_id serial NOT NULL,
projectname varchar NOT NULL,
request_date timestamptz,
requestor varchar NOT NULL,
request_detail varchar NOT NULL,
request_completion_date timestamptz,
request_address varchar,
request_city varchar,
request_state varchar,
request_zip varchar,
request_location_description varchar,
request_floor varchar,
request_department varchar,
request_priority varchar,
request_health_security_concern varchar,
request_vandalism varchar,
request_recurring_interval varchar,
request_roomid varchar,
request_tagid varchar,
request_presence_required bool,
request_available_date timestamptz,
site_name varchar NOT NULL,
authorizer varchar,
date_authorized timestamptz,
elementid int8,
element_description varchar,
priority varchar,
work_detail varchar,
est_start_time timestamptz,
actual_start_time timestamptz,
est_completion_time timestamptz,
actual_completion_time timestamptz,
actual_labor_cost numeric,
actual_material_cost numeric,
comments varchar,
recurrence_interval int8,
account_name varchar,
CONSTRAINT pk_workorder PRIMARY KEY (work_order_id),
CONSTRAINT "Relationship238" FOREIGN KEY (projectname) REFERENCES project
(projectname) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "Relationship245" FOREIGN KEY (requestor) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship246" FOREIGN KEY (authorizer) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship254" FOREIGN KEY (projectname, account_name)
REFERENCES projectdata.account (projectname, account_name) ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT "Relationship265" FOREIGN KEY (projectname, site_name)
REFERENCES workorder.worksite (projectname, site_name) ON UPDATE CASCADE ON
DELETE RESTRICT
)
Any thoughts or ideas on the error? Thanks in advance for your help!
-Jeanna
Just a bit more info: Running Postgres 8.0
and the: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it
2006-05-12 17:00:00-05 W6037 Ruth Lane Onalaska Wisconsin 54650 ...
is a row (the first row) that should be inserted/restored into the
'workorder.workorder' table.
Hope that helps!
-Jeanna
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeanna Geier
Sent: Thursday, January 11, 2007 3:10 PM
To: pgsql-general
Subject: [GENERAL] RESTORE Error
Hi List!
I'm attempting to do a restore of a database and I'm getting the following
error the I'm not familiar with:
COPY workorder (work_order_id, projectname, request_date, requestor,
request_detail, request_completion_date, request_a...
pg_restore: restoring data for table "worksite"
pg_restore: [archiver (db)] Error from TOC entry 2584; 0 6536819 TABLE DATA
worksite postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at
or near "255454" at character 1
Command was: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it
2006-05-12 17:00:00-05 W6037 Ruth Lane Onalaska Wisconsin 54650 ...
pg_restore: restoring data for table "BLOBS"
pg_restore: restored 0 large objects
WARNING: errors ignored on restore: 73
Process returned exit code 1.
The '255454' here is in the OID column of the table...
And here's the CREATE stmt for the workorder table:
CREATE TABLE workorder.workorder
(
work_order_id serial NOT NULL,
projectname varchar NOT NULL,
request_date timestamptz,
requestor varchar NOT NULL,
request_detail varchar NOT NULL,
request_completion_date timestamptz,
request_address varchar,
request_city varchar,
request_state varchar,
request_zip varchar,
request_location_description varchar,
request_floor varchar,
request_department varchar,
request_priority varchar,
request_health_security_concern varchar,
request_vandalism varchar,
request_recurring_interval varchar,
request_roomid varchar,
request_tagid varchar,
request_presence_required bool,
request_available_date timestamptz,
site_name varchar NOT NULL,
authorizer varchar,
date_authorized timestamptz,
elementid int8,
element_description varchar,
priority varchar,
work_detail varchar,
est_start_time timestamptz,
actual_start_time timestamptz,
est_completion_time timestamptz,
actual_completion_time timestamptz,
actual_labor_cost numeric,
actual_material_cost numeric,
comments varchar,
recurrence_interval int8,
account_name varchar,
CONSTRAINT pk_workorder PRIMARY KEY (work_order_id),
CONSTRAINT "Relationship238" FOREIGN KEY (projectname) REFERENCES project
(projectname) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "Relationship245" FOREIGN KEY (requestor) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship246" FOREIGN KEY (authorizer) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship254" FOREIGN KEY (projectname, account_name)
REFERENCES projectdata.account (projectname, account_name) ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT "Relationship265" FOREIGN KEY (projectname, site_name)
REFERENCES workorder.worksite (projectname, site_name) ON UPDATE CASCADE ON
DELETE RESTRICT
)
Any thoughts or ideas on the error? Thanks in advance for your help!
-Jeanna
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
And log file shows the following (what I posted before was through pgAdmin):
2007-01-11 15:38:17 LOG: could not load root certificate file "C:/Program
Files/PostgreSQL/8.0/data/root.crt": No such file or directory
2007-01-11 15:38:17 DETAIL: Will not verify client certificates.
2007-01-11 15:38:47 ERROR: relation "temp_measurement" does not exist
2007-01-11 15:38:47 ERROR: syntax error at or near "7094982" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67459" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67464" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67465" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "252460" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67478" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "379654" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "379580" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "255624" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67484" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3378657" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "115706" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3370733" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67494" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "379576" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67530" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "718343" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3362828" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67783" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67785" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3410163" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67788" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67808" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67814" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "67830" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "113205" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "113574" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "113579" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "255454" at character 1
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeanna Geier
Sent: Thursday, January 11, 2007 3:20 PM
To: pgsql-general
Subject: Re: [GENERAL] RESTORE Error
Just a bit more info: Running Postgres 8.0
and the: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it
2006-05-12 17:00:00-05 W6037 Ruth Lane Onalaska Wisconsin 54650 ...
is a row (the first row) that should be inserted/restored into the
'workorder.workorder' table.
Hope that helps!
-Jeanna
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeanna Geier
Sent: Thursday, January 11, 2007 3:10 PM
To: pgsql-general
Subject: [GENERAL] RESTORE Error
Hi List!
I'm attempting to do a restore of a database and I'm getting the following
error the I'm not familiar with:
COPY workorder (work_order_id, projectname, request_date, requestor,
request_detail, request_completion_date, request_a...
pg_restore: restoring data for table "worksite"
pg_restore: [archiver (db)] Error from TOC entry 2584; 0 6536819 TABLE DATA
worksite postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at
or near "255454" at character 1
Command was: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it
2006-05-12 17:00:00-05 W6037 Ruth Lane Onalaska Wisconsin 54650 ...
pg_restore: restoring data for table "BLOBS"
pg_restore: restored 0 large objects
WARNING: errors ignored on restore: 73
Process returned exit code 1.
The '255454' here is in the OID column of the table...
And here's the CREATE stmt for the workorder table:
CREATE TABLE workorder.workorder
(
work_order_id serial NOT NULL,
projectname varchar NOT NULL,
request_date timestamptz,
requestor varchar NOT NULL,
request_detail varchar NOT NULL,
request_completion_date timestamptz,
request_address varchar,
request_city varchar,
request_state varchar,
request_zip varchar,
request_location_description varchar,
request_floor varchar,
request_department varchar,
request_priority varchar,
request_health_security_concern varchar,
request_vandalism varchar,
request_recurring_interval varchar,
request_roomid varchar,
request_tagid varchar,
request_presence_required bool,
request_available_date timestamptz,
site_name varchar NOT NULL,
authorizer varchar,
date_authorized timestamptz,
elementid int8,
element_description varchar,
priority varchar,
work_detail varchar,
est_start_time timestamptz,
actual_start_time timestamptz,
est_completion_time timestamptz,
actual_completion_time timestamptz,
actual_labor_cost numeric,
actual_material_cost numeric,
comments varchar,
recurrence_interval int8,
account_name varchar,
CONSTRAINT pk_workorder PRIMARY KEY (work_order_id),
CONSTRAINT "Relationship238" FOREIGN KEY (projectname) REFERENCES project
(projectname) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "Relationship245" FOREIGN KEY (requestor) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship246" FOREIGN KEY (authorizer) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship254" FOREIGN KEY (projectname, account_name)
REFERENCES projectdata.account (projectname, account_name) ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT "Relationship265" FOREIGN KEY (projectname, site_name)
REFERENCES workorder.worksite (projectname, site_name) ON UPDATE CASCADE ON
DELETE RESTRICT
)
Any thoughts or ideas on the error? Thanks in advance for your help!
-Jeanna
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
OK, so if there are several of these same types of errors (which I verified
by running the RESTORE from the command line vs. pgAdmin - some with "\" and
some with the OID; how come it exits out on that one particulat one?? Any
thoughts or ideas on that one?:
COPY profession_type (projectname, profession_type) WITH OIDS FROM
stdin;
pg_restore: restoring data for table "public_authorities"
pg_restore: [archiver (db)] Error from TOC entry 2545; 0 6536592 TABLE
DATA publ
ic_authorities postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax
error at or
near "\" at character 1
Command was: \.
COPY subflooring (projectname, subfloor_id, covered_flooring,
cover_floor_adhesi
on, ...
pg_restore: restoring data for table "training_class"
pg_restore: [archiver (db)] Error from TOC entry 2550; 0 6536617 TABLE
DATA trai
ning_class postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax
error at or
near "67783" at character 1
Command was: 67783 WestSalem CONC001 Concrete \N
\N
\N \N \N \N
67784 WestSalem \N \N \N \N \N
\N
\N
\.
COPY training_class (projectna...
pg_restore: restoring data for table "training_types"
pg_restore: [archiver (db)] Error from TOC entry 2551; 0 6536622 TABLE
DATA trai
ning_types postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax
error at or
near "\" at character 1
Command was: \.
COPY arcelement (elementid, "start", "end", radius_x, radius_y,
large_arc, sweep
, rotation) WITH OIDS FROM stdin;
pg_restore: restoring data for table "attribute"
pg_restore: [archiver (db)] Error from TOC entry 2488; 0 6536241 TABLE
DATA attr
ibute postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax
error at or
near "3410163" at character 1
Command was: 3410163 1810987
(2491.8225222024057,8759.2336777975943)
(2440.9108777975944,8759.2336777975943) 35.999969 35.999969
f
f 0
34...
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeanna Geier
Sent: Thursday, January 11, 2007 3:43 PM
To: pgsql-general
Subject: Re: [GENERAL] RESTORE Error
And log file shows the following (what I posted before was through pgAdmin):
2007-01-11 15:38:17 LOG: could not load root certificate file "C:/Program
Files/PostgreSQL/8.0/data/root.crt": No such file or directory
2007-01-11 15:38:17 DETAIL: Will not verify client certificates.
2007-01-11 15:38:47 ERROR: relation "temp_measurement" does not exist
2007-01-11 15:38:47 ERROR: syntax error at or near "7094982" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67459" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67464" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67465" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "252460" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67478" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "379654" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "379580" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "255624" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67484" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3378657" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "115706" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3370733" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67494" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "379576" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67530" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "718343" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3362828" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67783" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67785" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "3410163" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67788" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67808" at character 1
2007-01-11 15:38:47 ERROR: syntax error at or near "67814" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "67830" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "113205" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "113574" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "113579" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "\" at character 1
2007-01-11 15:38:48 ERROR: syntax error at or near "255454" at character 1
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeanna Geier
Sent: Thursday, January 11, 2007 3:20 PM
To: pgsql-general
Subject: Re: [GENERAL] RESTORE Error
Just a bit more info: Running Postgres 8.0
and the: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it
2006-05-12 17:00:00-05 W6037 Ruth Lane Onalaska Wisconsin 54650 ...
is a row (the first row) that should be inserted/restored into the
'workorder.workorder' table.
Hope that helps!
-Jeanna
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeanna Geier
Sent: Thursday, January 11, 2007 3:10 PM
To: pgsql-general
Subject: [GENERAL] RESTORE Error
Hi List!
I'm attempting to do a restore of a database and I'm getting the following
error the I'm not familiar with:
COPY workorder (work_order_id, projectname, request_date, requestor,
request_detail, request_completion_date, request_a...
pg_restore: restoring data for table "worksite"
pg_restore: [archiver (db)] Error from TOC entry 2584; 0 6536819 TABLE DATA
worksite postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at
or near "255454" at character 1
Command was: 255454 3 WestSalem 2006-05-12 14:10:18.578-05 greg clean it
2006-05-12 17:00:00-05 W6037 Ruth Lane Onalaska Wisconsin 54650 ...
pg_restore: restoring data for table "BLOBS"
pg_restore: restored 0 large objects
WARNING: errors ignored on restore: 73
Process returned exit code 1.
The '255454' here is in the OID column of the table...
And here's the CREATE stmt for the workorder table:
CREATE TABLE workorder.workorder
(
work_order_id serial NOT NULL,
projectname varchar NOT NULL,
request_date timestamptz,
requestor varchar NOT NULL,
request_detail varchar NOT NULL,
request_completion_date timestamptz,
request_address varchar,
request_city varchar,
request_state varchar,
request_zip varchar,
request_location_description varchar,
request_floor varchar,
request_department varchar,
request_priority varchar,
request_health_security_concern varchar,
request_vandalism varchar,
request_recurring_interval varchar,
request_roomid varchar,
request_tagid varchar,
request_presence_required bool,
request_available_date timestamptz,
site_name varchar NOT NULL,
authorizer varchar,
date_authorized timestamptz,
elementid int8,
element_description varchar,
priority varchar,
work_detail varchar,
est_start_time timestamptz,
actual_start_time timestamptz,
est_completion_time timestamptz,
actual_completion_time timestamptz,
actual_labor_cost numeric,
actual_material_cost numeric,
comments varchar,
recurrence_interval int8,
account_name varchar,
CONSTRAINT pk_workorder PRIMARY KEY (work_order_id),
CONSTRAINT "Relationship238" FOREIGN KEY (projectname) REFERENCES project
(projectname) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "Relationship245" FOREIGN KEY (requestor) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship246" FOREIGN KEY (authorizer) REFERENCES
projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT "Relationship254" FOREIGN KEY (projectname, account_name)
REFERENCES projectdata.account (projectname, account_name) ON UPDATE CASCADE
ON DELETE RESTRICT,
CONSTRAINT "Relationship265" FOREIGN KEY (projectname, site_name)
REFERENCES workorder.worksite (projectname, site_name) ON UPDATE CASCADE ON
DELETE RESTRICT
)
Any thoughts or ideas on the error? Thanks in advance for your help!
-Jeanna
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"Jeanna Geier" <jgeier@apt-cafm.com> writes:
> And log file shows the following (what I posted before was through pgAdmin):
> 2007-01-11 15:38:47 ERROR: relation "temp_measurement" does not exist
> 2007-01-11 15:38:47 ERROR: syntax error at or near "7094982" at character 1
> 2007-01-11 15:38:47 ERROR: syntax error at or near "67459" at character 1
OK, that makes it come a bit into focus. I think what pg_restore tried
to send was
COPY temp_measurement FROM STDIN;
7094982 ... rest of row 1 ...
67459 ... rest of row 2 ...
and the COPY command failed because of the stated reason, and then the
backend found itself chewing on a lot of data that was being sent as SQL
commands (because back then pg_restore depended on feedback from the
backend to tell whether an archive entry was COPY data or SQL commands).
So the bottom line here is you're trying to restore into a table that's
not there. How come? I suppose you were trying a data-only restore,
else this is a pg_dump or pg_restore bug.
Another point is that AFAICT 8.0 pg_restore does know enough to stop
if the COPY command fails --- least the case seems to work for me.
[ digs in CVS logs... ] Oh, it seems we fixed that in 8.0.7:
2006-02-05 15:59 tgl
* src/bin/pg_dump/: pg_backup_archiver.c, pg_backup_archiver.h,
pg_backup_db.c (REL8_0_STABLE): Fix pg_restore to properly discard
COPY data when trying to continue after an error in a COPY
statement. Formerly it thought the COPY data was SQL commands, and
got quite confused.
Stephen Frost
So I suppose you're running some earlier 8.0.x subrelease, in which case
an update would be an extremely good idea.
regards, tom lane
Thanks for the reply, Tom. Yes, I had copied some data from a VIEW into a
TABLE (named temp_measurement), then deleted the VIEW and renamed the
TABLE, but must've created my script before performing the rename...
And, yes, it was a Data-only restore.
So, after recreating my scripts and creating my database and loading the
data, it's FINALLY working (yeah)!! =)
And, just for the record we're running version 8.0.8 of Postgres..
C:\Program Files\PostgreSQL\8.0\bin>psql --version
psql (PostgreSQL) 8.0.8
Thanks for everyone's help throughtout the week working through the issues
that I've had; you guys ROCK and the help is greatly appreciated!
-Jeanna
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Thursday, January 11, 2007 9:28 PM
To: Jeanna Geier
Cc: pgsql-general
Subject: Re: [GENERAL] RESTORE Error
"Jeanna Geier" <jgeier@apt-cafm.com> writes:
> And log file shows the following (what I posted before was through
pgAdmin):
> 2007-01-11 15:38:47 ERROR: relation "temp_measurement" does not exist
> 2007-01-11 15:38:47 ERROR: syntax error at or near "7094982" at character
1
> 2007-01-11 15:38:47 ERROR: syntax error at or near "67459" at character 1
OK, that makes it come a bit into focus. I think what pg_restore tried
to send was
COPY temp_measurement FROM STDIN;
7094982 ... rest of row 1 ...
67459 ... rest of row 2 ...
and the COPY command failed because of the stated reason, and then the
backend found itself chewing on a lot of data that was being sent as SQL
commands (because back then pg_restore depended on feedback from the
backend to tell whether an archive entry was COPY data or SQL commands).
So the bottom line here is you're trying to restore into a table that's
not there. How come? I suppose you were trying a data-only restore,
else this is a pg_dump or pg_restore bug.
Another point is that AFAICT 8.0 pg_restore does know enough to stop
if the COPY command fails --- least the case seems to work for me.
[ digs in CVS logs... ] Oh, it seems we fixed that in 8.0.7:
2006-02-05 15:59 tgl
* src/bin/pg_dump/: pg_backup_archiver.c, pg_backup_archiver.h,
pg_backup_db.c (REL8_0_STABLE): Fix pg_restore to properly discard
COPY data when trying to continue after an error in a COPY
statement. Formerly it thought the COPY data was SQL commands, and
got quite confused.
Stephen Frost
So I suppose you're running some earlier 8.0.x subrelease, in which case
an update would be an extremely good idea.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings