Обсуждение: text search configuration missing while migration from 8.3 to 9.4

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!

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

Julie Nishimura <juliezain@hotmail.com> writes:
> 1) This is the 8.3 source:
> select * from pg_ts_dict;
> ...
>  english_stem_nostop |         21960 |        10 |        11282 | language = 'english'
>  hwsplit             |         21960 |        10 |        22342 |
>  hwsplit_only        |         21960 |        10 |        22342 | returnreplaced = 'false', keeporig = 'false'

OK, so you have some non-built-in text search objects in your 8.3
installation.  That's fine, but the dump/restore process should certainly
have included commands to recreate those objects in the new database.
Did you ignore any errors during the dump or restore?  (Trying to run
the restore as a non-superuser could well have led to failure to restore
these objects, for instance, but you'd have gotten errors.)  Did you perhaps
do a selective dump or restore?  (For example, excluding whatever schema
21960 is would've led to excluding these objects.)

If you're not real sure about the errors angle, I'd suggest retrying
the process to see.  You could use "pg_dump -s" to dump only schema
not data, so as to make that faster --- any relevant errors should
still occur.

I'm a bit confused by the initial report, too.  If you are missing
the text search configuration(s) your application needs, that should
lead to query errors, not just things running slower.  What exactly
is going wrong?

> Unfortunately, I am not sure about tsearch2,

psql's "\dx" would tell you what extensions are installed in the
8.3 database.

            regards, tom lane

On 4/16/19 2:46 PM, Tom Lane wrote:
> Julie Nishimura <juliezain@hotmail.com> writes:
>> 1) This is the 8.3 source:
>> select * from pg_ts_dict;
>> ...
>>   english_stem_nostop |         21960 |        10 |        11282 | language = 'english'
>>   hwsplit             |         21960 |        10 |        22342 |
>>   hwsplit_only        |         21960 |        10 |        22342 | returnreplaced = 'false', keeporig = 'false'
> 
> OK, so you have some non-built-in text search objects in your 8.3
> installation.  That's fine, but the dump/restore process should certainly
> have included commands to recreate those objects in the new database.
> Did you ignore any errors during the dump or restore?  (Trying to run
> the restore as a non-superuser could well have led to failure to restore
> these objects, for instance, but you'd have gotten errors.)  Did you perhaps
> do a selective dump or restore?  (For example, excluding whatever schema
> 21960 is would've led to excluding these objects.)
> 
> If you're not real sure about the errors angle, I'd suggest retrying
> the process to see.  You could use "pg_dump -s" to dump only schema
> not data, so as to make that faster --- any relevant errors should
> still occur.
> 
> I'm a bit confused by the initial report, too.  If you are missing
> the text search configuration(s) your application needs, that should
> lead to query errors, not just things running slower.  What exactly
> is going wrong?
> 
>> Unfortunately, I am not sure about tsearch2,
> 
> psql's "\dx" would tell you what extensions are installed in the
> 8.3 database.

Unfortunately that does not exist in 8.3. I think because CREATE 
EXTENSION did not appear until 9.1

> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/16/19 1:55 PM, Julie Nishimura wrote:
> Oh thank you for answering me!!!
> 

> 
> 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)
> 


The only thing I can find searching that might be relevant is:

https://en.wikipedia.org/wiki/Hitwise

Does that ring any bells?

> 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'
> 
> 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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/16/19 4:22 PM, Julie Nishimura wrote:
> Adrian, Hitwise is the name of our company. Unfortunately, it does not 

Aah that would explain it.

> help to find how I can migrate missing search config or install whatever 
> is on 8.3. I did not restore the entire database (due to its large 
> size), I've dumped and restored just about 200 hundred tables they 
> needed on staging. Perhaps, some functions need to be migrated as well, 
> just do not know where to look...

I would start by looking at the queries that where running slowly and 
see what they where calling.

Also in the 8.3 database using psql:

\df
if you want more info:
\df+

That will list the functions installed in the database. It will be a 
long list. You can reduce by using a pattern. For more info see:

https://www.postgresql.org/docs/8.3/app-psql.html

> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Tuesday, April 16, 2019 3:08 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 1:55 PM, Julie Nishimura wrote:
>> Oh thank you for answering me!!!
>> 
> 
>> 
>> 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)
>> 
> 
> 
> The only thing I can find searching that might be relevant is:
> 
> https://en.wikipedia.org/wiki/Hitwise
> 
> Does that ring any bells?
> 
>> 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'
>> 
>> 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
>> 
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/16/19 4:22 PM, Julie Nishimura wrote:
> Adrian, Hitwise is the name of our company. Unfortunately, it does not 
> help to find how I can migrate missing search config or install whatever 
> is on 8.3. I did not restore the entire database (due to its large 
> size), I've dumped and restored just about 200 hundred tables they 
> needed on staging. Perhaps, some functions need to be migrated as well, 
> just do not know where to look...
> 

To add to my previous post, you might try Tom's suggestion of just 
dumping the schema from the 8.3 database and restoring that to a 9.4 
database(FYI 9.4 will be the next version to go EOL, so it might be 
prudent to upgrade to a more recent version). Then you could load data 
into the staging tables and see what happens.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Julie Nishimura <juliezain@hotmail.com> writes:
> Thank you guys. I've dumped the schema and was able to load it properly this time. There were some missing text
searchdictionaries, fts templates and functions. I was able to recreate it. Now is my question - do I need to reload
thedata? 

The base textual data should be fine, but if you've got derived
tsvector columns or indexes, you might need to recompute those.

            regards, tom lane

Julie Nishimura <juliezain@hotmail.com> writes:
> Thank you. Is there a way to find out if I have tables with tsvectors or indexes? I have hundreds of tables...

Search the system catalogs.  I'd probably do it like this:

select attrelid::regclass, attname from pg_attribute
  where atttypid = 'tsvector'::regtype;

but you could also devise some more-portable query involving
the information_schema ...

            regards, tom lane

On 4/16/19 10:51 PM, Julie Nishimura wrote:
> Thank you. Is there a way to find out if I have tables with tsvectors or 
> indexes? I have hundreds of tables...

I'm afraid I know the answers, but I will ask anyway:

Is there any documentation to go along with the database?

Or, is the schema in some form of version control that you can search?

> 
> ------------------------------------------------------------------------
> *From:* Tom Lane <tgl@sss.pgh.pa.us>
> *Sent:* Tuesday, April 16, 2019 9:45 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; pgsql-general@lists.postgresql.org
> *Subject:* Re: text search configuration missing while migration from 
> 8.3 to 9.4
> Julie Nishimura <juliezain@hotmail.com> writes:
>> Thank you guys. I've dumped the schema and was able to load it properly this time. There were some missing text
searchdictionaries, fts templates and functions. I was able to recreate it. Now is my question - do I need to reload
thedata?
 
> 
> The base textual data should be fine, but if you've got derived
> tsvector columns or indexes, you might need to recompute those.
> 
>                          regards, tom lane


-- 
Adrian Klaver
adrian.klaver@aklaver.com