10.1 update: PostgreSQL 18 migration fails on Taiga's dump and leaves the whole server with all apps down

[imc67]

Last night one of our three Cloudrons auto-updated from 10.0.5 to 10.1.3. It ended with 24 of our 30 apps down for over five hours, from about 01:10 to 06:24 UTC, and the server did not recover by itself, not even after a restart of the box. The trigger was a single app (Taiga) whose database dump PostgreSQL 18 refuses. What turned that into a full outage is how the platform handles that failure. Below is the root cause, the workaround that got us back, and some suggestions, because this will hit every Cloudron with Taiga installed.

What happened

After the update, startPostgresql exports the PostgreSQL addon databases (Taiga and Pretix), removes the old data dir (rmaddondir.sh postgresql), starts the new cloudron/postgresql:7.0.0 (PostgreSQL 18) and imports the dumps again. The Taiga import fails:

ERROR:  syntax error at or near "json" at character 53
STATEMENT:  CREATE FUNCTION public.json_object_delete_keys(json json, VARIADIC keys_to_delete text[]) RETURNS json
restore: failed to restore database db45c50160… Error: process exited with code 3

The box marks Taiga as errored ("PostgreSQL restore failed. This may require more memory." — memory had nothing to do with it) and moves on to Pretix, and there it hangs: importing addon postgresql of app 58ebed9d… / Setting up postgresql, then nothing. The request never reached the addon (nothing in the postgresql log); the box's socket to 172.18.30.2:3000 had 3.7 MB stuck in the send queue, which looks like the rest of the Taiga dump that was still being piped when the addon gave up. onInfraReady never ran, so 23 apps stayed in pending_restart (plus Taiga in error) with "Waiting for platform to initialize".

The dashboard meanwhile showed the banner "Starting PostgreSQL service" while every service on the Services page was green. The first run (01:10) even ended in an uncaughtException (Unexpected status code 500 from pipework) and a box restart; that restart reproduced the hang exactly, and so did a manual docker restart postgresql + systemctl restart box this morning.

Root cause

Taiga creates its own helper function json_object_delete_keys with a parameter that is literally named json (of type json, and a jsonb variant). The dump header says:

-- Dumped from database version 16.14
-- Dumped by pg_dump version 16.14

For PostgreSQL 16 an unquoted json is a valid parameter name, so pg_dump 16 sees no reason to quote it. PostgreSQL 18 rejects it. I verified this in the new 7.0.0 container: the original statement gives the syntax error above, the same function with "json" json is created and works. The data in Taiga is irrelevant; the function exists from the moment Taiga is installed, so an unused Taiga breaks just as well as a busy one.

So the migration dumps with the old major version's pg_dump and restores into the new major version. That is the classic way to get bitten by keyword changes between majors; the usual practice is to dump with the new version's pg_dump, or at least use pg_dump --quote-all-identifiers, which exists exactly for dumps that go to a different major version.

Workaround that brought the server back

1. Copy the dumps somewhere safe first. After rmaddondir.sh the files in appsdata/<appid>/postgresqldump (and the pre-update backup) are the only copies of these databases.
2. Quote the parameter name in Taiga's dump (four lines: CREATE and ALTER for both variants), keep the owner:

F=/home/yellowtent/appsdata/<taiga-app-id>/postgresqldump
sed -i 's/json_object_delete_keys(json json,/json_object_delete_keys("json" json,/; s/json_object_delete_keys(json jsonb,/json_object_delete_keys("json" jsonb,/' "$F"
chown yellowtent:yellowtent "$F"
systemctl restart box

The box then sees "already exported addon postgresql in previous run", imports both databases in two seconds, recreates the redis containers and reports "platform is ready"; the apps start by themselves. Taiga and Pretix stayed in error though: their pending task fails with "Unknown install command in apptask:error", so both need a manual Repair. Pretix's data was complete afterwards (order count identical to the dump).

Suggestions

• Dump with the target version (or with --quote-all-identifiers) when migrating between PostgreSQL majors. This one is probably a one-line fix and would have prevented all of the above.
• One app's database must not be able to block the platform. If an import fails, mark that app as errored, close the connection properly and continue with the next app and with onInfraReady. Here a single unused app kept 23 other apps, among them a dozen WordPress sites, a ticket shop and two helpdesks, offline for hours.
• Put a timeout on the addon requests during the import, so a stuck socket ends in an error instead of waiting forever.
• Don't remove the old data dir before the import has succeeded, or keep the export in a place that survives a retry. As it is, the only way back is the dumps or a full restore.
• Make the state visible. "Starting PostgreSQL service" with all services green gives an admin nothing to go on at 7 in the morning. The import error and the app it concerns belong in the notification and on the Services page, and the error message should not suggest a memory problem when PostgreSQL reported a syntax error.
• Mention it in the release notes: a major PostgreSQL migration happens in 10.1, apps with their own SQL functions are at risk, and admins with Taiga should expect this.

