FS#74669 - [postgresql] cannot be upgraded from 13 to 14

Attached to Project: Arch Linux
Opened by Andrej Podzimek (andrej) - Saturday, 07 May 2022, 06:29 GMT
Last edited by Levente Polyak (anthraxx) - Thursday, 12 May 2022, 20:51 GMT
Task Type Bug Report
Category Packages: Extra
Status Closed
Assigned To No-one
Architecture All
Severity High
Priority Normal
Reported Version
Due in Version Undecided
Due Date Undecided
Percent Complete 100%
Votes 0
Private No


This should have been a HUGE announcement on the ArchLinux front page. But all I can see is deep silence w.r.t. PostgreSQL.

TL;DR: The recent upgrade to PostgreSQL 14.x breaks *everything*, rendering pre-existing PostgreSQL servers inoperable. The only way to restore law and order is to keep 13.x and hope for the best. Forget about pg_upgrade; it just doesn’t work. You will have to restore everything manually, INSERT by INSERT.

How about this? https://githubhot.com/repo/zalando/spilo/issues/602 Hint: Nope, that doesn’t work/help either.

Additional info:
* package version(s)
No precise numbers are needed; this is all about PostgreSQL 13 to 14 upgrades.

* config and/or log files etc.
could not execute query: ERROR: column reference "backend_type" is ambiguous
pg_restore: error could not execute query: ERROR: column reference "backend_type" is ambiguous
LINE 34: "get_pg_stat_activity"."backend_type"

* link to upstream bug report, if any

None. This is a distro error. If something doesn’t work, then *please* file an upstream bug, but don’t just let such a disaster pass from “testing” into “extra”, hoping that random users will magically fix it. (They most likely won’t!)

Steps to reproduce:
Upgrade to PostgreSQL 14.x.
Who has a working PostgreSQL 14.x? Anyone…? Thought so. Ideally just don’t upgrade from 13. This is really bad.
This task depends upon

Closed by  Levente Polyak (anthraxx)
Thursday, 12 May 2022, 20:51 GMT
Reason for closing:  Not a bug
Comment by Levente Polyak (anthraxx) - Saturday, 07 May 2022, 10:25 GMT
I dont see any distro error here, please follow the instructions in the wiki especially related to the usage of `postgresql-old-upgrade` and specific migration strategies on major version upgrades.