Restauring old omero db

Hi all,

I’m trying to retrieve a database that was dumped in 2016 from a Windows NT server (with Postgresql < 9.4). I don’t know the Omero version.

The strategy I used so far was to

  • de-dump the db on my local postgresql 11 server and reset the database name and user to omero to get back to something standard.

  • dump the corrected version with the same postrgresql.

  • use doker-compose from docker-example-omero to spin the three dockers (database, omeroserver, omeroweb).

  • stop the omeroserver and omeroweb services.

  • log into the database docker and drop the existing omero database

  • dedump the archive with:

docker-compose exec database psql -U omero -d omero -f /backups/omero.dump.sql
  • codker-compose up the services again

I know this looks very convoluted, but it got me close to what I wanted .The problem I can’t seem to fix is that after that, the root omero user password is no longer valid (it should be set by the ROOTPASS variable in docker-compose.yml). When I long into the database server, I can see the omero database, and that it is not empty. I tried logging into omero from the web client and directly from a shell in omeroserver via omero login, neither work.

  • TL;DR from here, is there a way to reset the root omero user password, so that I can try to login again?

  • Am I doing this very wrong and should I take a completely different approach?

Thank you!

Impressive! Glad you made it through all that.

Only if for a new database. We don’t currently overwrite the password on each start up.

Definitely. Use:

omero db password

to generate the necessary SQL command.

If you’ve made it this far, I’d think it’s all fine. Probably a few of the steps could have been omitted, but I don’t know that for sure. If OMERO starts with your updated database, I’d say you’re in pretty good shape.

The only thing I can think to double check is the encoding. Is it UTF-8?

Good luck with the further migration.
~Josh

Thanks a lot for the swift response Josh.

As for utf-8, if hope it won’t be too much of an issue (if we only have mangled names, it’s not a big problem).

I tried reseting the password as you indicated, but with no luck:

bash-4.2$ export PATH=$PATH:/opt/omero/server/OMERO.server/bin
bash-4.2$ omero db password
Please enter password for OMERO root user: 
Please re-enter password for OMERO root user: 
UPDATE password SET hash = 'b'kYMkc9SyhupHyJP1Cl2Alg=='' WHERE experimenter_id = 0;
bash-4.2$ omero login
Server: [localhost:4064]
Username: [omero-server]root
Password:
Password check failed for 'root': [id=0]
Password:
Password check failed for 'root': [id=0]
Password:
3 incorrect password attempts

I tried to restart the containers but it did not help. Could it be a hashing issue? Can I investigate this elsewhere?

Thanks again

Guillaume

:+1:

The SQL command only gets printed. You will need to run it against your PostgreSQL yourself. ~J

Oh… I did not get that!

It worked!!

I can log in the viewer, and I see the images from the root user.
Now ‘all’ I have to do is bring back the over users.

Thanks a lot!
G.

2 Likes

A post was split to a new topic: Image does not appear in OMERO.iviewer