Re: text search configuration missing while migration from 8.3 to 9.4 - Mailing list pgsql-general
| From | Julie Nishimura |
|---|---|
| Subject | Re: text search configuration missing while migration from 8.3 to 9.4 |
| Date | |
| Msg-id | BYAPR08MB50140786DCCBC9FDE7DD0137AC240@BYAPR08MB5014.namprd08.prod.outlook.com Whole thread Raw |
| In response to | Re: text search configuration missing while migration from 8.3 to 9.4 (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: text search configuration missing while migration from 8.3 to 9.4
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: text search configuration missing while migration from 8.3 to 9.4 (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
Oh thank you for answering me!!!
1) This is the 8.3 source:
select * from pg_ts_dict;
dictname | dictnamespace | dictowner | dicttemplate | dictinitoption
---------------------+---------------+-----------+--------------+---------------------------------------------------
simple | 11 | 10 | 3727 |
danish_stem | 11 | 10 | 11282 | language = 'danish', stopwords = 'danish'
dutch_stem | 11 | 10 | 11282 | language = 'dutch', stopwords = 'dutch'
english_stem | 11 | 10 | 11282 | language = 'english', stopwords = 'english'
finnish_stem | 11 | 10 | 11282 | language = 'finnish', stopwords = 'finnish'
french_stem | 11 | 10 | 11282 | language = 'french', stopwords = 'french'
german_stem | 11 | 10 | 11282 | language = 'german', stopwords = 'german'
hungarian_stem | 11 | 10 | 11282 | language = 'hungarian', stopwords = 'hungarian'
italian_stem | 11 | 10 | 11282 | language = 'italian', stopwords = 'italian'
norwegian_stem | 11 | 10 | 11282 | language = 'norwegian', stopwords = 'norwegian'
portuguese_stem | 11 | 10 | 11282 | language = 'portuguese', stopwords = 'portuguese'
romanian_stem | 11 | 10 | 11282 | language = 'romanian'
russian_stem | 11 | 10 | 11282 | language = 'russian', stopwords = 'russian'
spanish_stem | 11 | 10 | 11282 | language = 'spanish', stopwords = 'spanish'
swedish_stem | 11 | 10 | 11282 | language = 'swedish', stopwords = 'swedish'
turkish_stem | 11 | 10 | 11282 | language = 'turkish', stopwords = 'turkish'
english_stem_nostop | 21960 | 10 | 11282 | language = 'english'
hwsplit | 21960 | 10 | 22342 |
hwsplit_only | 21960 | 10 | 22342 | returnreplaced = 'false', keeporig = 'false'(19 rows)
This is the target 9.4:
select * from pg_ts_dict;
dictname | dictnamespace | dictowner | dicttemplate | dictinitoption
-----------------+---------------+-----------+--------------+---------------------------------------------------
simple | 11 | 10 | 3727 |
danish_stem | 11 | 10 | 11592 | language = 'danish', stopwords = 'danish'
dutch_stem | 11 | 10 | 11592 | language = 'dutch', stopwords = 'dutch'
english_stem | 11 | 10 | 11592 | language = 'english', stopwords = 'english'
finnish_stem | 11 | 10 | 11592 | language = 'finnish', stopwords = 'finnish'
french_stem | 11 | 10 | 11592 | language = 'french', stopwords = 'french'
german_stem | 11 | 10 | 11592 | language = 'german', stopwords = 'german'
hungarian_stem | 11 | 10 | 11592 | language = 'hungarian', stopwords = 'hungarian'
italian_stem | 11 | 10 | 11592 | language = 'italian', stopwords = 'italian'
norwegian_stem | 11 | 10 | 11592 | language = 'norwegian', stopwords = 'norwegian'
portuguese_stem | 11 | 10 | 11592 | language = 'portuguese', stopwords = 'portuguese'
romanian_stem | 11 | 10 | 11592 | language = 'romanian'
russian_stem | 11 | 10 | 11592 | language = 'russian', stopwords = 'russian'
spanish_stem | 11 | 10 | 11592 | language = 'spanish', stopwords = 'spanish'
swedish_stem | 11 | 10 | 11592 | language = 'swedish', stopwords = 'swedish'
turkish_stem | 11 | 10 | 11592 | language = 'turkish', stopwords = 'turkish'
(16 rows) As you see, last 3 records for dictnamespace 21960 are missing.
Another thing:
source (8.3):
\dF
List of text search configurations
Schema | Name | Description
------------+------------+------------------------------------------------
pg_catalog | danish | configuration for danish language
pg_catalog | dutch | configuration for dutch language
pg_catalog | english | configuration for english language
pg_catalog | finnish | configuration for finnish language
pg_catalog | french | configuration for french language
pg_catalog | german | configuration for german language
pg_catalog | hungarian | configuration for hungarian language
pg_catalog | italian | configuration for italian language
pg_catalog | norwegian | configuration for norwegian language
pg_catalog | portuguese | configuration for portuguese language
pg_catalog | romanian | configuration for romanian language
pg_catalog | russian | configuration for russian language
pg_catalog | simple | simple configuration
pg_catalog | spanish | configuration for spanish language
pg_catalog | swedish | configuration for swedish language
pg_catalog | turkish | configuration for turkish language
public | hw_english | Hitwise configuration for the English language
(17 rows) target (9.4):
\dF
List of text search configurations
Schema | Name | Description
------------+------------+---------------------------------------
pg_catalog | danish | configuration for danish language
pg_catalog | dutch | configuration for dutch language
pg_catalog | english | configuration for english language
pg_catalog | finnish | configuration for finnish language
pg_catalog | french | configuration for french language
pg_catalog | german | configuration for german language
pg_catalog | hungarian | configuration for hungarian language
pg_catalog | italian | configuration for italian language
pg_catalog | norwegian | configuration for norwegian language
pg_catalog | portuguese | configuration for portuguese language
pg_catalog | romanian | configuration for romanian language
pg_catalog | russian | configuration for russian language
pg_catalog | simple | simple configuration
pg_catalog | spanish | configuration for spanish language
pg_catalog | swedish | configuration for swedish language
pg_catalog | turkish | configuration for turkish language
(16 rows) So, the last record "hw_english" does not show in the target.
This is the parameter in postgresql file, same on both locations.
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
default_text_search_config = 'pg_catalog.english'
Unfortunately, I am not sure about tsearch2, it is new system that I inherited not too long ago. So, the production is still on 8.3, but dev/staging that was built recently, is on 9.4. The engineer who installed it said he used it from Puppet module for postgresql... He is not sure about tsearch2 module either. Do you know how can I find it? Or how can I install it? It seems like some gin indices are missing. I need to find out what is missing...
THANK YOU for your help
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, April 16, 2019 1:02 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org
Subject: Re: text search configuration missing while migration from 8.3 to 9.4
Sent: Tuesday, April 16, 2019 1:02 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org
Subject: Re: text search configuration missing while migration from 8.3 to 9.4
On 4/16/19 12:02 PM, Julie Nishimura wrote:
> Correction: tables are not missing, but some records from the tables
> missing. How can we migrate them properly from 8.3 to 9.4?
This is going to need some more info:
1) What records?
2) 8.3 was the version where text search was integrated into core. Where
you using tsearch2 before you moved to 8.3?
3) If the answer to 2) is yes, did you have the contrib/tsearch2 module
installed in 8.3 for backwards compatibility?
4) How was the 9.4 version installed?
4) If the answer to 3) is yes did you install the contrib/tsearch2 in
the 9.4 cluster?
>
> Sent from my iPhone
>
> On Apr 16, 2019, at 10:32 AM, Julie Nishimura <juliezain@hotmail.com
> <mailto:juliezain@hotmail.com>> wrote:
>
>> Hello, we recenlty created fresh install of 9.4, created new dbs, and
>> pg_dumped and restored correspondent dbs from 8.3 (pg_dump|psql, being
>> on target). Now we've noticed that some of `pg_catalog.pg_ts_config`
>> and pg_ts_dict tables are missing, making text search on 9.4 very
>> slow. We tried to insert missing records from pg_catalog.pg_ts_config,
>> but it does not help. What is the proper procedure for it? Please
>> help, thanks!
--
Adrian Klaver
adrian.klaver@aklaver.com
> Correction: tables are not missing, but some records from the tables
> missing. How can we migrate them properly from 8.3 to 9.4?
This is going to need some more info:
1) What records?
2) 8.3 was the version where text search was integrated into core. Where
you using tsearch2 before you moved to 8.3?
3) If the answer to 2) is yes, did you have the contrib/tsearch2 module
installed in 8.3 for backwards compatibility?
4) How was the 9.4 version installed?
4) If the answer to 3) is yes did you install the contrib/tsearch2 in
the 9.4 cluster?
>
> Sent from my iPhone
>
> On Apr 16, 2019, at 10:32 AM, Julie Nishimura <juliezain@hotmail.com
> <mailto:juliezain@hotmail.com>> wrote:
>
>> Hello, we recenlty created fresh install of 9.4, created new dbs, and
>> pg_dumped and restored correspondent dbs from 8.3 (pg_dump|psql, being
>> on target). Now we've noticed that some of `pg_catalog.pg_ts_config`
>> and pg_ts_dict tables are missing, making text search on 9.4 very
>> slow. We tried to insert missing records from pg_catalog.pg_ts_config,
>> but it does not help. What is the proper procedure for it? Please
>> help, thanks!
--
Adrian Klaver
adrian.klaver@aklaver.com
pgsql-general by date: