FYI: Not possible to create new OMERO instance with latest PostgreSQL versions

If you are trying to install a fresh OMERO and database creation fails with:

ERROR:  relation "i_logicalchannel_emissionwave" already exists

or similar, please check your PostgreSQL version. The following versions should be considered broken:

  • 9.4.23
  • 9.5.18
  • 9.6.14
  • 10.9
  • 11.4

Until further notice, you will need to pin PostgreSQL to a previous version. We expect new versions to be released soon, though. If you are interested in more details, please see:

https://www.postgresql.org/message-id/15865-17940eacc8f8b081%40postgresql.org

~Josh

P.S. related docker thread https://github.com/docker-library/postgres/issues/594

Anyone following this issue, please update here when it’s resolved at postgres.

I rewound the VM and installed postgres with this command:
yum -y install postgresql10-server-10.8 postgresql10-10.8

Installation continued, and the script to populate the database complete without errors.

1 Like

Anyone know how to get postgresql 10.8 on Ubuntu 18.04? As far as I can see, there are no older versions in the apt repo.
Thanks,
Damir

I don’t think anyone’s found a good solution yet, see the latest on

If you or anyone else has an existing Ubuntu 18.04 server with a working version of postgres you might be able to copy all the relevant *.deb packages from the cache (maybe somewhere under /var/cache/?).

If you do it’d be great if you could share those packages with us!

Hi Simon,
I couldn’t find a source for a 10.8 .debs either. Still looking but don’t have much hope. Is there a good way to request the postgresql folks to quickly do a point release with the fix for this bug?

Is it correct that there is no issue if I upgrade an existing OMERO installation even if the server has already been upgraded to one of the offending postgresql versions?

Thanks,
Damir

Upgrading works, so far we’ve only seen the bug when creating new databases…
Which gave me an idea: it looks like creating a new OMERO db in a temporary working postgres e.g. 10.8, dumping the DB, and reloading it into a broken version of PG e.g. 10.9 works!

# Create OMERO5.4__0.sql
docker run -it --rm -v $PWD:/data -w /data --entrypoint /opt/omero/server/OMERO.server/bin/omero openmicroscopy/omero-server:5.5.1 db script --password omero

# Create a new database using a working version of postgres
docker run --rm --name tmpdb -v $PWD:/data postgres:10.8

docker exec -it -u postgres tmpdb createuser -P -D -R -S omero
docker exec -it -u postgres tmpdb createdb -O omero omero
docker exec -it -u postgres tmpdb sh -c 'psql -h localhost -U omero omero < /data/OMERO5.4__0.sql'

# Dump the database
docker exec -it -u postgres tmpdb pg_dump -f /data/omero-new.pgdump omero

# Load omero-new.pgdump into a broken version of postgres
psql -hlocalhost -Uomero omero < omero-new.pgdump

For convenience I’ve attached the dumped db (renamed to .txt as discourse restricts file extensions)
omero-new.pgdump.txt (639.6 KB)

Hi Simon,

Thanks for this. That worked fine as far as I can tell. I now have a fully functional “from scratch” install on Ubuntu 18.04.

For others wanting to do this:

OMERO.server/bin/omero db script -f OMERO.server/db.sql --password “$OMERO_ROOT_PASS”
psql -h localhost -U “$OMERO_DB_USER” “$OMERO_DB_NAME” < OMERO.server/db.sql

with:

wget https://forum.image.sc/uploads/short-url/i3VvA2uJxGJKpittOX8F8tsfrzw.txt
mv i3VvA2uJxGJKpittOX8F8tsfrzw.txt omero-new.pgdump
psql -h localhost -U “$OMERO_DB_USER” “$OMERO_DB_NAME” < omero-new.pgdump

after completing the install instructions you have a working OMERO 5.5.1 where the OMERO root password is “omero”. Make sure to change that ASAP.

Cheers,
Damir

All,

PostgreSQL 11.5, 10.10, 9.6.15, 9.5.19, 9.4.24, and 12 Beta 3 Released!”. These versions are now available on Docker and Debian Stretch. I’ve successfully tested 9.6.15 and 10.10 with omero-server-docker. We’ll slowly start unpinning versions in the OMERO repositories, but if anyone runs into an issue, please let us know.

All the best,
~Josh

1 Like