Problem migrating from 8.0.3 to 8.2.3

Поиск
Список
Период
Сортировка
От Anton Pikhteryev
Тема Problem migrating from 8.0.3 to 8.2.3
Дата
Msg-id F489AB573A749146B33461ECE080913A0108BEF4@EXCHANGE-1.sandvine.com
обсуждение исходный текст
Ответы Re: Problem migrating from 8.0.3 to 8.2.3
Список 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

В списке pgsql-admin по дате отправления:

Предыдущее
От: olive Mckenzie
Дата:
Сообщение: unsubscribe
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Problem migrating from 8.0.3 to 8.2.3