Problems with pg_restore - Mailing list pgsql-general

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