Thread: Problem migrating from 8.0.3 to 8.2.3
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
"Anton Pikhteryev" <apikhteryev@sandvine.com> writes:
> 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"
You have removed the parts of the dump that show the creation of
this table. Please try again.
> 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.
Highly unlikely. The error message must be coming from domain_in,
but why is that getting invoked? I want to see the declaration of
that table and any data types or constraints it's using.
regards, tom lane
There is that part and doesn't have any errors:
CREATE TABLE clap_file_info (
fileid integer DEFAULT
nextval('public.clap_file_info_fileid_seq'::text) NOT NULL,
filename text NOT NULL,
created timestamp with time zone DEFAULT ('now'::text)::timestamp(6)
with time zone NOT NULL,
"owner" valid_user NOT NULL
);
CREATE TABLE
ALTER TABLE public.clap_file_info OWNER TO labadmin;
...
CREATE TABLE event_occurrence (
event_occurrence_id integer DEFAULT
nextval('public.event_occurrence_event_occurrence_id_seq'::text) NOT
NULL,
event_id serial_ref NOT NULL,
event_timestamp timestamp with time zone DEFAULT
('now'::text)::timestamp(6) with time zone NOT NULL,
testrun_id serial_ref,
user_id valid_user NOT NULL
);
CREATE TABLE
ALTER TABLE public.event_occurrence OWNER TO labadmin;
ALTER TABLE
CREATE SEQUENCE event_occurrence_event_occurrence_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
CREATE SEQUENCE
ALTER TABLE public.event_occurrence_event_occurrence_id_seq OWNER TO
labadmin;
ALTER TABLE
ALTER SEQUENCE event_occurrence_event_occurrence_id_seq OWNED BY
event_occurrence.event_occurrence_id;
ALTER SEQUENCE
Absolutely no errors when it is created.
BTW:
labdb=# \dT valid_user
List of data types
Schema | Name | Description
--------+------------+----------------------------
public | valid_user | valid PostgreSQL user name
(1 row)
labdb=# \dT+ valid_user
List of data types
Schema | Name | Internal name | Size | Description
--------+------------+---------------+------+---------------------------
-
public | valid_user | valid_user | 64 | valid PostgreSQL user name
(1 row)
Did I miss anything?
Anton Pikhteryev
Network Administrator, Tools Group
Sandvine Incorporated
Phone: 519.880.2400 ext. 2923
http://www.sandvine.com
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, March 16, 2007 12:29 PM
> To: Anton Pikhteryev
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Problem migrating from 8.0.3 to 8.2.3
>
> "Anton Pikhteryev" <apikhteryev@sandvine.com> writes:
> > 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"
>
> You have removed the parts of the dump that show the creation
> of this table. Please try again.
>
> > 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.
>
> Highly unlikely. The error message must be coming from
> domain_in, but why is that getting invoked? I want to see
> the declaration of that table and any data types or
> constraints it's using.
>
> regards, tom lane
>
"Anton Pikhteryev" <apikhteryev@sandvine.com> writes:
> There is that part and doesn't have any errors:
> CREATE TABLE clap_file_info (
> fileid integer DEFAULT
> nextval('public.clap_file_info_fileid_seq'::text) NOT NULL,
> filename text NOT NULL,
> created timestamp with time zone DEFAULT ('now'::text)::timestamp(6)
> with time zone NOT NULL,
> "owner" valid_user NOT NULL
> );
What is a valid_user? (No, I don't want \dT output, I want the
definition of the type.)
regards, tom lane
On the destination box:
#\dD+ valid_user
List of domains
Schema | Name | Type | Modifier |
Check
--------+------------+------+--------------------------+----------------
-----------------------------------------------------
public | valid_user | name | default "session_user"() | CHECK
(has_table_privilege(VALUE, 'pg_user'::text, 'SELECT'::text))
(1 row)
On the source:
# \dD+ valid_user
List of domains
Schema | Name | Type | Modifier
--------+------------+------+--------------------------
public | valid_user | name | default "session_user"()
(1 row)
I created valid_user domain on the old system (8.0.3) a long time ago
as:
CREATE DOMAIN valid_user
AS name -- pg_catalog.pg_user.usename%TYPE
DEFAULT SESSION_USER
CONSTRAINT user_exists
CHECK (has_table_privilege(VALUE,'pg_user','SELECT'));
COMMENT ON DOMAIN valid_user IS 'valid PostgreSQL user name';
I was also thinking that there is no need to recreate the domain again
as a part of migration and hoping that pg_dumpall will take care of it.
Anton Pikhteryev
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, March 16, 2007 3:34 PM
> To: Anton Pikhteryev
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Problem migrating from 8.0.3 to 8.2.3
>
> "Anton Pikhteryev" <apikhteryev@sandvine.com> writes:
> > There is that part and doesn't have any errors:
> > CREATE TABLE clap_file_info (
> > fileid integer DEFAULT
> > nextval('public.clap_file_info_fileid_seq'::text) NOT NULL,
> > filename text NOT NULL,
> > created timestamp with time zone DEFAULT
> > ('now'::text)::timestamp(6) with time zone NOT NULL,
> > "owner" valid_user NOT NULL
> > );
>
> What is a valid_user? (No, I don't want \dT output, I want
> the definition of the type.)
>
> regards, tom lane
>
BTW creating domain didn't throw any error on the destination server
(8.2.3), but I found that after migration permissions are not
consistent:
...
COMMENT
CREATE DOMAIN valid_user AS name DEFAULT "session_user"()
CONSTRAINT user_exists CHECK (has_table_privilege(VALUE,
'pg_user'::text, 'SELECT'::text));
CREATE DOMAIN
ALTER DOMAIN public.valid_user OWNER TO labadmin;
ALTER DOMAIN
COMMENT ON DOMAIN valid_user IS 'valid PostgreSQL user name';
COMMENT
...
New server (8.2.3)
=> select has_table_privilege('mfgtest','clap_file_info','SELECT');
has_table_privilege
---------------------
f
(1 row)
Old one (8.0.3):
# select has_table_privilege('mfgtest','clap_file_info','SELECT');
has_table_privilege
---------------------
t
(1 row)
Anton Pikhteryev
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Anton
> Pikhteryev
> Sent: Monday, March 19, 2007 9:52 AM
> To: Tom Lane
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Problem migrating from 8.0.3 to 8.2.3
>
> On the destination box:
>
> #\dD+ valid_user
> List of domains
> Schema | Name | Type | Modifier |
> Check
> --------+------------+------+--------------------------+------
> ----------
> -----------------------------------------------------
> public | valid_user | name | default "session_user"() |
> CHECK (has_table_privilege(VALUE, 'pg_user'::text, 'SELECT'::text))
> (1 row)
>
> On the source:
>
> # \dD+ valid_user
> List of domains
> Schema | Name | Type | Modifier
> --------+------------+------+--------------------------
> public | valid_user | name | default "session_user"()
> (1 row)
>
>
> I created valid_user domain on the old system (8.0.3) a long time ago
> as:
>
> CREATE DOMAIN valid_user
> AS name -- pg_catalog.pg_user.usename%TYPE
> DEFAULT SESSION_USER
> CONSTRAINT user_exists
> CHECK (has_table_privilege(VALUE,'pg_user','SELECT'));
> COMMENT ON DOMAIN valid_user IS 'valid PostgreSQL user name';
>
> I was also thinking that there is no need to recreate the
> domain again as a part of migration and hoping that
> pg_dumpall will take care of it.
>
>
>
> Anton Pikhteryev
>
>
>
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Friday, March 16, 2007 3:34 PM
> > To: Anton Pikhteryev
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Problem migrating from 8.0.3 to 8.2.3
> >
> > "Anton Pikhteryev" <apikhteryev@sandvine.com> writes:
> > > There is that part and doesn't have any errors:
> > > CREATE TABLE clap_file_info (
> > > fileid integer DEFAULT
> > > nextval('public.clap_file_info_fileid_seq'::text) NOT NULL,
> > > filename text NOT NULL,
> > > created timestamp with time zone DEFAULT
> > > ('now'::text)::timestamp(6) with time zone NOT NULL,
> > > "owner" valid_user NOT NULL
> > > );
> >
> > What is a valid_user? (No, I don't want \dT output, I want the
> > definition of the type.)
> >
> > regards, tom lane
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
"Anton Pikhteryev" <apikhteryev@sandvine.com> writes:
> I created valid_user domain on the old system (8.0.3) a long time ago
> as:
> CREATE DOMAIN valid_user
> AS name -- pg_catalog.pg_user.usename%TYPE
> DEFAULT SESSION_USER
> CONSTRAINT user_exists
> CHECK (has_table_privilege(VALUE,'pg_user','SELECT'));
Ah. That seems a rather strange way to check that a user exists,
but anyway the missing bit of information was that the domain is
over type "name" which is subscriptable. You need the attached
patch (which will be in 8.2.4).
regards, tom lane
Index: src/backend/utils/cache/lsyscache.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/cache/lsyscache.c,v
retrieving revision 1.138
diff -c -r1.138 lsyscache.c
*** src/backend/utils/cache/lsyscache.c 4 Oct 2006 00:30:00 -0000 1.138
--- src/backend/utils/cache/lsyscache.c 19 Mar 2007 15:56:30 -0000
***************
*** 1366,1375 ****
/*
* Array types get their typelem as parameter; everybody else gets their
! * own type OID as parameter. (This is a change from 8.0, in which only
! * composite types got their own OID as parameter.)
*/
! if (OidIsValid(typeStruct->typelem))
return typeStruct->typelem;
else
return HeapTupleGetOid(typeTuple);
--- 1366,1375 ----
/*
* Array types get their typelem as parameter; everybody else gets their
! * own type OID as parameter. (As of 8.2, domains must get their own OID
! * even if their base type is an array.)
*/
! if (typeStruct->typtype == 'b' && OidIsValid(typeStruct->typelem))
return typeStruct->typelem;
else
return HeapTupleGetOid(typeTuple);
"Anton Pikhteryev" <apikhteryev@sandvine.com> writes:
> ... but I found that after migration permissions are not
> consistent:
Again, you have not provided enough information to let someone else
replicate the problem.
regards, tom lane