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

Problem migrating from 8.0.3 to 8.2.3

From: "Anton Pikhteryev" <apikhteryev(at)sandvine(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Problem migrating from 8.0.3 to 8.2.3
Date: 2007-03-16 15:59:44
Message-ID: F489AB573A749146B33461ECE080913A0108BEF4@EXCHANGE-1.sandvine.com (view raw or flat)
Thread:
Lists: pgsql-admin
I was following migration document
http://www.postgresql.org/docs/8.2/interactive/migration.html and hit
the problem I can't solve.

Basically my goal is to migrate data from 8.0.3 to 8.2.3

The error I am getting is:

ERROR:  type "char" is not a domain

Doing: 

pg_dumpall -p 5432 | psql -d postgres -p 6543


....

CREATE TABLE
ALTER TABLE public.vlan_switch OWNER TO labadmin;
ALTER TABLE
COMMENT ON TABLE vlan_switch IS 'which vlans are configured on which
switches';
COMMENT
ALTER TABLE public.vlan_config OWNER TO labadmin;
ALTER TABLE
COPY annotation_type (an_type, an_description, an_ack) FROM stdin;
COPY assert_run (ar_id, icr_id, assert_timestamp, assert_result,
assert_text) FROM stdin;
COPY attribute (attrib_id, attrib_name, attrib_weight, attrib_desc) FROM
stdin;
COPY attribute_conversion (attrib_id, attrib_value, attrib_text_value)
FROM stdin;
COPY clap_file_data (fileid, linenum, line) FROM stdin;
COPY clap_file_info (fileid, filename, created, "owner") FROM stdin;

ERROR:  type "char" is not a domain

CONTEXT:  COPY clap_file_info, line 1, column owner: "mfgtest"
COPY event_detail_type (event_detail_type_id, event_detail_type_name,
event_detail_type_desc) FROM stdin;
COPY event_occurrence (event_occurrence_id, event_id, event_timestamp,
testrun_id, user_id) FROM stdin;

ERROR:  type "char" is not a domain

Table structures:

labdb=# select * from clap_file_info limit 1;
-[ RECORD 1 ]---------------------------
fileid   | 332160
filename | ProfileMgr-205442.cfg
created  | 2004-07-16 15:03:39.494271-04
owner    | mfgtest

labdb=# \d+ clap_file_info
Table "public.clap_file_info"
-[ RECORD 1
]-------------------------------------------------------------------
Column      | fileid
Type        | integer
Modifiers   | not null default
nextval('public.clap_file_info_fileid_seq'::text)
Description |
-[ RECORD 2
]-------------------------------------------------------------------
Column      | filename
Type        | text
Modifiers   | not null
Description |
-[ RECORD 3
]-------------------------------------------------------------------
Column      | created
Type        | timestamp with time zone
Modifiers   | not null default ('now'::text)::timestamp(6) with time
zone
Description |
-[ RECORD 4
]-------------------------------------------------------------------
Column      | owner
Type        | valid_user
Modifiers   | not null
Description |

Indexes:
    "clap_file_info_pkey" PRIMARY KEY, btree (fileid)
    "unique_filename" UNIQUE, btree (filename)
    "cfi_owner_created" btree ("owner", created)
Has OIDs: no

labdb=# select * from event_occurrence limit 1;
-[ RECORD 1 ]-------+------------------------------
event_occurrence_id | 3
event_id            | 402
event_timestamp     | 2006-03-09 13:46:49.526573-05
testrun_id          |
user_id             | mfgtest

labdb=# \d+ event_occurrence
Table "public.event_occurrence"
-[ RECORD 1
]-------------------------------------------------------------------
---------------
Column      | event_occurrence_id
Type        | integer
Modifiers   | not null default
nextval('public.event_occurrence_event_occurrence
_id_seq'::text)
Description | unique id for event occurrences
-[ RECORD 2
]-------------------------------------------------------------------
---------------
Column      | event_id
Type        | serial_ref
Modifiers   | not null
Description | unique id associated with an event
-[ RECORD 3
]-------------------------------------------------------------------
---------------
Column      | event_timestamp
Type        | timestamp with time zone
Modifiers   | not null default ('now'::text)::timestamp(6) with time
zone
Description | timestamp of event occurrence
-[ RECORD 4
]-------------------------------------------------------------------
---------------
Column      | testrun_id
Type        | serial_ref
Modifiers   |
Description | unique testrun in which the event occurred (could be null)
-[ RECORD 5
]-------------------------------------------------------------------
---------------
Column      | user_id
Type        | valid_user
Modifiers   | not null
Description | user who experienced the occurrence of the event

Indexes:
    "event_occurrence_pkey" PRIMARY KEY, btree (event_occurrence_id)
    "event_occurrence_subcat_idx" btree (event_id)
    "event_occurrence_testrun_id_idx" btree (testrun_id)
    "event_occurrence_timestamp_idx" btree (event_timestamp)
Foreign-key constraints:
    "event_occurrence_testrun_id_fkey" FOREIGN KEY (testrun_id)
REFERENCES test_
run(testrun_id) ON UPDATE CASCADE ON DELETE CASCADE
    "event_occurrence_event_id_fkey" FOREIGN KEY (event_id) REFERENCES
event(eve
nt_id) ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no

I am thinking that it has something to do with usesysid not being
consistent for valid_user type from 8.0.3 to 8.2.3.

On 8.0.3:

labdb=# select * from pg_user where usename='mfgtest';
-[ RECORD 1 ]---------
usename     | mfgtest
usesysid    | 11302
usecreatedb | f
usesuper    | f
usecatupd   | f
passwd      | ********
valuntil    |
useconfig   |

On 8.2.3:

labdb=# select * from pg_user where usename='mfgtest';
-[ RECORD 1 ]---------
usename     | mfgtest
usesysid    | 16612
usecreatedb | f
usesuper    | f
usecatupd   | f
passwd      | ********
valuntil    |
useconfig   |

Any suggesting on how to solve it or where to look or what to try?

Thank you,

Anton Pikhteryev


Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2007-03-16 16:28:51
Subject: Re: Problem migrating from 8.0.3 to 8.2.3
Previous:From: olive MckenzieDate: 2007-03-16 15:51:18
Subject: unsubscribe

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