Page 1 of 1

Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Thu Dec 14, 2017 10:47 am
by rikardjo
Hello!

I had trouble getting a Ubuntu 14.04 vm with Omero 5.2.7 installed on it online.

I created a new vm with Ubuntu 16.04 and installed Omero 5.4.0, everything seems to work well.

Now, I've managed to get the old vm online, and I would like to migrate the binary data store and database from that one to the new server. I've googled and browsed the forums a lot about this, but the only post I've found about moving Omero data between servers is one about from Windows to Linux, the rest is about upgrading.

So, I'm curious. Would it work to rsync the binary data store to the new installation, then dump the old database and import it on the new server? Perhaps I need to try upgrade the old system before moving the data?

Perhaps goes without saying, but what's of interest to move is the resources uploaded/generated in binary data store in the earlier installation, and users with their permissions. (of which there is less than a hand full of).

Thanks in advance!

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Thu Dec 14, 2017 3:18 pm
by kennethgillen
Hi rikardjo,

So, I'm curious. Would it work to rsync the binary data store to the new installation, then dump the old database and import it on the new server?

Yes, this is exactly the process you would go through.

Perhaps I need to try upgrade the old system before moving the data?

Since the new server has already been set up with the new distribution of OMERO, you'd have to upgrade the 5.2 database before an OMERO 5.4 distribution would start up.

Note, you'll need to follow the upgrade instructions from 5.2.3 -> 5.3, then 5.3 -> 5.4 - as we mention in the big red "Warning" box at the top of https://docs.openmicroscopy.org/omero/5 ... grade.html

There's no reason to ugprade your original database on the source server though, you could run through the database upgrades after pg_restoring on the new host, leaving your original host in place in the event something goes wrong with the migration.

You can effectively test this whole process while leaving your original server in place - i.e. rsyncing on-line and only taking your source OMERO instance offline for the time it takes to run a pg_dump. Then you can run through the setup and testing of the DB upgrades etc on the new host. Then it's just a quick final rysnc and offline DB dump/restore for the "final/real" migration step (and potentially a DNS switch).

Perhaps goes without saying, but what's of interest to move is the resources uploaded/generated in binary data store in the earlier installation, and users with their permissions. (of which there is less than a hand full of).

