Content
[Solved] Upgrade from version 7 to version 11
Added by Will Griffin over 3 years ago
Hi
Please would you be able to help?
I have openproject version 7 installed on ubuntu 16.04 from the package manager (7.4.7-1532077196.a556554d.xenial)
My goal is to upgrade to version 11 on ubuntu 20.04. I have tried a couple of approaches without success.
Any advice would be appreciated
Attempt 1: I upgraded the OS first. Then I attempted to follow the openproject upgrade process at https://docs.openproject.org/installation-and-operations/operation/upgrading/
1 a) I updated the package sources to point to the newer release channel:
wget -qO-
https://dl.packager.io/srv/opf/openproject/key
| sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/openproject.list
https://dl.packager.io/srv/opf/openproject/stable/11/installer/ubuntu/20.04.repo
sudo apt-get update
1 b) then I ran the upgrade install:
sudo apt-get install --only-upgrade openproject sudo openproject reconfigure
...but this stopped due to the lack of the postgresql database, with the following error:
======= INCOMPATIBLE DATABASE DETECTED =======
Your database is set up for use with a MySQL or MySQL-compatible variant.
This installation of OpenProject no longer supports these variants.
The following guides provide extensive documentation for migrating your installation to a PostgreSQL database:
https://www.openproject.org/migration-guides/
This process is mostly automated so you can continue using your OpenProject installation within a few minutes! ==============================================
1 c) so then I tried to migrate the mysql database to postgresql as described in the migration guide at https://docs.openproject.org/installation-and-operations/misc/packaged-postgresql-migration/
wget -qO-
https://dl.packager.io/srv/opf/pgloader-ccl/key
| sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/pgloader-ccl.list
https://dl.packager.io/srv/opf/pgloader-ccl/master/installer/ubuntu/20.04.repo
sudo apt-get update sudo apt-get install pgloader
[pgloader-ccl was not compatible with ubuntu 20.04 library versions, so I had to use the standard pgloader packeaged with the OS]
sudo apt install postgresql postgresql-contrib libpq-dev
sudo su - postgres
createuser -P -d openproject
sudo openproject config:set MYSQL_DATABASE_URL="localmysqlconnectionstring"
sudo openproject config:set DATABASE_URL="localpostgresqlconnectionstring"
sudo openproject run /opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres
...but this failed with the error:
Traceback (most recent call last):
2: from /opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres:120:in `<main>'
1: from /opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres:71:in `run'
/opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres:71:in `spawn': No such file or directory - fork failed (Errno::ENOENT)
...then I realised I need to edit this line of the script to use pgloader, not pgloader-ccl:
gloader_path = ENV.fetch('PGLOADER_PATH', 'pgloader-ccl')
..but then pgloader gave the error:
KABOOM!
FATAL error: Failed to connect to mysql at "localhost" (port 3306) as user "openproject":
Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
An unhandled error condition has been signalled: Failed to connect to mysql at "localhost" (port 3306) as user "openproject": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
...after some investigation this turned out to be due to mysql 8 (which is the version packaged in ubuntu 20.04) no longer uses mysql_native_password as the default authentication method, so I edited my.cnf to use this and changed the user permissions for the openproect mysql user:
sudo nano /etc/mysql/my.cnf
[mysqld]
default-authentication-plugin=mysql_native_password
...then when I reran the migration script it got further, but errored at:
An unhandled error condition has been signalled:
76 fell through ECASE expression.
Wanted one of (2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41
42 45 46 47 48 49 50 51 52 54 55 56 60 61 62 63 64 65 69 72
77 78 79 82 83 87 90 92 93 94 95 96 97 98 101 102 103 104 105
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
121 122 123 124 128 129 130 131 132 133 134 135 136 137 138
139 140 141 142 143 144 145 146 147 148 149 150 151 159 160
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175
176 177 178 179 180 181 182 183 192 193 194 195 196 197 198
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213
214 215 223 224 225 226 227 228 229 230 231 232 233 234 235
236 237 238 239 240 241 242 243 244 245 246 247 254).
...after some investigation it turned out that the default character collation in mysql 8 is utf8mb4_0900_ai_ci which it identifies with the number 76, but the packaged version of pgloader 3.6.1 uses a qmynd version that does not cater for this. I didn't want to get into compiling a new version of pgloader from source, so instead I span up a snapshot I took of the server during the OS upgrade process when it was on ubuntu 18.04, which has a mysql 5.7 as the packaged version, then changed the database connection string the migration script was using to point at that instead.
...this time the script completed successfully, so I continued with the next step to reconfigure openproject
sudo openproject reconfigure
...but it gave the following error:
PG::UndefinedTable: ERROR: relation "planning_element_type_colors_pkey" does not exist
...when I checked manually, this table does not exist in the database. So it looks like the migration script does not create additional database objects needed by version 11, but just migrates the database objects already present in version 7.
I attempted to do a fresh install of version 11 in order to create an empty version 11 database, then perform the migration script on that empty database, thinking that the migration would just move the version 7 data, and the version 11 install would make sure that all the necessary database objects would be present.
Unfortunately the migration script will not run if there is data already present in postgresql, and openproject configure
will not create a new database if postgresql is already present.
Attempt 2:
So I abandoned the above approach, and instead tried to use the dockerised method on a snapshot of the 18.04 version of the server in order to avoid the mysql 8.0 issues, following the approach described at https://docs.openproject.org/installation-and-operations/misc/upgrading-older-openproject-versions/
2 a) install docker and backup the openproject installation to get the mysql dump:
sudo apt install docker.io
sudo openproject run backup
2 b) update the package sources
wget -qO-
https://dl.packager.io/srv/opf/openproject/key
| sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/openproject.list
https://dl.packager.io/srv/opf/openproject/stable/11/installer/ubuntu/18.04.repo
2 c) upgrade openproject
sudo apt-get update
sudo apt-get install --only-upgrade openproject
2 d) extract the mysql dump
sudo cp /var/db/openproject/backup/mysql-dump-20210204143720.sql.gz /tmp/openproject-mysql.dump.gz
sudo gunzip /tmp/openproject-mysql.dump.gz
2 e) run the script
sudo bash /opt/openproject/script/migration/migrate-from-pre-8.sh 172.17.0.1 /tmp/openproject-mysql.dump
...but that errors:
1.6) Migrating database from 6 to 7 ... (NOOP if already on 7)
rake aborted!
Mysql2::Error: Can't connect to MySQL server on '172.17.0.1' (110 "Connection timed out")
...where 172.17.0.1 is the IP of the host machine on the docker virtual network:
ip a
4: docker0: <BROADCAST,MULTICAST,UP,LOWER_UP>
mtu 1500 qdisc noqueue state UP group default l
ink/ether 02:42:74:34:16:3c brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
valid_lft forever preferred_lft forever
inet6 fe80::42:74ff:fe34:163c/64 scope link
...so it looks like it cannot connect to the mysql instance, but I don't understand why it is trying to connect to a mysql instance on the host anyway. The script runs up 3 containers, one of which is running mysql 5.6 - surely it should be trying to connect to this instance, not the instance on the host machine anyway?
sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
a86315941e28 openproject/community:8-mysql "./docker/entrypoint…" 2 hours ago Up 2 hours 80/tcp, 5432/tcp op8
aaf60becc5e4 openproject/community:7 "/usr/src/app/docker…" 2 hours ago Up 2 hours 80/tcp, 5432/tcp op7
b027d3191ab6 mysql:5.6 "docker-entrypoint.s…" 2 hours ago Up 2 hours
sudo docker network ls
NETWORK ID NAME DRIVER SCOPE
1f830b5c94cb bridge bridge local
1626897c3a22 host host local
682331a8ca0d none null local
...so I pointed the script at that docker container mysql instance:
sudo docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' b027d3191ab6
172.17.0.2
sudo bash /opt/openproject/script/migration/migrate-from-pre-8.sh 172.17.0.2 /tmp/openproject-mysql.dump
...but that errors too, though it seems to be due to permissions:
Mysql2::Error: Can't connect to MySQL server on '172.17.0.2' (111 "Connection refused")
Please could you advise a way which I can successfully migrate from openproject 7 on mysql on ubuntu 16.04, to openproject 11 on postgresql on ubuntu 20.04?
Many thanks for your help, it is much appreciated.
Will
Replies (6)
Dear Will,
It makes sense to upgrade OpenProject step by step and not directly from 7 to 11.
I would go 7->8->9->11, while changing from MySQL to PostgreSQL in-between (e.g. between 8 and 9).
Have you found these upgrade notes, yet?:
https://docs.openproject.org/installation-and-operations/operation/upgrading/#upgrade-notes-for-openproject-7x-to-8x
https://docs.openproject.org/installation-and-operations/operation/upgrading/#upgrade-notes-for-8x-to-9x
You can go either step by step by hand or use a script to go directly from 8 to 10: https://github.com/opf/openproject/blob/dev/script/migration/migrate-from-pre-8.sh (or you can just use it as inspiration).
For the script to work you will need docker. However, you can run it somewhere other than the final server and then use the result in a package-based installation. The script expects a MySQL backup as input and will give you a PostgreSQL backup as output.
On packager.io there seem to be only packages for 9 or newer. This means: If you don't have the package for 9 lying around you will have to use the docker script either way as far as I see it.
Regarding Ubuntu: If you want to do the migration by hand, and not via script, you should first do the migration on the old 16.04 system, because the old packages will not be transferable to Ubuntu 20.04 probably.
I hope that helps.
Kind regards
Matthias
Hi Matthias
Many thanks for your help, it is much appreciated.
This is the process I used for upgrading from version 7 on ubuntu 16.04 to version 11 on ubuntu 20.04 in case others may find it useful. Please note this was a package based install.
Step 1: use the docker migration script to create an openproject version 10 postgresql database dump, from inputting an openproject version 7 mysql database dump.
I recommend running the docker migration script on a separate VM from your original openproject version 7 VM.
[When I tried to run it on the same VM I got a variety of errors to do with the dockerised processes being unable to connect to the mysql instance on the host machine. I suspect this was to do with there already being an openproject database on the host VM with the same database name and username as the database in the msqldump file that the docker script was trying to process.]
Dump your database on your original VM running version 7 of openproject:
mysqldump -uroot -p --databases openproject --result-file=/opt/openproject/openproject7.sql
Create a new VM just for running the docker script.
Make sure it has at least 4GB RAM.
[I got an out of memory error when I ran the docker script on a VM with 2GB RAM, but succeeded with 4GB. I used the same OS as the original openproject version 7 VM which was Ubuntu 16.04.]
Copy your database dump from the original openproject version 7 VM to the new VM:
scp /opt/openproject/openproject7.sql root@xxx.yyy.ppp.qqq:openproject7.sql
Grab the docker script onto your new VM:
cd /opt/openproject/
wget https://raw.githubusercontent.com/opf/openproject/dev/script/migration/migrate-from-pre-8.sh
Make it executable:
chmod +x /opt/openproject/migrate-from-pre-8.sh
install docker onto your new VM:
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt-get update
sudo apt-get install -y docker-ce
sudo systemctl status docker
install mysql onto your new VM:
apt-get install mysql-server
Edit the mysql config to bind to all interfaces, so the docker containers will be able to access it:
nano /etc/mysql/my.cnf
[mysqld]
bind-address = 0.0.0.0
sudo systemctl restart mysql
run the migration script (xxx.yyy.ppp.qqq is the IP address):
migrate-from-pre-8.sh xxx.yyy.ppp.qqq openproject7.sql
[You can see from reading the script the steps it will go through, and the kind of output to expect. It took about 5 minutes to run for me, but I have a small database.]
It will output a few files including:
openproject-migrated.dump
openproject-dump-8.sql
migration.log
openproject-migrated.dump
is the final oepnproject version 10 postresql database. Save this for later. Now you can destroy the VM that you created just for running the docker script.Step 2: upgrade the OS on the original openproject version 7 VM
Upgrade Ubuntu from 16.04 to 18.04. I won't go into all the details as this is well documented elsewhere, but the gist of it is:
Allow access to a second instance of sshd obn port 1022 in case your current ssh session gets killed during the upgrade:
sudo ufw allow 1022/tcp
Comment out or delete the openproject package sources file references:
/etc/apt/sources.list.d/openproject-ce.list
Make sure all the packages are up to date then do the upgrade:
sudo apt update
sudo apt dist-upgrade
sudo do-release-upgrade
As part of the upgrade you will probably need to choose to retain various config files such as sshd, webserver, grub, etc then reboot.
Once the upgrade is complete, make sure that openproject version 7 is still functional. You might need to tweak webserver configuration if stuff has changed.
Step 3: upgrade openproject from version 7 to version 10
Make sure the correct repo is in your package sources files:
Either just edit the files directly e.g.
sudo nano /etc/apt/sources.list.d/openproject.list
or
wget -qO- https://dl.packager.io/srv/opf/openproject/key | sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/openproject.list https://dl.packager.io/srv/opf/openproject/stable/10/installer/ubuntu/18.04.repo
Install the new version of openproject:
sudo apt-get update
sudo apt-get install --only-upgrade openproject
Install postgresql:
sudo apt install postgresql postgresql-contrib libpq-dev
Create a database and user for openproject:
sudo su - postgres
createuser -P -d openproject
createdb -O openproject openproject
exit
Stop open project:
sudo service openproject stop
Set your database connection string to use the details of the database and user you just created:
sudo openproject config:set DATABASE_URL="postgresql://openproject:password@localhost/openproject"
copy over your openproject version 10 postgresql database dump (produced from the docker script) and restore it:
sudo pg_restore --host=localhost --username=openproject --password --dbname=openproject --clean --create /home/will/openproject-migrated.dump
configure openproject:
cd /opt/openproject
sudo openproject configure
...now visit the openproject site and make sure it is working okay
Step 4: Upgrade Ubuntu from 18.04 to 20.04
Again, make sure all the packages are up to date then do the upgrade:
sudo apt update
sudo apt dist-upgrade
sudo do-release-upgrade
As part of the upgrade you will probably again need to choose to retain various config files such as sshd, webserver, grub, etc.
Check the openproject site is working okay
Step 5: upgrade openproject from version 10 to version 11
Edit package sources
sudo nano /etc/apt/sources.list.d/openproject.list
sudo nano /etc/apt/sources.list.d/openproject.list.distUpgrade
change:
deb https://dl.packager.io/srv/deb/opf/openproject/stable/10/ubuntu 18.04 main
to:
deb https://dl.packager.io/srv/deb/opf/openproject/stable/11/ubuntu 20.04 main
Install the new version of openproject:
sudo apt-get update
sudo apt-get install --only-upgrade openproject
cd /opt/openproject
sudo openproject configure
[At this point I got various errors about missing indexes. It seems like the docker script migration process produces a postgres database with indexes which are named differently to those resulting from a clean install of postgresql, and so differently to what is expected by the version 11 upgrade process. The solution is to identify the indexes and rename them to what the version 11 upgrade process expects.]
The first run of
sudo openproject configure
produced this error:rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
PG::UndefinedTable: ERROR: relation "cost_objects_pkey" does not exist]
To fix the error, rename the index.
Get your database connection string:
sudo su openproject
openproject config:get DATABASE_URL
postgresql://openproject:password@localhost/openproject
run psql with this connection string:
psql postgresql://openproject:password@localhost/openproject
Show the information about the table:
\d+ cost_objects
This will show you the table and it's indexes. Find the primary key and rename it, for example:
ALTER INDEX "idx_16546_primary" RENAME TO "cost_objects_pkey";
Then re-run
sudo openproject configure
I had to do this repeatedly for multiple misnamed indices, so you may need to repeat this process until all the indexes are renamed and the configure runs to completion.
Then check the site is working okay. Go to
https://yoururl/admin/info
and you should see it says:Core version
OpenProject 11.1.3 (PostgreSQL)
...so this produced a working instance of version 11 of openproject running on Ubuntu 20.04, with the same data as the original version 7 instance which was on Ubuntu 16.04.
Kind regards
Will
Hi Will,
I'm glad it worked. Thanks a lot for your extensive description of the process! I'm sure this will help other users with the same situation a lot.
Kind regards
Matthias
Hello Will,
thanks for the very detailed notes about your upgrade process. About the broken indices: There is a step in the script that is supposed to fix this automatically. Evidently it didn't. Can you please confirm that the indices still had the wrong names in
openproject-migrated.dump
? Maybe we should just add an extra step for good measure just before the dump to fix the indices again.Can you please also confirm that all tables are in the same schema? This seems to be an issue that keeps popping up as well.
Again there is a step in the script that ought to fix it but I just want to make sure it worked properly in your case.
Hi Markus
Please find attached the output of
pg_restore -l openproject-migrated.dump
This shows that all the tables seem to be in the public schema. Also that the indices
cost_objects_pkey
andcost_object_journals_pkey
are not present. So maybe the extra step before the dump could be useful.Hope that helps
Best wishes
Will
FYI the script now includes another step to make extra sure all primary keys are named correctly. I've done some more test runs and it did indeed still have to rename a couple of indices. I'm not quite sure why this isn't covered during the initial MySQL-to-Postgres migration in the OpenProject 10 container, but anyway using this script it will be corrected.
Thanks again for all your help, Will.