Database upgrade failure from 5.0.6 to 5.1.1

[wsigurdson]

We have OMERO 5.0.6 running successfully on Ubuntu 14.03 and wish to upgrade to 5.1.
Following the upgrade instructions, I am unable to upgrade our database from 5.0.6 to 5.1.1. When I run the upgrade script, the terminal output has many lines of;

"ERROR: current transaction is aborted, commands ignored until end of transaction block"

and I never see the requested "Password for user ..."

Without the server running (it fails to start since the database version is wrong), the output from "omero admin diagnostics" is:

================================================================================
OMERO Diagnostics 5.1.1-ice35-b43
================================================================================

WARNING:omero.util.UpgradeCheck:UPGRADE AVAILABLE:Please upgrade to 5.1.2 See http://trac.openmicroscopy.org.uk/omero for the latest version

Commands: java -version 1.7.0 (/usr/bin/java)
Commands: python -V 2.7.6 (/usr/bin/python)
Commands: icegridnode --version 3.5.1 (/usr/bin/icegridnode)
Commands: icegridadmin --version 3.5.1 (/usr/bin/icegridadmin)
Commands: psql --version 9.3.7 (/usr/bin/psql)

Server: icegridnode running
Server: Blitz-0 active (pid = 16800, enabled)
Server: DropBox active (pid = 16812, enabled)
Server: FileServer active (pid = 16816, enabled)
Server: Indexer-0 active (pid = 16819, enabled)
Server: MonitorServer active (pid = 16821, enabled)
Server: OMERO.Glacier2 active (pid = 16823, enabled)
Server: OMERO.IceStorm active (pid = 16826, enabled)
Server: PixelData-0 active (pid = 16827, enabled)
Server: Processor-0 active (pid = 16836, enabled)
Server: Tables-0 active (pid = 16845, enabled)
Server: TestDropBox inactive (enabled)

Log dir: /home/omero/OMERO.server-5.1.1-ice35-b43/var/log exists

Log files: Blitz-0.log 21.0 KB errors=8 warnings=0
Log files: DropBox.log 1.0 KB errors=2 warnings=1
Log files: FileServer.log 0.0 KB
Log files: Indexer-0.log 2.0 MB errors=660 warnings=132
Log files: MonitorServer.log 0.0 KB
Log files: OMEROweb.log n/a
Log files: PixelData-0.log 1.0 MB errors=325 warnings=65
Log files: Processor-0.log 15.0 KB errors=8 warnings=4
Log files: Tables-0.log 16.0 KB errors=8 warnings=4
Log files: TestDropBox.log n/a
Log files: master.err 0.0 KB errors=0 warnings=2
Log files: master.out 0.0 KB
Log files: Total size 3.26 MB


Environment:OMERO_HOME=(unset)
Environment:OMERO_NODE=(unset)
Environment:OMERO_MASTER=(unset)
Environment:OMERO_TEMPDIR=(unset)
Environment:PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games
Environment:PYTHONPATH=(unset)
Environment:ICE_HOME=(unset)
Environment:LD_LIBRARY_PATH=(unset)
Environment:DYLD_LIBRARY_PATH=(unset)

OMERO SSL port:4064
OMERO TCP port:4063
OMERO data dir:'/OMERO' Exists? True Is writable? True
OMERO temp dir:'/home/omero/omero/tmp' Exists? True Is writable? True (Size: 0)

JVM settings: Blitz -Xmx3600m -XX:MaxPermSize=1g # Settings({'system_memory': '24000'})
JVM settings: Indexer -Xmx2400m -XX:MaxPermSize=1g # Settings({'system_memory': '24000'})
JVM settings: Pixeldata -Xmx3600m -XX:MaxPermSize=1g # Settings({'system_memory': '24000'})
JVM settings: Repository -Xmx2400m -XX:MaxPermSize=1g # Settings({'system_memory': '24000'})

OMERO.web status... [NOT STARTED]

Previous upgrades have run without problems, so this is a mystery to me.

Wade

[kennethgillen]

Hi Wade,

