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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2007-03-16 16:28:51 Re: Problem migrating from 8.0.3 to 8.2.3
Previous Message olive Mckenzie 2007-03-16 15:51:18 unsubscribe