I get that a major database migration on many installs is not trivial, and in general updates have been smooth for us. But this was an automatic night-time update, and the result was a server that stayed down until someone dug through the logs. Our other two Cloudrons stay on 10.0.5 for now. Can you confirm whether other appstore apps ship SQL functions or identifiers that PostgreSQL 18 no longer accepts unquoted, and whether a fix will land before the next release?

[james]

Hello @imc67

Thank you very much for the detailed report!

Suggestions:

• Dump with the target version (or with --quote-all-identifiers)
  Will have to look into it if one or the other option is better.

• One app's database must not be able to block the platform
  Will need some thoughts from the team.

• Put a timeout on the addon requests during the import
  I have come to dislike static timeouts.
  With a check if it actually is doing something, in connection with a timeout, then I'd like it.

• Don't remove the old data dir before the import has succeeded
  Sounds good.

• Make the state visible
  Agreed, it should be visible somewhere.

• Mention it in the release notes
  Good point.

Your suggestions got me also thinking.
Maybe when something errors it could give a clickable link to the log in question for example Task $TASKID failed with error $ERRORMSG - View Log(clickable) in the dashboard.
And if we could add that, if the link would also directly lead to the logged error similar to when linking code in GitHub GitLab $URI#L4-L6 even better.

[girish]

Excellent summary @imc67 . I am fixing these. Will summarize when I get through them all.

The core issue is that it seems that json became a reserved keyword in postgres 18 and the pgdump is not as portable as we thought it was After that it's a bunch of cascading errors.

[girish]

There was a bug that the importer "hung" if the other side (postgres) errored out. That is also fixed now. Will make a new path releases with the fixes.

[girish]

OK, so I had AI go in and check over 70 of our packages if they will hit any keyword conflicts. 2 hours later...

Taiga is the only Cloudron PostgreSQL app whose shipped schema will fail this upgrade. Every other app at the pinned version is clear.

I checked this on PostgreSQL 18.6 first, because it changes what is worth testing. A table column named json, json_table, merge_action, and the rest is legal. These names fail only as a function or procedure argument, or as a RETURNS TABLE column. CREATE TABLE t (json varchar(16)) succeeds. CREATE FUNCTION f(json json) and RETURNS TABLE(json int) fail. So a user column named json in Grist, NocoDB, Baserow, or Directus does not break the restore.

The scan looked for those argument and RETURNS TABLE names in each upstream tree at the Cloudron pin. Taiga 6.10.2 is the only hit: json_object_delete_keys("json" json, ...) in the custom-attribute migrations (the same shape with jsonb in the later migration). A PostgreSQL 16 pg_dump writes that argument unquoted, and PostgreSQL 18 rejects it.

Nothing else matched. That includes GitLab 19.3.2 (1322 functions), Immich (123), NocoDB (51), AFFiNE (32), Baserow (26), Discourse (15), and the ONLYOFFICE 9.4.0 and ONLYOFFICE EE 9.4.0 packages (one function, merge_db, with ordinary argument names). Moodle, Nextcloud, and the rest of the PostgreSQL app list have no function argument or RETURNS TABLE column with these names.

[imc67]

Thanks @girish and @james, that was quick, and it's good to see the fixes already in the box repo (quoting identifiers in the dump, patching an existing Taiga dump, skipping apps in error instead of blocking the platform, and the importer no longer hanging).

I ran the same check on our other two servers: per database, function argument names and type names against the words that are stricter in PG 18 than in 16 (json plus the new json_* keywords and merge_action). Same result as yours: only Taiga. Dawarich has a function json(geometry), but that belongs to PostGIS, so pg_dump leaves it out. We archived the Taiga on the second server, after renaming the parameter in the live database (json → j, Taiga calls the function positionally), so that backup restores cleanly on PG 18.

One question: does the Taiga patch also apply when restoring an app backup or archive that was taken on PostgreSQL 16? That's the case someone hits months from now when they bring an archived Taiga back.

We'll keep the other two servers on 10.0.5 until the patch release is out.

[girish]

Yes, the patch is applied at restore time, so restore from some old backup and archive will work. I would say you should take a more recent backup though after upgrading to 10.1.x. It will create dumps with the correct quoting. I don't think we will maintain this taiga specific patch for many releases. It's just a hack right now to move forward.