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