If you rsync your entire "OMERO data directory" (https://docs.openmicroscopy.org/omero/5 ... o.data.dir) along with a pg_dump/pg_restore (with OMERO offline) you will end up with the complete OMERO instance at the destination server. Note: it's possible to run the first rsync with your existing system online, then follow it up with a final rsync and db_dump once the source OMERO instance has been stopped. That will speed up the migration downtime.

Good luck, and please let us know how you get on!

Kenny

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Fri Dec 15, 2017 10:09 am
by kennethgillen
Also,

Don't forget to persist any config changes you've made on your source server:

(obviously there may be differences, such as the new filesystem locations etc)

For both OMERO.web and OMERO.server (if you're migrating both), e.g.
Code: Select all
bin/omero config get (at source) ->bin/omero config edit (at dest)

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Fri Dec 15, 2017 11:56 am
by rikardjo
Hi!

Thank you so much, I'm in the process of rsyncing the BDS to the new server, it'll still be a few hours due to the size of it.

So, If I understood correctly, I'll dump the DB, import it on the new one, then start the upgrade process of the DB to 5.3, then to 5.4. Then set any config that's different from the old server.

Stupid question as I'm new to postgresql. On importing/restoring the DB, would it be the password for the DB user on the old server that I must set on the new server? My guess is that the users tag along with migration, but would like to ask just in case. :roll:

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Fri Dec 15, 2017 2:14 pm
by kennethgillen
Hi!

Thank you so much, I'm in the process of rsyncing the BDS to the new server, it'll still be a few hours due to the size of it.

So, If I understood correctly, I'll dump the DB, import it on the new one, then start the upgrade process of the DB to 5.3, then to 5.4. Then set any config that's different from the old server.

Yes, though for clarity: I'd set all the _same_ config you had before (e.g. public user config etc), but make sure to change anything which is different to the old server, like filesystem locations etc.

Stupid question as I'm new to postgresql. On importing/restoring the DB, would it be the password for the DB user on the old server that I must set on the new server? My guess is that the users tag along with migration, but would like to ask just in case. :roll:

So there are a couple of passwords to discuss.

1. The OMERO user account passwords, if you're not using ldap, and certainly for "root", are indeed persisted by the DB pg_dump/pg_restore (hashed and salted, so not "kept" per se, but OMERO user's passwords won't change).

2. The new DB user password that will depend on what DB user password you set when you created the PostgreSQL DB user in the new installation of PostgreSQL on the new/destination server.

That's one of the things that I was thinking of when I mentioned above - i.e. what OMERO.config (get|set) settings that _could_ differ between the systems.

If you want to move forward with the new DB password if it differs, you just configure the new OMERO.dist to use the new DB password. i.e. you don't have to set it on the new server to the same as it was before - as long as you tell OMERO what you gave when you configured your new PostgreSQL user.

Hope that's clear?

Best,

Kenny

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Wed Dec 20, 2017 10:50 am
by rikardjo
Hi!

I seem to have got it working, and without any real trouble too, joyful!

Step by step what was done, was:
(when old-omero is the 5.2.7 installation and omero/omero-server-address the 5.4.0. Credentials removed)

Code: Select all
# send the database
root@old-omero:~# pg_dump -h localhost -U databaseuser -Fc -f omero.postgresql.before_upgrade.db.dump databasename
root@old-omero:~# rsync -azv omero.postgresql.before_upgrade.db.dump omero-server-address:~

# sync the binary data store, the path is different on the new installation
root@old-omero:/BMC-OMERO# rsync -avz ./ root@omero-server-address:/OMERO/

# I could have used the same database, but I create a new empty one on omero
root@omero:~# export OMERO_DB_USER="databaseuser"
root@omero:~# export OMERO_DB_NAME=databasename
root@omero:~# su - postgres -c "createdb -E UTF8 -O '$OMERO_DB_USER' '$OMERO_DB_NAME'"

# didn't know how to import data to postgresql as root at this point, so switched to user postgres
root@omero:~# cp omero.postgresql.before_upgrade.db.dump /var/lib/postgresql/.
root@omero:~# su - postgres
postgres@omero:~$ pg_restore -Fc -d databasename omero.postgresql.before_upgrade.db.dump

# changed settings
omero@omero:~$ OMERO.server/bin/omero config edit

# Edit your preferences below. Comments are ignored
omero.config.version=5.1.0
omero.data.dir=/OMERO
omero.db.name=databasename
omero.db.pass=databasepassword
omero.db.user=databaseuser
omero.web.application_server=wsgi-tcp

# save and exit

# pre check for 5.3, went well
omero@omero:~$ psql -h localhost -U databaseuser databasename < OMERO.server/sql/psql/OMERO5.3__0/OMERO5.2__0-precheck.sql
# upgrade to 5.3
omero@omero:~$ psql -h localhost -U databaseuser databasename < OMERO.server/sql/psql/OMERO5.3__0/OMERO5.2__0.sql

# pre check for 5.4, went well
omero@omero:~$ psql -h localhost -U databaseuser databasename < OMERO.server/sql/psql/OMERO5.4__0/OMERO5.3__0-precheck.sql

# upgrade to 5.4
omero@omero:~$ psql -h localhost -U databaseuser databasename < OMERO.server/sql/psql/OMERO5.4__0/OMERO5.3__0.sql

# check that all files under /OMERO is owned by user omero, should give no output, which it didn't:
root@omero:/OMERO# find . \! -user omero -print

# then starts the omero and omero-web services


I can login both using Insight and over the web interface, and the data is available!

Thank you so much, kennethgillen!

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Wed Dec 20, 2017 10:55 am
by rikardjo
There's only one problem with the installation, it seems to be network/firewall related though.
Some users can't connect using Insight from another VLAN here on the university, even though port 4064 on the new server is open for all the VLANs on the university.

There's no firewall on the server, firewall is disabled on the client side.

I can update here later today if I managed to solve it after visiting them trying connecting myself.

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Wed Dec 20, 2017 12:15 pm
by kennethgillen
We're very glad to hear the upgrade went well!

Some users can't connect using Insight from another VLAN here on the university, even though port 4064 on the new server is open for all the VLANs on the university.

There's no firewall on the server, firewall is disabled on the client side.


So this is very possibly a client-server version mismatch.

After any MAJOR.MINOR version upgrade (e.g. 5.3->5.4), all OMERO clients need to match the server's MAJOR.MINOR version level.

So before any of these upgrades, when emailing users of OMERO, I always include a really large and obvious statement about the need to upgrade the OMERO clients.

Still, for the next few days, we'll almost always receive a few requests from users who can't connect.

I hope this is all that's going on in your case.

You can start to diagnose the ability to see the server with e.g. telnet (see #1)

- if not, you'll need to engage your local IT support mechanism, and point them at #2.

#1 https://docs.openmicroscopy.org/latest/ ... stallation
#2 https://docs.openmicroscopy.org/latest/ ... urity.html

Re: Omero, move data/db from 5.2.7 to a new 5.4.0

PostPosted: Wed Dec 20, 2017 3:09 pm
by rikardjo
kennethgillen wrote:We're very glad to hear the upgrade went well!

So this is very possibly a client-server version mismatch.

After any MAJOR.MINOR version upgrade (e.g. 5.3->5.4), all OMERO clients need to match the server's MAJOR.MINOR version level.


Hello, that was the very much the issue, never considered it as the error message was about wrong username/password, which, what I've read, mostly occur when there's a firewall issue.

Thanks you for all the help, both from me and the researchers here using Omero.

Regards, Rikard