I am trying to upgrade my local Galaxy instance from release_20.09 to release_24.1. When executing sh manage_db.sh upgrade , the following error occurs. Does anyone know how to resolve it? Thank you.
$ sh manage_db.sh upgrade
Activating virtualenv at .venv
INFO:alembic.runtime.migration:Context impl PostgresqlImpl.
INFO:alembic.runtime.migration:Will assume transactional DDL.
INFO:alembic.runtime.migration:Running upgrade -> e7b6dcb09efd, create gxy branch
DEBUG:alembic.runtime.migration:new branch insert e7b6dcb09efd
INFO:alembic.runtime.migration:Running upgrade e7b6dcb09efd -> b182f655505f, add workflow.source_metadata column
DEBUG:alembic.runtime.migration:update e7b6dcb09efd to b182f655505f
INFO:alembic.runtime.migration:Running upgrade b182f655505f -> 6a67bf27e6a6, deferred data tables
DEBUG:alembic.runtime.migration:update b182f655505f to 6a67bf27e6a6
INFO:alembic.runtime.migration:Running upgrade 6a67bf27e6a6 -> 186d4835587b, drop job_state_history.update_time column
DEBUG:alembic.runtime.migration:update 6a67bf27e6a6 to 186d4835587b
INFO:alembic.runtime.migration:Running upgrade 186d4835587b -> e0e3bb173ee6, add column deleted to API keys
DEBUG:alembic.runtime.migration:update 186d4835587b to e0e3bb173ee6
INFO:alembic.runtime.migration:Running upgrade e0e3bb173ee6 -> 59e024ceaca1, add export association table
DEBUG:alembic.runtime.migration:update e0e3bb173ee6 to 59e024ceaca1
INFO:alembic.runtime.migration:Running upgrade 59e024ceaca1 -> 518c8438a91b, Add when_expression column
DEBUG:alembic.runtime.migration:update 59e024ceaca1 to 518c8438a91b
INFO:alembic.runtime.migration:Running upgrade 518c8438a91b -> 3100452fa030, Add reason column on invocation table
DEBUG:alembic.runtime.migration:update 518c8438a91b to 3100452fa030
INFO:alembic.runtime.migration:Running upgrade 3100452fa030 -> c39f1de47a04, Add skipped state to collection_job_state_summary_view
DEBUG:alembic.runtime.migration:update 3100452fa030 to c39f1de47a04
INFO:alembic.runtime.migration:Running upgrade c39f1de47a04 -> d0583094c8cd, add quota source labels
DEBUG:alembic.runtime.migration:update c39f1de47a04 to d0583094c8cd
INFO:alembic.runtime.migration:Running upgrade d0583094c8cd -> 9540a051226e, preferred_object_store_ids
DEBUG:alembic.runtime.migration:update d0583094c8cd to 9540a051226e
INFO:alembic.runtime.migration:Running upgrade c39f1de47a04 -> 3a2914d703ca, add index workflow_request_step_states__workflow_invocation_id
DEBUG:alembic.runtime.migration:new branch insert 3a2914d703ca
INFO:alembic.runtime.migration:Running upgrade 3a2914d703ca -> caa7742f7bca, add index workflow_request_input_parameters__workflow_invocation_id
DEBUG:alembic.runtime.migration:update 3a2914d703ca to caa7742f7bca
INFO:alembic.runtime.migration:Running upgrade caa7742f7bca, 9540a051226e -> 460d0ecd1dd8, Merge head revisions
DEBUG:alembic.runtime.migration:merge, delete ['caa7742f7bca'], update 9540a051226e to 460d0ecd1dd8
INFO:alembic.runtime.migration:Running upgrade 460d0ecd1dd8 -> 3356bc2ecfc4, drop view
DEBUG:alembic.runtime.migration:update 460d0ecd1dd8 to 3356bc2ecfc4
INFO:alembic.runtime.migration:Running upgrade 3356bc2ecfc4 -> b855b714e8b8, make api_keys deleted non nullable
DEBUG:alembic.runtime.migration:update 3356bc2ecfc4 to b855b714e8b8
INFO:alembic.runtime.migration:Running upgrade b855b714e8b8 -> 2d749563e1fe, Add Notification System tables
DEBUG:alembic.runtime.migration:update b855b714e8b8 to 2d749563e1fe
INFO:alembic.runtime.migration:Running upgrade 2d749563e1fe -> e0561d5fc8c7, add archived columns to history
DEBUG:alembic.runtime.migration:update 2d749563e1fe to e0561d5fc8c7
INFO:alembic.runtime.migration:Running upgrade e0561d5fc8c7 -> e93c5d0b47a9, alter column CustosAuthnzToken.external_user_id to increase length
DEBUG:alembic.runtime.migration:update e0561d5fc8c7 to e93c5d0b47a9
INFO:alembic.runtime.migration:Running upgrade e0561d5fc8c7 -> 987ce9839ecb, create celery_user_rate_limit_table
DEBUG:alembic.runtime.migration:new branch insert 987ce9839ecb
INFO:alembic.runtime.migration:Running upgrade 987ce9839ecb, e93c5d0b47a9 -> 92fb564c7095, Merge alembic heads after pulling external_user_id length extension forward
DEBUG:alembic.runtime.migration:merge, delete ['987ce9839ecb'], update e93c5d0b47a9 to 92fb564c7095
INFO:alembic.runtime.migration:Running upgrade 92fb564c7095 -> ddbdbc40bdc1, add workflow_comment table
DEBUG:alembic.runtime.migration:update 92fb564c7095 to ddbdbc40bdc1
INFO:alembic.runtime.migration:Running upgrade ddbdbc40bdc1 -> 8a19186a6ee7, add label, requires_path_in_url and requires_path_in_header_named columns to interactivetool_entry_point
DEBUG:alembic.runtime.migration:update ddbdbc40bdc1 to 8a19186a6ee7
INFO:alembic.runtime.migration:Running upgrade 8a19186a6ee7 -> 2dc3386d091f, add_indexes_for_workflow_comment_foreign_keys
DEBUG:alembic.runtime.migration:update 8a19186a6ee7 to 2dc3386d091f
INFO:alembic.runtime.migration:Running upgrade 2dc3386d091f -> 55f02fd8ab6c, Add index on history_dataset_association extension
DEBUG:alembic.runtime.migration:update 2dc3386d091f to 55f02fd8ab6c
INFO:alembic.runtime.migration:Running upgrade 55f02fd8ab6c -> 303a5583a030, add dispatched column to notifications
DEBUG:alembic.runtime.migration:update 55f02fd8ab6c to 303a5583a030
INFO:alembic.runtime.migration:Running upgrade 303a5583a030 -> 5924fbf10430, add galaxy_url column to notifications
DEBUG:alembic.runtime.migration:update 303a5583a030 to 5924fbf10430
INFO:alembic.runtime.migration:Running upgrade 5924fbf10430 -> c14a3c93d66a, add user defined object stores
DEBUG:alembic.runtime.migration:update 5924fbf10430 to c14a3c93d66a
INFO:alembic.runtime.migration:Running upgrade c14a3c93d66a -> c14a3c93d66b, add user defined file sources
DEBUG:alembic.runtime.migration:update c14a3c93d66a to c14a3c93d66b
INFO:alembic.runtime.migration:Running upgrade c14a3c93d66b -> c63848676caf, Update username column schema and data
DEBUG:alembic.runtime.migration:update c14a3c93d66b to c63848676caf
INFO:alembic.runtime.migration:Running upgrade c63848676caf -> 04288b6a5b25, make dataset uuids unique
Traceback (most recent call last):
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedFunction: function gen_random_uuid() does not exist
LINE 3: SET uuid=REPLACE(gen_random_uuid()::text, '-', '')
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/galaxy-dist/galaxy/./scripts/db.py", line 37, in <module>
main()
File "/galaxy-dist/galaxy/./scripts/db.py", line 33, in main
args.func(args)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/base.py", line 265, in upgrade
self._exec_command("upgrade", args)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/base.py", line 289, in _exec_command
getattr(dbscript, command)(args)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/base.py", line 202, in upgrade
self._upgrade_to_head(args.sql)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/dbscript.py", line 88, in _upgrade_to_head
self._upgrade_to_revision("gxy@head", is_sql_mode)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/base.py", line 234, in _upgrade_to_revision
command.upgrade(self.alembic_config, rev, is_sql_mode)
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/alembic/command.py", line 403, in upgrade
script.run_env()
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/alembic/script/base.py", line 583, in run_env
util.load_python_file(self.dir, "env.py")
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
module = load_module_py(module_id, path)
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
spec.loader.exec_module(module) # type: ignore
File "<frozen importlib._bootstrap_external>", line 850, in exec_module
File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/alembic/env.py", line 146, in <module>
run_migrations_online()
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/alembic/env.py", line 40, in run_migrations_online
_configure_and_run_migrations_online(url)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/alembic/env.py", line 123, in _configure_and_run_migrations_online
context.run_migrations()
File "<string>", line 8, in run_migrations
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/alembic/runtime/environment.py", line 948, in run_migrations
self.get_context().run_migrations(**kw)
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
step.migration_fn(**kw)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/alembic/versions_gxy/04288b6a5b25_make_dataset_uuids_unique.py", line 70, in upgrade
_randomize_uuids_for_purged_datasets_with_duplicated_uuids(connection)
File "/galaxy-dist/galaxy/lib/galaxy/model/migrations/alembic/versions_gxy/04288b6a5b25_make_dataset_uuids_unique.py", line 225, in _randomize_uuids_for_purged_datasets_with_duplicated_uuids
connection.execute(updated_purged_uuids)
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
ret = self._execute_context(
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
return self._exec_single_context(
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
self._handle_dbapi_exception(
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/galaxy-dist/galaxy/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function gen_random_uuid() does not exist
LINE 3: SET uuid=REPLACE(gen_random_uuid()::text, '-', '')
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
[SQL:
UPDATE dataset
SET uuid=REPLACE(gen_random_uuid()::text, '-', '')
WHERE uuid IN (SELECT uuid FROM temp_duplicate_datasets_purged) AND purged = true
]
(Background on this error at: https://sqlalche.me/e/20/f405)