Am I missing something? I searched for instructions in the documentation but I just found an instruction for migrating the DB ON the server openproject runs on. And another instruction for migrating an Openproject instance with PostgreSQL to another server with PostgreSQL.

Greetings

Meanwhile I found this: https://docs.openproject.org/installation-and-operations/misc/upgrading-older-openproject-versions/

But it states that it'll only work if I have a Docker installation. However I have an installation with DEB packages..

Hi Christian,
have you found these resources?
https://docs.openproject.org/installation-and-operations/misc/packaged-postgresql-migration/ (for packaged installations)
https://docs.openproject.org/installation-and-operations/misc/upgrading-older-openproject-versions/#re-installing-openproject (as an alternative)
https://docs.openproject.org/installation-and-operations/operation/upgrading/#upgrade-notes-for-8x-to-9x (basic approach and good to know)

Kind regards
Matthias

Hey Matthias,

Yes I found at least the 1st and 2nd resource you mentioned. The first tells me how to migrate the database on the SAME machine (if I'm not mistaken) but I don't want to do that on the same machine if theres no other way.

The crux of the matter is that I want to leave the existing Openproject instance on server A as is and set up a new OP Instance on server B and then import the Database. 

That is because:
Server A is a production system and I cannot risk any data loss,
I do not have that much experiences with Databases,
I want to test the new instance on server B thoroughly before switching the production system from server A to server B.

greetings
Christian

Hey Christian,
have you thought about this way?: Create a databank dump, then you can migrate your data from MySQL to PostgreSQL using https://pgloader.io before uploading it on server B

Kind regards

I'm trying to understand what PGLoader does. It seems like it just takes the data from the origin DBMS and puts into a new PostgreSQL DBMS without changing any of the data. Is that correct?

Hi Christian,

You could run the migration script on the new server, as described in https://docs.openproject.org/installation-and-operations/misc/packaged-postgresql-migration/ but connect it over the network to the old mysql server instance.

You would need to open ports on any firewalls between the two machines to allow mysql traffic over the network, so check that mysql on the production server is bound to external interfaces and what port it is on:

sudo netstat -plunt

You would expect something like:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2675/mysqld

... so mysql is listening on external interfaces on port 3306 as expects, so for example if the production machine was running ubuntu you might use ufw to open the port to allow traffic from your new server in on port 3306 (mysql):

sudo ufw allow 3306/tcp

You will also need to ensure that there is a suitable user on the production server mysql instance for the migration script on the new server to connect in as:

CREATE USER openproject@{newserveripaddress};
GRANT ALL PRIVILEGES ON openproject.* TO openproject@{newserveripaddress} IDENTIFIED BY '{password}';
FLUSH PRIVILEGES;

Then check you can access the production server mysql dqatabase from the new server:

mysql -u openproject -p -h {productionserveripaddess} -P 3306

Then edit the openproject config file on the new server so that the mysql conncetion string points at the production server and uses the credentials of the user you just created on it:

sudo openproject config:set MYSQL_DATABASE_URL="mysql2://openproject:{password}@{productionserveripaddress}:3306/openproject"

Then you can run the migration script, and it will connect over the network to your production mysql instance to get the data, but it will migrate it into the postgresql instance on your new server.

Good luck!

Will

Nice article thanks for sharing……  WalgreensListens

Thanks a lot Will

I followed your instructions and it seems to work but the migration script ended with:

Renaming to public schema
ERROR:  must be owner of schema public
ERROR:  must be owner of schema public
Failed to rename schema openproject to public. See above.

is that bad?

greets

Hi Christian

I don't know if that matters or not.  I would suggest you try logging into the web application and testing a few things.  If there are issues, then it may imply that you need to run the migration script as a postgresql user with sufficient permissions to be able to do that renaming, i.e. own the schema public.  If so, you could edit your postgresql config to allow suitable permissions (search for config options for pg_hba.conf to learn more on that).

Best wishes

Will