Problem with pg_convert from 8.4 -> 9.0

Karl Denninger wrote:

Uh, is there a way around this problem?

$ bin/pg_upgrade -c -d /usr/local/pgsql-8.4/data -D data -b
/usr/local/pgsql-8.4/bin -B bin
Performing Consistency Checks
-----------------------------
Checking old data directory (/usr/local/pgsql-8.4/data) ok
Checking old bin directory (/usr/local/pgsql-8.4/bin) ok
Checking new data directory (data) ok
Checking new bin directory (bin) ok
Checking for reg* system oid user data types fatal

| Your installation contains one of the reg* data types in
| user tables. These data types reference system oids that
| are not preserved by pg_upgrade, so this cluster cannot
| currently be upgraded. You can remove the problem tables
| and restart the migration. A list of the problem columns
| is in the file:
| /usr/local/pgsql/tables_using_reg.txt

$ more tables_using_reg.txt
Database: marketticker
public.pg_ts_dict.dict_init
public.pg_ts_dict.dict_lexize
public.pg_ts_parser.prs_start
public.pg_ts_parser.prs_nexttoken
public.pg_ts_parser.prs_end
public.pg_ts_parser.prs_headline
public.pg_ts_parser.prs_lextype
Database: ticker
public.pg_ts_dict.dict_init
public.pg_ts_dict.dict_lexize
public.pg_ts_parser.prs_start
public.pg_ts_parser.prs_nexttoken
public.pg_ts_parser.prs_end
public.pg_ts_parser.prs_headline
public.pg_ts_parser.prs_lextype
$

These two databases use the full-text "Tsearch2" module..... I can't
remove these tables either; they're marked "system" and even as a
superuser removing them is not permitted.

I belive you are incorrect. They are in the public schema so I am not
sure how they are "system". They are prefixed with "pg_" but that
should not be a problem.

Does this mean that I have to dump and restore, and can't use convert?
If so that's a real bummer.....

Usually I tell folks to dump/delete/reload those tables and use
pg_upgrade for everything else, as the error message suggests.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 9/21/2010 10:16 PM, Bruce Momjian wrote:

Karl Denninger wrote:

Uh, is there a way around this problem?

$ bin/pg_upgrade -c -d /usr/local/pgsql-8.4/data -D data -b
/usr/local/pgsql-8.4/bin -B bin
Performing Consistency Checks
-----------------------------
Checking old data directory (/usr/local/pgsql-8.4/data) ok
Checking old bin directory (/usr/local/pgsql-8.4/bin) ok
Checking new data directory (data) ok
Checking new bin directory (bin) ok
Checking for reg* system oid user data types fatal

| Your installation contains one of the reg* data types in
| user tables. These data types reference system oids that
| are not preserved by pg_upgrade, so this cluster cannot
| currently be upgraded. You can remove the problem tables
| and restart the migration. A list of the problem columns
| is in the file:
| /usr/local/pgsql/tables_using_reg.txt

$ more tables_using_reg.txt
Database: marketticker
public.pg_ts_dict.dict_init
public.pg_ts_dict.dict_lexize
public.pg_ts_parser.prs_start
public.pg_ts_parser.prs_nexttoken
public.pg_ts_parser.prs_end
public.pg_ts_parser.prs_headline
public.pg_ts_parser.prs_lextype
Database: ticker
public.pg_ts_dict.dict_init
public.pg_ts_dict.dict_lexize
public.pg_ts_parser.prs_start
public.pg_ts_parser.prs_nexttoken
public.pg_ts_parser.prs_end
public.pg_ts_parser.prs_headline
public.pg_ts_parser.prs_lextype
$

These two databases use the full-text "Tsearch2" module..... I can't
remove these tables either; they're marked "system" and even as a
superuser removing them is not permitted.

I belive you are incorrect. They are in the public schema so I am not
sure how they are "system". They are prefixed with "pg_" but that
should not be a problem.

Does this mean that I have to dump and restore, and can't use convert?
If so that's a real bummer.....

Usually I tell folks to dump/delete/reload those tables and use
pg_upgrade for everything else, as the error message suggests.

That appears to be working.... not sure why it didn't before....

Thanks.

-- Karl

Karl Denninger <karl@denninger.net> writes:

$ more tables_using_reg.txt
Database: marketticker
public.pg_ts_dict.dict_init
public.pg_ts_dict.dict_lexize
public.pg_ts_parser.prs_start
public.pg_ts_parser.prs_nexttoken
public.pg_ts_parser.prs_end
public.pg_ts_parser.prs_headline
public.pg_ts_parser.prs_lextype
Database: ticker
public.pg_ts_dict.dict_init
public.pg_ts_dict.dict_lexize
public.pg_ts_parser.prs_start
public.pg_ts_parser.prs_nexttoken
public.pg_ts_parser.prs_end
public.pg_ts_parser.prs_headline
public.pg_ts_parser.prs_lextype
$

Actually you should be able to just drop and forget those tables.
They appear to be carry-forwards from a pre-8.3 installation of
contrib/tsearch2. If so, they don't do anything useful anymore.
(Read the manual's section about migrating from pre-8.3 tsearch.)

These two databases use the full-text "Tsearch2" module..... I can't
remove these tables either; they're marked "system" and even as a
superuser removing them is not permitted.

Possibly you tried to remove the similarly-named system catalogs
(in pg_catalog)?

regards, tom lane