If the database upgrade script aborted, then likely the upgrade script failed to run, and your OMERO database is still an OMERO 5.0.6 database (as you've mentioned yourself).

If you weren't prompted for a password when running the upgrade script, Postgres must be configured to allow you to connect without entering it.

Can you please reply with the full command you are executing to run the DB upgrade please?

If you pipe the output from the upgrade script to a file, e.g. with the `tee` command, you should be able to look through it and see what is causing the upgrade script to fail. Perhaps paste that output here if it's not clear why it's not running?

All the best,

Kenny

[wsigurdson]

Hi Kenny,

Thanks very much for quick reply.

The command entered to start the database upgrade is:

psql -h localhost -U omero omero < sql/psql/OMERO5.1__1/OMERO5.0__0.sql

Since our system is used primarily as a test bed to show users the advantages of using OMERO, I used very simple and I admit insecure naming; the db and username are both "omero"

The piped output from the upgrade command is:

BEGIN
CREATE FUNCTION
omero_assert_db_version
-------------------------

(1 row)

DROP FUNCTION
CREATE FUNCTION
ROLLBACK

This is then followed by the many lines of:
ERROR: current transaction is aborted, commands ignored until end of transaction block

I've always had to enter the postgres password in previous upgrades (at least to the best of memory), so I'm not sure what could have changed.

Thanks,

Best,
Wade

[jmoore]

Hi Wade,

you can use `-f sql/psql/OMERO5.1__1/OMERO5.0__0.sql` to see line numbers, but I suspect you are running into issues with the PG version check:

https://github.com/openmicroscopy/openmicroscopy/blob/develop/sql/psql/OMERO5.1__1/OMERO5.0__0.sql#L72

What version of PostgreSQL are you using?

~Josh

[wsigurdson]

Hi Josh,

I'm using 9.3.7 according to "psql --version"

The line numbers end at 2651 and start somewhere earlier (my terminal history is only 100 lines I think) than 1418.

In case it matters the output from 'psql -l' is :

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
omero | omero | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

so the encoding appears right.

Best,
Wade

[mtbc]

Dear Wade,

I'd be grateful if you could indulge me with what, from the "psql -U omero omero" shell, you get from,

Code: Select all

SELECT CAST(setting AS INTEGER) FROM pg_settings WHERE name = 'server_version_num';

and,

Code: Select all

SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = current_database();

It looks very much like we already know what the answers are, but when weird things happen I doubt my assumptions.

Thank you for your patience while we help you to figure this out.

Cheers,
Mark

[wsigurdson]

Hi Mark,

From within the psql shell your "SELECT CAST ..." code returned:

setting
---------
90113
(1 row)

And the the "SELECT pg_encoding..." returned:

pg_encoding_to_char
---------------------
UTF8
(1 row)

I greatly appreciate your help in sorting this out and I'm learning as we go.

Best,

Wade

[mtbc]

Dear Wade,

Ah, that would seem to explain everything. Your PostgreSQL server is reporting that it is version 9.1.13. As described at http://www.openmicroscopy.org/site/supp ... components OMERO 5.1 requires at least version 9.2, and honestly I'd advise going later than that if convenient: personally I usually run my testing on version 9.4.3 and that's working well, and your local psql client already seems to be at version 9.3.7 which is perfectly fine. With luck, upgrading your database server should then allow the OMERO upgrade to proceed smoothly. Do let us know if you run into any further puzzles! Also, watch out for having multiple versions of software installed, in case the one that's running isn't the one you thought.

Unfortunately, PostgreSQL major version upgrades can be a little involved for the server side so do work through their release notes and instructions carefully.

Cheers,

Mark

[wsigurdson]

Hi Mark,

You are correct in deducing that I had multiple versions installed/available. I was of course confusing the client with server. I'll check the upgrade instructions for postgres, but since 9.3 is available under Ubuntu's Synaptic Package Manager, hopefully that will take care any dependencies. Do you suggest un-installing 9.1 first or letting the upgrade proceed and then un-installing/deactivating it.

Obviously I'm afraid loosing the omero database although I have a backup.

Thanks very much for your help and I'll update you on my progress.

Wade

[rleigh]

Dear Wade,

The Debian/Ubuntu PostgreSQL packages provide a "pg_upgradecluster" tool which will handle the migration of your databases from 9.1 to 9.3. You'll need both installed during the upgrade, but you can remove 9.1 after the upgrade is completed. Note: run pg_lsclusters to see exactly what is running on which ports.

As always, please do take a backup first in case of any mishaps. I've not had any problems with it myself, but best to be safe!

Note the "port" setting in /etc/postgresql/$version/$cluster/postgresql.conf. The main server will be running on the default port 5432; extra servers will be on 5433 and higher numbers. The manual way to upgrade would be:

- run pg_dump on your existing database to back it up
- switch the port numbers and restart both servers
- run pg_restore to restore the dump into the new database now running on the default port
- drop the old database cluster and remove the old packages
(this is in effect what pg_upgradecluster will be doing for you)

Kind regards,
Roger