PostGeSQL 9.6.13 vs. PostGreSQL 10.10 (Upgrade from 5.4.10 to 5.5.1)

Hi,

I do:

a) pg_dump -U omero -F c -f omero_db.Dmp omero_db # Ver: 9.6.13
b) pg_restore -Fc -d omero_db omero_db.Dmp # Ver: 10.10

Is that safe ??

I get some issues … can I ignore them ?

best regards
Andreas

The Issues:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6716; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS ‘PL/pgSQL procedural language’;
WARNING: errors ignored on restore: 1

You will definitely need plpgsql enabled on the new database for things to work properly. You are running this as the postgres user? ~Josh

cf. https://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database

Hi Josh,

no, I run it as user ‘omero’.

Ok. I drop the omero_db again and do a new pg_restore as user ‘postgres’.

greetings
andreas

Hi again,

now the import is fine. (without errors)

But now I have the Omero5.4 database-part in the 10.10 PostGreSQL. I’ve a proper 5.5.1 Omero-Installation on a new Server and a copy of all Image-Data on the correct place. The Omero 5.5 is not running yet.

First I try to upgrade the Database and use:

https://docs.openmicroscopy.org/omero/5.5.1/sysadmins/server-upgrade.html

I wounder a litte bit about the numbers in section:
https://docs.openmicroscopy.org/omero/5.5.1/sysadmins/server-upgrade.html#run-the-upgrade-script

Why is it this way:
psql -h localhost -U db_user omero_database < sql/psql/OMERO5.4__0/OMERO5.3__1.sql

I would have expected something like: … sql/psql/OMERO5.5__0/OMERO5.4__1.sql …

And another point is: if I run the precheck script, I get a “wrong database version” issue.
(I understand that issue, cause it’s another version…)

What do I’ve to do ?
Will it run without “upgrade” ? ( … I’m not dare)

Thank’s for your help and have a nice evening!
Andreas

I am afraid that this is the problem described at Question about postgres database during upgrade from 5.4.9 to 5.5.1 - we have a fix in but it’s not yet live on our site, sorry to have misled you. The short story is that you can wholly skip that “Upgrade your database” section because the OMERO 5.5 server runs on top of the OMERO 5.4 database.

Hi Mark,

thanks a lot for your hints !!

My 5.5 Server is up and running.
(sorry for the crossposting in the slackthread)

wish you a nice day and hope we see us next year
Andreas

2 Likes