Upgrading Postgresql Server 9.5 to 12

Technical assistance
1

Infos:

  • Used Zammad version: 5.4.0-1680612797.66e35235.focal
  • Used Zammad installation type: deb (Debian Package for Ubuntu)
  • Operating system: Ubuntu 20.04
  • Browser + version: Chrome (latest Version), MS Edge (latest Version)

Expected behavior:

  • Zammad should work :slight_smile:

Actual behavior:

I have upgraded, the local installed postgresql 9.5, to a newer version (postgresql 12). After this upgrade its not possible any more to create/change an article. After trying to submit a new Ticket, i got the following error:

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

Trying to create a new article on a existing ticket resolves with this error:

* PG::SyntaxError: ERROR: syntax error at or near "groups" LINE 2: ... select stringagg('g' *organization**id* 'g',' ') groups ^ QUERY: SELECT ( select stringagg('g' *organization**id* 'g',' ') groups from ( select distinct organization_id from public.ticket_articles a inner join public.users u on a.createdbyid = u.id where a.ticket_id=NEW.id order by 1 ) x ) CONTEXT: PL/pgSQL function triggerticketsgroup_permissions() line 5 at assignment

I should also say, that we have create 3 new (ticket) object *due_date", “organization_id”, employee_id
I have tried to restart the server without success.

Steps to reproduce the behavior:

  • Upgrade to a newer version of Postgresql
sudo pg_dropcluster 12 main --stop
sudo pg_upgradecluster 9.5 main
2

We use docker-compose, and I’m not sure how much of this applies to package installations. I’m also not so familiar with Postgres. So take my experience with a grain of salt.

We recently upgraded from 5.2.3 to 5.4.1, which required switching from Postgres 9 to 13.

On docker-compose, it’s not possible to use the same database files when moving from 9 to 13. Moving from Postgres 9 to 13 required restoring from backup into a fresh DB.

I wasn’t able to find any information in Zammad release notes, but if this “restore from backup” requirement also applies to Postgres upgrades on package installs, that might explain your problem.

For reference, here are the docker instructions for migrating from Postgres 9 to 13:

And here is more general documentation on Zammad backups and database restores:

https://docs.zammad.org/en/latest/install/update.html

https://docs.zammad.org/en/latest/appendix/backup-and-restore/index.html

https://docs.zammad.org/en/latest/appendix/migrate-to-postgresql.html