Thread: Problem with pg_convert from 8.4 -> 9.0

Problem with pg_convert from 8.4 -> 9.0

From

Karl Denninger

Date:

22 September 2010, 00:11:04

 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.

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

-- Karl

Attachment

karl.vcf

Re: Problem with pg_convert from 8.4 -> 9.0

From

Bruce Momjian

Date:

22 September 2010, 00:16:16

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. +

Re: Problem with pg_convert from 8.4 -> 9.0

From

Karl Denninger

Date:

22 September 2010, 00:33:24

 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

Attachment

karl.vcf

Re: Problem with pg_convert from 8.4 -> 9.0

From

Tom Lane

Date:

22 September 2010, 01:05:29

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