Обсуждение: 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!
Correction: tables are not missing, but some records from the tables missing. How can we migrate them properly from 8.3 to 9.4?
Sent from my iPhone
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
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
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
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...
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
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
> 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
Thank you guys. I've dumped the schema and was able to load it properly this time. There were some missing text search dictionaries, fts templates and functions. I was able to recreate it. Now is my question - do I need to reload the data?
Thanks!
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, April 16, 2019 5:14 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 5:14 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 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
> 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
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
Thank you. Is there a way to find out if I have tables with tsvectors or indexes? I have hundreds of tables...
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
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 search dictionaries, fts templates and functions. I was able to recreate it. Now is my question - do I need to reload the data?
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
> Thank you guys. I've dumped the schema and was able to load it properly this time. There were some missing text search dictionaries, fts templates and functions. I was able to recreate it. Now is my question - do I need to reload the data?
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