[rt-users] Problem with database upgrade - PostgreSQL, RT 3.8.9 -> 4.0.0

hubert depesz lubaczewski depesz at depesz.com
Sun May 1 13:56:02 EDT 2011


hi,
So, my rt3 database is called rt, and contains:

(depesz at localhost:5900) 19:47:55 [rt] 
$ \d
                       List of relations
 Schema |               Name                |   Type   | Owner 
--------+-----------------------------------+----------+-------
 public | acl                               | table    | rt
 public | acl_id_seq                        | sequence | rt
 public | attachments                       | table    | rt
 public | attachments_id_seq                | sequence | rt
 public | attributes                        | table    | rt
 public | attributes_id_seq                 | sequence | rt
 public | cachedgroupmembers                | table    | rt
 public | cachedgroupmembers_id_seq         | sequence | rt
 public | customfields                      | table    | rt
 public | customfields_id_seq               | sequence | rt
 public | customfieldvalues                 | table    | rt
 public | customfieldvalues_id_seq          | sequence | rt
 public | fm_articles                       | table    | rt
 public | fm_articles_id_seq                | sequence | rt
 public | fm_classes                        | table    | rt
 public | fm_classes_id_seq                 | sequence | rt
 public | fm_objecttopics                   | table    | rt
 public | fm_objecttopics_id_seq            | sequence | rt
 public | fm_topics                         | table    | rt
 public | fm_topics_id_seq                  | sequence | rt
 public | groupmembers                      | table    | rt
 public | groupmembers_id_seq               | sequence | rt
 public | groups                            | table    | rt
 public | groups_id_seq                     | sequence | rt
 public | jobs_done_monthly                 | view     | rt
 public | jobs_done_monthly_by_ticket       | view     | rt
 public | jobs_done_monthly_by_transactions | view     | rt
 public | links                             | table    | rt
 public | links_id_seq                      | sequence | rt
 public | objectcustomfields                | table    | rt
 public | objectcustomfields_id_s           | sequence | rt
 public | objectcustomfieldvalues           | table    | rt
 public | objectcustomfieldvalues_id_s      | sequence | rt
 public | principals                        | table    | rt
 public | principals_id_seq                 | sequence | rt
 public | queues                            | table    | rt
 public | queues_id_seq                     | sequence | rt
 public | scripactions                      | table    | rt
 public | scripactions_id_seq               | sequence | rt
 public | scripconditions                   | table    | rt
 public | scripconditions_id_seq            | sequence | rt
 public | scrips                            | table    | rt
 public | scrips_id_seq                     | sequence | rt
 public | sessions                          | table    | rt
 public | templates                         | table    | rt
 public | templates_id_seq                  | sequence | rt
 public | tickets                           | table    | rt
 public | tickets_id_seq                    | sequence | rt
 public | transactions                      | table    | rt
 public | transactions_id_seq               | sequence | rt
 public | users                             | table    | rt
 public | users_id_seq                      | sequence | rt
(52 rows)

database for rt4 is done using:

=$ psql -c "create database rt4 with template template0 owner rt;"
CREATE DATABASE

=$ pg_dump rt | psql -v ON_ERROR_STOP=1 -d rt4
...

which works fine.

Now, after doing make install in rt4 source directory (installation to /opt/rt4, previous was /opt/rt3), I changed database settings in copied rt_siteconfig, and ran db upgrade. effect:

=# /opt/rt4/sbin/rt-setup-database --dba postgres --prompt-for-dba-password --action upgrade
In order to create or update your RT database, this script needs to connect to your  Pg instance on localhost as postgres
Please specify that user's database password below. If the user has no database
password, just press return.

Password: 
Working with:
Type:   Pg
Host:   localhost
Name:   rt4
User:   rt
DBA:    postgres
Enter RT version you're upgrading from: 3.8.9

Going to apply following upgrades:
* 3.9.1
* 3.9.2
* 3.9.3
* 3.9.5
* 3.9.6
* 3.9.7
* 3.9.8
* 4.0.0rc2
* 4.0.0rc4
* 4.0.0rc7

Enter RT version if you want to stop upgrade at some point,
  or leave it blank if you want apply above upgrades: 

IT'S VERY IMPORTANT TO BACK UP BEFORE THIS STEP

