[BUG] Taiga database could not be upgraded to Postgresql 14

[patrickdung]

Describe the bug
Taiga database could not be upgraded to PostgreSQL v14

How can we reproduce the behavior
Upgrade an existing Taiga database.

Detailed steps to reproduce the behavior.
When I run the pg_upgrade, it stopped at taiga database:

pg_restore: creating AGGREGATE "public.array_agg_mult("anyarray")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 1096; 1255 23138 AGGREGATE array_agg_mult("anyarray") taiga
pg_restore: error: could not execute query: ERROR:  function array_cat(anyarray, anyarray) does not exist
Command was: CREATE AGGREGATE "public"."array_agg_mult"("anyarray") (
    SFUNC = "array_cat",
    STYPE = "anyarray",
    INITCOND = '{}'
);

Seems to related to this problem:
https://www.postgresql.org/message-id/20210520232922.GG3676%40telsasoft.com

Taiga environment
Self hosting Taiga version 6 (2021-May) on Linux

Workaround
I put the Taiga database in another PostgreSQL 13 instance and then it is fine.

[hoxnox]

As far as I figured out, this function is used only during migration process from one taiga version to another.

https://github.com/kaleidos-ventures/taiga-back/blob/44800319ddf1a39ee072db185246091b8facbe4e/taiga/projects/migrations/0046_triggers_to_update_tags_colors.py

So if you not planing to update taiga, you could just drop that aggregate before postgresql upgrade.

drop aggregate array_agg_mult(anyarray);

After that you could try to create the array_agg_multi in the new DB, or fix the function it uses in the new DB.

create aggregate array_agg_mult(anyarray) (sfunc = 'array_cat', stype = 'anyarray', initcond = '{}');

[LouisSung]

I was creating a new Taiga instance (instead of migrating from an old one) with PostgreSQL 14.2
I mounted a tmp6.5.1_0046.py to override /taiga-back/taiga/projects/migrations/0046_triggers_to_update_tags_colors.py (docker path) with lines #L49 and #L51 modified (by replacing anyarray as anycompatiblearray)

And the modified part would look like:

# issue54: fix 0046_triggers_to_update_tags_colors.py#L45-L55

        # Function: aggregates multi dimensional arrays
        migrations.RunSQL(
            """
            DROP AGGREGATE IF EXISTS array_agg_mult (anyarray);
            CREATE AGGREGATE array_agg_mult (anycompatiblearray)  (
                SFUNC     = array_cat
               ,STYPE     = anycompatiblearray
               ,INITCOND  = '{}'
            );
            """
        ),

Not sure if it's the correct way or also works for an existing instance, but it seems work to a new one 🤔
Once the migration is done, it's OK to unmount the tmp.py script

[ibotty]

I just ran into it today for a new clean install on postgresql 14. Maybe this ought to be fixed?

[jdaviescoates]

FYI, Cloudron users are hitting this issue too now that Cloudron uses Postgresql 14 it's no longer possible to install Taiga on Cloudron.

https://forum.cloudron.io/post/63991

[stefanoginella]

Another Cloudron user here, and I hit the same issue when I tried to install Taiga.

[Promentum]

Also another Cloudron user here, had the same issue. Have taiga acknowledged the issue?

[yamila-moreno]

We believe this tutorial may help you through the process: https://community.taiga.io/t/migrate-taiga-docker-from-postgresql-12-to-postgresql-14/1266

[yamila-moreno]

@LouisSung for new instances from scratch (without data migration) we're preparing some little changes in the official image and will be available in the next release.

[LouisSung]

@yamila-moreno good to hear this issue is under tracking :D
We have been running well with Postgres 14 for a year by overwriting the 0046_triggers_to_update_tags_colors.py migration script as mentioned above

We are not using Cloudron but a standalone docker image from scratch, so not sure if your migration tutorial helps to them
I guess they might bump into the issue similar to us because they are trying to install a new instance using GUI?

Also noticed the discussion thread on the forum: https://community.taiga.io/t/postgresql-14-compatibility/1249
and the Twitter post: https://twitter.com/taigaio/status/1660623957567983616

[yamila-moreno]

the tutorial tries to help with generic migration databases commands, with the example for docker, that should be useful in other environments as well.

For your specific case, if you're already running with p14, that's great; maybe you find useful to check the expected changes to the migration: https://github.com/kaleidos-ventures/taiga-back/pull/131/files#diff-a537144d69b23ceb2117faee15125c39173bd5b590773089f5cdce1275001703R15

Cheers!

[jdaviescoates]

and the Twitter post: https://twitter.com/taigaio/status/1660623957567983616

Just for completeness, that tweet says:

Currently, Taiga is only compatible with max version 12. We’ve created this issue 2 in our tracker to make it compatible with Postgres 14. We'll try to include it in the next release.

You can check the status here.
https://tree.taiga.io/project/taiga/issue/5196

[LouisSung]

For your specific case, if you're already running with p14, that's great; maybe you find useful to check the expected changes to the migration: https://github.com/kaleidos-ventures/taiga-back/pull/131/files#diff-a537144d69b23ceb2117faee15125c39173bd5b590773089f5cdce1275001703R15

Do a quick review, so the array_agg_mult as a temporal aggregate function got created on-the-fly and dropped immediately at the end of 0046 migration script, quite makes sense 🤔
(BTW, not sure if the indentation is accidentally messed up? (should be 8 spaces instead of 9?)

[yamila-moreno]

@LouisSung you were right; I've fixed spaces in the migration file, thanks!

This issue was in the first place about the migration to postgres14, so I'm closing it as the tutorial should be a good solution.

[gramakri]

Just to clarify: one can upgrade existing Taiga installation to use Postgresql 14 but new installations still cannot use Postgresql 14. Is that correct (until a new Taiga release is made)?

[yamila-moreno]

New installations could use postgres14 with a bit of work; to have it working out of the box you may want to wait until the team release a new official image.