DB Upgrade Script failed

[RickB]

Hi, I'm attempting an upgrade of our Omero instance from 5.0.8 to 5.1.2. I'm running Postgres 9.3.6, client and server, on CentOS 6, and when I run the upgrade script like this as the omero user:

/usr/pgsql-9.3/bin/psql -h localhost -U omero omero < sql/psql/OMERO5.1__1/OMERO5.0__0.sql

The script starts and chugs along for a bit, then dies with:

CREATE FUNCTION
ERROR: must be owner of function annotation_link_delete_trigger
ERROR: current transaction is aborted, commands ignored until end of transaction block


Any help is appreciated, and I'm happy to provide any additional info.

Thanks,
Rick

[manics]

Hi Rick

It sounds like some (or all) of your tables might be owned by a different user. Can you paste the first few lines of:

Code: Select all

psql -h localhost -U omero omero -c '\df+ annotation_link_delete_trigger'


The first result row should tell you who owns that object.

Simon

[RickB]

Thanks, looks like it's owned by the postgres user:

-bash-4.1$ psql -h localhost -U omero omero -c '\df+ annotation_link_delete_trigger'
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language |
Source code | Description
--------+--------------------------------+------------------+---------------------+---------+------------+----------+----------+------
--------------------------------------------------------------------------------------------+-------------
public | annotation_link_delete_trigger | trigger | | trigger | volatile | postgres | plpgsql


Should I be connecting to the DB as postgres instead of Omero? Or just change the owner of that object to omero? Thanks,
Rick

[jmoore]

Hi Rick,

were you previously accessing this database as `postgres` or did you perhaps restore the DB recently? You'll either need to use the `postgres` user everywhere, or change all the objects to belong to the `omero` user. (It may be easier to restore the database as `omero` rather than trying to change all the objects)

Cheers,
~Josh

[RickB]

Thanks, when we went from v 4.x to 5.0, we dumped the DB and restored it on a new server - Likely as the postgres user. I'll try running the upgrade script as the postgres user 1st. Thanks,
Rich