Proceed [y/N]:y
Processing 3.9.1
Now inserting data.
Processing 3.9.2
Now inserting data.
Processing 3.9.3
Now populating database schema.
Processing 3.9.5
Now populating database schema.
Processing 3.9.6
Now populating database schema.
Processing 3.9.7
Now populating database schema.
Now inserting data.
[Sun May  1 17:52:26 2011] [warning]: DBD::Pg::st execute failed: ERROR:  column "basedon" is of type integer but expression is of type text
LINE 1: UPDATE CustomFields SET BasedOn = (SELECT Content FROM Attri...
                                          ^
HINT:  You will need to rewrite or cast the expression. at /usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 509. (/usr/share/perl5/DBIx/SearchBuilder/Handle.pm:509)
[Sun May  1 17:52:26 2011] [warning]: RT::Handle=HASH(0x5055568) couldn't execute the query 'UPDATE CustomFields SET BasedOn = (SELECT Content FROM Attributes WHERE Name = ? AND ObjectType = ? AND CustomFields.id = Attributes.ObjectId)' at /usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 522
        DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x5055568)', 'UPDATE CustomFields SET BasedOn = (SELECT Content FROM Attrib...', 'BasedOn', 'RT::CustomField') called at ./etc/upgrade/3.9.7/content line 15
        RT::Handle::__ANON__('CustomFields', 'RT::CustomField', 'BasedOn') called at ./etc/upgrade/3.9.7/content line 35
        RT::Handle::__ANON__() called at /opt/rt4/sbin/../lib/RT/Handle.pm line 753
        eval {...} called at /opt/rt4/sbin/../lib/RT/Handle.pm line 753
        RT::Handle::InsertData('RT::Handle=HASH(0x5055568)', './etc/upgrade/3.9.7/content', undef) called at /opt/rt4/sbin/rt-setup-database line 285
        main::action_insert('prompt-for-dba-password', 1, 'datafile', undef, 'action', 'upgrade', 'datadir', './etc/upgrade/3.9.7', 'backcompat', ...) called at /opt/rt4/sbin/rt-setup-database line 387
        main::action_upgrade('prompt-for-dba-password', 1, 'action', 'upgrade', 'dba', 'postgres') called at /opt/rt4/sbin/rt-setup-database line 190 (/usr/share/perl/5.10/Carp.pm:47)
[Sun May  1 17:52:26 2011] [error]: Failed to move BasedOn on RT::CustomField from Attributes into CustomFields table (./etc/upgrade/3.9.7/content:17)
Processing 3.9.8
Now populating database schema.
Now inserting data.
[Sun May  1 17:52:31 2011] [error]: You appear to be upgrading from RTFM 2.0 - We don't support upgrading this old of an RTFM yet (./etc/upgrade/3.9.8/content:12)
[Sun May  1 17:52:31 2011] [error]: We found RTFM tables in your database.  Checking for content. (./etc/upgrade/3.9.8/content:15)
[Sun May  1 17:52:31 2011] [error]: You appear to have RTFM Articles.  You can upgrade using the etc/upgrade/upgrade-articles script.  Read more about it in UPGRADING (./etc/upgrade/3.9.8/content:20)
Processing 4.0.0rc2
Processing 4.0.0rc4
Now populating database schema.
Processing 4.0.0rc7
Now inserting data.
Done.


Afterwards, I have in rt4 database:

$ \d
                        List of relations
 Schema |               Name                |   Type   |  Owner   
--------+-----------------------------------+----------+----------
 public | acl                               | table    | rt
 public | acl_id_seq                        | sequence | rt
 public | articles                          | table    | postgres
 public | articles_id_seq                   | sequence | postgres
 public | attachments                       | table    | rt
 public | attachments_id_seq                | sequence | rt
 public | attributes                        | table    | rt
 public | attributes_id_seq                 | sequence | rt
 public | cachedgroupmembers                | table    | rt
 public | cachedgroupmembers_id_seq         | sequence | rt
 public | classes                           | table    | postgres
 public | classes_id_seq                    | sequence | postgres
 public | customfields                      | table    | rt
 public | customfields_id_seq               | sequence | rt
 public | customfieldvalues                 | table    | rt
 public | customfieldvalues_id_seq          | sequence | rt
 public | fm_articles                       | table    | rt
 public | fm_articles_id_seq                | sequence | rt
 public | fm_classes                        | table    | rt
 public | fm_classes_id_seq                 | sequence | rt
 public | fm_objecttopics                   | table    | rt
 public | fm_objecttopics_id_seq            | sequence | rt
 public | fm_topics                         | table    | rt
 public | fm_topics_id_seq                  | sequence | rt
 public | groupmembers                      | table    | rt
 public | groupmembers_id_seq               | sequence | rt
 public | groups                            | table    | rt
 public | groups_id_seq                     | sequence | rt
 public | jobs_done_monthly                 | view     | rt
 public | jobs_done_monthly_by_ticket       | view     | rt
 public | jobs_done_monthly_by_transactions | view     | rt
 public | links                             | table    | rt
 public | links_id_seq                      | sequence | rt
 public | objectclasses                     | table    | postgres
 public | objectclasses_id_seq              | sequence | postgres
 public | objectcustomfields                | table    | rt
 public | objectcustomfields_id_s           | sequence | rt
 public | objectcustomfieldvalues           | table    | rt
 public | objectcustomfieldvalues_id_s      | sequence | rt
 public | objecttopics                      | table    | postgres
 public | objecttopics_id_seq               | sequence | postgres
 public | principals                        | table    | rt
 public | principals_id_seq                 | sequence | rt
 public | queues                            | table    | rt
 public | queues_id_seq                     | sequence | rt
 public | scripactions                      | table    | rt
 public | scripactions_id_seq               | sequence | rt
 public | scripconditions                   | table    | rt
 public | scripconditions_id_seq            | sequence | rt
 public | scrips                            | table    | rt
 public | scrips_id_seq                     | sequence | rt
 public | sessions                          | table    | rt
 public | templates                         | table    | rt
 public | templates_id_seq                  | sequence | rt
 public | tickets                           | table    | rt
 public | tickets_id_seq                    | sequence | rt
 public | topics                            | table    | postgres
 public | topics_id_seq                     | sequence | postgres
 public | transactions                      | table    | rt
 public | transactions_id_seq               | sequence | rt
 public | users                             | table    | rt
 public | users_id_seq                      | sequence | rt
(62 rows)


First of all - there is error in the migration, as it tries to update value using bad datatype.

Second - why some tables are created, and lest as owned by "postgres",
and their ownership is not changed to standard user? Should I ran it
differently?

But the rtfm thing worries me actually more - as far as I know, I never had
rtfm in this system - I am not 100% sure because I took over administration of
this from someone, and now I'm on my own, but the web interface doesn't
show anything related to rtfm.

What could/should I do to upgrade to 4.0?

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/



More information about the rt-users mailing list