Thread: Problems with pg_restore
Hi ,
I have the following problem.
DBVersion: PostgreSQL 7.2.3 on i386-pc-bsdi4.0.1, compiled by GCC 2.7.2.1
I have a dump of a database on two parts.
Here are the dump commands:
pg_dump -s -S postgres "db_name" |gzip > "db_name.shema.sql"
pg_dump -S postgres -a -Fc "db_name" > "db_name.data.dump"
So restore twice the schema with the following command:
psql -e "db_name" < "db_name.shema.sql"
psql -e "db_name" < "db_name.shema.sql"
I do it twice because some tables don't create from the first time.
And i come to the part to restore the data. And after a while I saw the
following error:
pg_restore -v -a -d -Fc -U "username" -O "db_name.data.dump"
.......
pg_restore: disabling triggers
pg_restore: restoring data for table tracking_base
pg_restore: enabling triggers
pg_restore: disabling triggers
pg_restore: connecting to database for restore
pg_restore: disabling triggers
pg_restore: restoring data for table epay_requests_archive
pg_restore: ERROR: invalid input syntax for type boolean: "172"
pg_restore: lost synchronization with server, resetting connection
pg_restore: [archiver (db)] error returned by PQendcopy
pg_restore: *** aborted because of error
When I try again on postgres 8.0.0 beta1
pg_restore: connecting to database for restore
pg_restore: restoring data for table "epay_requests_archive"
pg_restore: ERROR: invalid input syntax for type boolean: "172"
CONTEXT: COPY epay_requests_archive, line 1, column deleted: "172"
pg_restore: [archiver (db)] error returned by PQendcopy
pg_restore: *** aborted because of error
So here is more specific the tablename:
and here is the table creatin from the scema file:
CREATE TABLE "tracking_base" (
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"created_by" text DEFAULT get_username() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_by" text DEFAULT get_username() NOT NULL,
"version" integer DEFAULT nextval('tracking_seq'::text) NOT NULL,
"track_id" integer DEFAULT nextval('tracking_seq'::text) NOT NULL,
"archived_at" timestamp with time zone,
"archived_by" text,
"archived" boolean DEFAULT 'f'::bool NOT NULL,
"deleted" boolean
);
CREATE TABLE "epay_requests_archive" (
"ereq_id" integer,
"etrade_id" integer NOT NULL,
"password" text NOT NULL,
"eclient_id" integer NOT NULL,
"expires" integer NOT NULL,
"eservice_id" integer NOT NULL,
"total" double precision NOT NULL,
"quantity" double precision,
"status_id" integer NOT NULL,
"paid" boolean NOT NULL,
"make_inv" boolean NOT NULL,
"send_email" boolean NOT NULL
)
INHERITS ("tracking_base");
customer1=# \d epay_requests_archive
Table "epay_requests_archive"
Column | Type |
Modifiers
-------------+--------------------------+------------------------------------------------
created_at | timestamp with time zone | not null default 'now'::text
created_by | text | not null default get_username()
updated_at | timestamp with time zone | not null default 'now'::text
updated_by | text | not null default get_username()
version | integer | not null default
nextval('tracking_seq'::text)
track_id | integer | not null default
nextval('tracking_seq'::text)
archived_at | timestamp with time zone |
archived_by | text |
archived | boolean | not null default 'f'::bool
deleted | boolean |
ereq_id | integer |
etrade_id | integer | not null
password | text | not null
eclient_id | integer | not null
expires | integer | not null
eservice_id | integer | not null
total | double precision | not null
quantity | double precision |
status_id | integer | not null
paid | boolean | not null
make_inv | boolean | not null
send_email | boolean | not null
As I guess somehow the field deleted is missed in the copy command but
is present in the scema and so the '172' probably is for the column ereq_id.
Now I try to delete it in PG8.0.0 but :
ALTER TABLE ONLY epay_requests_archive DROP COLUMN deleted RESTRICT;
ERROR: cannot drop inherited column "deleted"
The problem is that I don't have another copy of this database so I must
find a way to restore it.
Now I can't drop the field to try if in that case the pg_restore will pass.
And I can't edit the "db_name.data.dump" because it is not readable (the
-Fc optin to pg_dump).
Is there a way to convert this file to INSERT commands (this would be
the best).
Or to press pg_restore to show me more information (the sql commands it
executes).
And any ideas how to drop this field, just to try wheather the restore
will pass.
Thanks in advance.
Kaloyan
Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes:
> [ details omitted ]
The easiest way around this is to use a recent (7.4 or 8.0) pg_dump to
dump from the 7.2 database. That will produce a dump using copy-with-
column-list commands which will be proof against the schema
rearrangement that you seem to have done. (I am guessing that the
"deleted" column was added to the parent table after the fact.)
If you no longer have the 7.2 server active, then you'll have to
hand-edit the dump script to add a column list in the proper order (ie,
the order matching the COPY data) to the COPY command for the child
table.
regards, tom lane
Thanks Tom, You are right. I coleage of mine has added the delete column after the schema was created and it ptobably was on the bottom of the table and dumped there. But now when I restore the schema this column is on the top and the copy command wouldn't work. So I must change the schema file on hand. To remove the column from the tracking_base table, and to add it manuly on the bottem of each table that inherits it. This should work. Thanks again. Kaloyan Tom Lane wrote: >Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes: > > >>[ details omitted ] >> >> > >The easiest way around this is to use a recent (7.4 or 8.0) pg_dump to >dump from the 7.2 database. That will produce a dump using copy-with- >column-list commands which will be proof against the schema >rearrangement that you seem to have done. (I am guessing that the >"deleted" column was added to the parent table after the fact.) > >If you no longer have the 7.2 server active, then you'll have to >hand-edit the dump script to add a column list in the proper order (ie, >the order matching the COPY data) to the COPY command for the child >table. > > regards, tom lane > > > >
Kaloyan Iliev Iliev wrote: > Hi , > > I have the following problem. > > DBVersion: PostgreSQL 7.2.3 on i386-pc-bsdi4.0.1, compiled by GCC 2.7.2.1 > > I have a dump of a database on two parts. > Here are the dump commands: > > pg_dump -s -S postgres "db_name" |gzip > "db_name.shema.sql" > pg_dump -S postgres -a -Fc "db_name" > "db_name.data.dump" > > So restore twice the schema with the following command: > > psql -e "db_name" < "db_name.shema.sql" > psql -e "db_name" < "db_name.shema.sql" > > I do it twice because some tables don't create from the first time. [snip] > Now I try to delete it in PG8.0.0 but : > ALTER TABLE ONLY epay_requests_archive DROP COLUMN deleted RESTRICT; > ERROR: cannot drop inherited column "deleted" > > The problem is that I don't have another copy of this database so I must > find a way to restore it. If you've got two versions of PG, it's usually best to use the most recent pg_dump - each version is smarter than the last, but is backwards-compatible quite a way. Try dumping with the 8.0 version of pg_dump. -- Richard Huxton Archonet Ltd
Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes:
> You are right. I coleage of mine has added the delete column after the
> schema was created and it ptobably was on the bottom of the table and
> dumped there. But now when I restore the schema this column is on the
> top and the copy command wouldn't work. So I must change the schema file
> on hand. To remove the column from the tracking_base table, and to add
> it manuly on the bottem of each table that inherits it.
No, you need not change the database schema, just fix the COPY commands
for the child tables.
regards, tom lane
But as I say the file with copy commands contains ^@^@^@TABLE DATA^@^@^@^@^@^@^@^@^@^@^@!^@^@^@COPY "tracking_base" FROM stdin; ^@^E^@^@^@maria^A^A^@^@^@^@I^,¬^@^@^[^@^@^@^@0x9b^A^@^@^@^A^@^@^@^@^G^@^@^@3944825^@^U^@^@^@epay_requests_archive ^@ things like that. So how can I change this. For me it will be easier to change the schema (which is in normal text format). To remove the field from the base table and to add it at the end of each table that inherits the base table. This is the command with which the column was added so I suppose it was added at the end of each table. alter table tracking_base* add deleted bool; Kaloyan Tom Lane wrote: >Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes: > > >>You are right. I coleage of mine has added the delete column after the >>schema was created and it ptobably was on the bottom of the table and >>dumped there. But now when I restore the schema this column is on the >>top and the copy command wouldn't work. So I must change the schema file >>on hand. To remove the column from the tracking_base table, and to add >>it manuly on the bottem of each table that inherits it. >> >> > >No, you need not change the database schema, just fix the COPY commands >for the child tables. > > regards, tom lane > > > >
Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes:
> But as I say the file with copy commands contains
> ^@^@^@TABLE DATA^@^@^@^@^@^@^@^@^@^@^@!^@^@^@COPY "tracking_base" FROM
> stdin;
> ^@^E^@^@^@maria^A^A^@^@^@^@I^,�^@^@^[^@^@^@^@0x9b^A^@^@^@^A^@^@^@^@^G^@^@^@3944825^@^U^@^@^@epay_requests_archive
> ^@
> things like that. So how can I change this.
Use pg_restore to make a text dump script, and then edit that, and then
load it into your newer server.
> For me it will be easier to change the schema (which is in normal text
> format). To remove the field from the base table and to add it at the
> end of each table that inherits the base table.
[ shrug... ] If you want to make a permanent change to work around this
one-time problem ...
regards, tom lane
You are right again. I don't want to make permanet changes for this one-time problem. Yes I find a way to Use pg_restore to make a text dump script, and then edit that, and then load it into your newer server. - pg_restore can output to file not only database. I will edit this file. Thanks. Kaloyan Tom Lane wrote: >Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes: > > >>But as I say the file with copy commands contains >>^@^@^@TABLE DATA^@^@^@^@^@^@^@^@^@^@^@!^@^@^@COPY "tracking_base" FROM >>stdin; >>^@^E^@^@^@maria^A^A^@^@^@^@I^,¬^@^@^[^@^@^@^@0x9b^A^@^@^@^A^@^@^@^@^G^@^@^@3944825^@^U^@^@^@epay_requests_archive >>^@ >> >> > > > >>things like that. So how can I change this. >> >> > >Use pg_restore to make a text dump script, and then edit that, and then >load it into your newer server. > > > >>For me it will be easier to change the schema (which is in normal text >>format). To remove the field from the base table and to add it at the >>end of each table that inherits the base table. >> >> > >[ shrug... ] If you want to make a permanent change to work around this >one-time problem ... > > regards, tom lane > > > >