Hi,
I setup a VM with indico 3.1. I am running problem with the database migration. I am not sure if I can just restore the database from a 9.6 dump. Maybe I have to upgrade the postgresql database? Thank you very much for any help on this issue.
My 2.3.5 is using postgresql 9.6 on a CentOS 7. Here is what I did:
old server (CentOS 7 and postgresql 9.6)
as indico user:
pg_dump -Fc -f /tmp/indico.dump
new server (Debian 11 with postgresql 13)
as root:
su - postgres -c 'dropdb indico'
su - postgres -c 'createdb -O indico indico'
su - postgres -c 'psql indico -c "CREATE EXTENSION unaccent; CREATE EXTENSION pg_trgm;"'
as indico user:
pg_restore -d indico -O /tmp/indico.dump
I got the following error messages:
(indico) indico@indicov3:~$ pg_restore -d indico -O indico.dump
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: from TOC entry 5933; 0 0 COMMENT SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: must be owner of schema public
Command was: COMMENT ON SCHEMA public IS 'standard public schema';
pg_restore: from TOC entry 5934; 0 0 COMMENT EXTENSION plpgsql
pg_restore: error: could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
pg_restore: from TOC entry 5935; 0 0 COMMENT EXTENSION pg_trgm
pg_restore: error: could not execute query: ERROR: must be owner of extension pg_trgm
Command was: COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';
pg_restore: from TOC entry 5936; 0 0 COMMENT EXTENSION unaccent
pg_restore: error: could not execute query: ERROR: must be owner of extension unaccent
Command was: COMMENT ON EXTENSION unaccent IS 'text search dictionary that removes accents';
pg_restore: warning: errors ignored on restore: 5
From the indico log:
(indico) indico@indicov3:~/log$ more indico.log
2022-01-26 11:26:16,700 INFO c69e333a65c7496e - indico.rh GET / [IP=128.171.30.254] [PID=690]
2022-01-26 11:26:16,729 ERROR c69e333a65c7496e - indico.flask (psycopg2.errors.UndefinedColumn) column categories.event_creation_mo
de does not exist
LINE 1: ...es AS categories_categories_default_event_themes, categories...
^
[SQL: SELECT categories.categories.event_message AS categories_categories_event_message, categories.categories.description AS categories_categories_descrip
tion, categories.categories.no_access_contact AS categories_categories_no_access_contact, categories.categories.id AS categories_categories_id, categories.
categories.parent_id AS categories_categories_parent_id, categories.categories.is_deleted AS categories_categories_is_deleted, categories.categories.positi
on AS categories_categories_position, categories.categories.visibility AS categories_categories_visibility, categories.categories.icon_metadata AS categori
es_categories_icon_metadata, categories.categories.logo_metadata AS categories_categories_logo_metadata, categories.categories.timezone AS categories_categ
ories_timezone, categories.categories.default_event_themes AS categories_categories_default_event_themes, categories.categories.event_creation_mode AS cate
gories_categories_event_creation_mode, categories.categories.event_creation_notification_emails AS categories_categories_event_creation_notification_emails
, categories.categories.event_message_mode AS categories_categories_event_message_mode, categories.categories.suggestions_disabled AS categories_categories
_suggestions_disabled, categories.categories.notify_managers AS categories_categories_notify_managers, categories.categories.is_flat_view_enabled AS catego
ries_categories_is_flat_view_enabled, categories.categories.default_ticket_template_id AS categories_categories_default_ticket_template_id, categories.cate
gories.default_badge_template_id AS categories_categories_default_badge_template_id, categories.categories.title AS categories_categories_title, categories
.categories.protection_mode AS categories_categories_protection_mode, (SELECT count(attachments.attachments.id) AS count_1
FROM attachments.attachments JOIN attachments.folders ON attachments.attachments.folder_id = attachments.folders.id
WHERE NOT attachments.folders.is_deleted AND NOT attachments.attachments.is_deleted AND attachments.folders.category_id = categories.categories.id) AS anon
_1, EXISTS (SELECT 1
FROM events.events
WHERE events.events.category_id = categories.categories.id AND NOT events.events.is_deleted) AS anon_2, categories_1.id AS categories_1_id, categories_1.ti
tle AS categories_1_title, categories_1.protection_mode AS categories_1_protection_mode
FROM categories.categories LEFT OUTER JOIN categories.categories AS categories_1 ON categories_1.parent_id = categories.categories.id AND NOT categories_1.
is_deleted
WHERE categories.categories.id = %(id_1)s AND categories.categories.is_deleted = false ORDER BY categories_1.position]
[parameters: {'id_1': 0}]
(Background on this error at: https://sqlalche.me/e/14/f405)
Traceback (most recent call last):
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.UndefinedColumn: column categories.event_creation_mode does not exist
LINE 1: ...es AS categories_categories_default_event_themes, categories...
^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/indico/.venv/lib/python3.9/site-packages/flask/app.py", line 1516, in full_dispatch_request
rv = self.dispatch_request()
File "/opt/indico/.venv/lib/python3.9/site-packages/flask/app.py", line 1502, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/flask/util.py", line 79, in wrapper
return obj().process()
File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/rh.py", line 291, in process
handle_sqlalchemy_database_error() # this will re-raise an exception
File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/rh.py", line 281, in process
res = self._do_process()
File "/opt/indico/.venv/lib/python3.9/site-packages/indico/web/rh.py", line 225, in _do_process
args_result = self._process_args()
File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/display.py", line 224, in _process_args
RHDisplayCategoryBase._process_args(self)
File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/base.py", line 34, in _process_args
self.category = self._get_category(category_id)
File "/opt/indico/.venv/lib/python3.9/site-packages/indico/modules/categories/controllers/base.py", line 27, in _get_category
category = self._category_query.filter_by(id=category_id, is_deleted=False).one_or_none()
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2836, in one_or_none
return self._iter().one_or_none()
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2894, in _iter
result = self.session.execute(
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
return connection._execute_clauseelement(
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
ret = self._execute_context(
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
self._handle_dbapi_exception(
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
util.raise_(
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
self.dialect.do_execute(
File "/opt/indico/.venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column categories.event_creation_mode does not exist
LINE 1: ...es AS categories_categories_default_event_themes, categories...
^
[SQL: SELECT categories.categories.event_message AS categories_categories_event_message, categories.categories.description AS categories_categories_descrip
tion, categories.categories.no_access_contact AS categories_categories_no_access_contact, categories.categories.id AS categories_categories_id, categories.
categories.parent_id AS categories_categories_parent_id, categories.categories.is_deleted AS categories_categories_is_deleted, categories.categories.positi
on AS categories_categories_position, categories.categories.visibility AS categories_categories_visibility, categories.categories.icon_metadata AS categori
es_categories_icon_metadata, categories.categories.logo_metadata AS categories_categories_logo_metadata, categories.categories.timezone AS categories_categ
ories_timezone, categories.categories.default_event_themes AS categories_categories_default_event_themes, categories.categories.event_creation_mode AS cate
gories_categories_event_creation_mode, categories.categories.event_creation_notification_emails AS categories_categories_event_creation_notification_emails
, categories.categories.event_message_mode AS categories_categories_event_message_mode, categories.categories.suggestions_disabled AS categories_categories
_suggestions_disabled, categories.categories.notify_managers AS categories_categories_notify_managers, categories.categories.is_flat_view_enabled AS catego
ries_categories_is_flat_view_enabled, categories.categories.default_ticket_template_id AS categories_categories_default_ticket_template_id, categories.cate
gories.default_badge_template_id AS categories_categories_default_badge_template_id, categories.categories.title AS categories_categories_title, categories
.categories.protection_mode AS categories_categories_protection_mode, (SELECT count(attachments.attachments.id) AS count_1
FROM attachments.attachments JOIN attachments.folders ON attachments.attachments.folder_id = attachments.folders.id
WHERE NOT attachments.folders.is_deleted AND NOT attachments.attachments.is_deleted AND attachments.folders.category_id = categories.categories.id) AS anon
_1, EXISTS (SELECT 1
FROM events.events
WHERE events.events.category_id = categories.categories.id AND NOT events.events.is_deleted) AS anon_2, categories_1.id AS categories_1_id, categories_1.ti
tle AS categories_1_title, categories_1.protection_mode AS categories_1_protection_mode
FROM categories.categories LEFT OUTER JOIN categories.categories AS categories_1 ON categories_1.parent_id = categories.categories.id AND NOT categories_1.
is_deleted
WHERE categories.categories.id = %(id_1)s AND categories.categories.is_deleted = false ORDER BY categories_1.position]
[parameters: {'id_1': 0}]
(Background on this error at: https://sqlalche.me/e/14/f405)
