Migrating from 5.4.2 to 5.5 - phrase search - heavy ALTER TABLE queries

Hi all

we are performing a migration from 5.4.2 to 5.5 and following the steps here https://doc.nuxeo.com/display/ADMINDOC/Upgrade+from+5.4.2+to+5.5 we've found that to activate phrase search, we have to follow the steps here https://jira.nuxeo.com/browse/NXP-5689

Everithing worked as expected but, when we are upgrading fulltext fields using the custom function tsvector_to_text it spents a huge period of time upgrading binatytext field See this in postgres log file: 2016-02-22 08:23:59 CET [20994]: [1-1] LOG: duration: 471476742.785 ms statement: ALTER TABLE fulltext ALTER binarytext TYPE text USING tsvector_to_text(binarytext);

Which is more than 5,45 days.. but the query is still working. The first ALTER TABLE worked fine (2h aprox.) ALTER TABLE fulltext ALTER simpletext TYPE text USING tsvector_to_text(simpletext);

But this second one is spending too much time. Any suggestion? Thanks

0 votes

1 answers

805 views

ANSWER



It seems this workaround only apply if you are migrating from a previous 5.3.2 version. If you are migrating from a 5.4.2 to 5.5, try the following steps.

  1. Stop your instance
  2. Create a new database using this command: create database nuxeo55 owner nuxeo encoding 'UTF8' template nuxeo542; (replace nuxeo542 with the name of your 5.4.2 database
  3. Download, uncompress and run Nuxeo 5.5.
  4. Go to the wizard and in the step of database config, put the data of your new database
  5. Complete the wizard and wait the restart
  6. Go to login page and try to log with your credentials

I used this method several times without problems.

Kind Regards

0 votes