From: Justin Pryzby <pryzby@telsasoft.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: pg_upgrade: fail early if a tablespace dir already exists for new cluster version
Date: Mon, 12 Oct 2020 12:33:39 -0500
Message-ID: <20201012173339.GG9241@telsasoft.com> (raw)
In-Reply-To: <20201009234251.GA8419@momjian.us>
References: <20200925005531.GJ23631@telsasoft.com>
<20201009185325.GA24870@momjian.us>
<20201009192310.GS17626@telsasoft.com>
<20201009234251.GA8419@momjian.us>
On Fri, Oct 09, 2020 at 07:42:51PM -0400, Bruce Momjian wrote:
> On Fri, Oct 9, 2020 at 02:23:10PM -0500, Justin Pryzby wrote:
> > In my local branch, I had revised this comment to say:
> >
> > + * Note, v8.4 has no tablespace_suffix, which is fine so long as the version we
> > + * being upgraded *to* has a suffix, since it's not allowed to pg_upgrade from
> > + * a version to the same version if tablespaces are in use.
>
> OK, updated patch attached. Also, from your original patch, I didn't
> need to call canonicalize_path() since we are not comparing paths, and I
> didn't need to include common/relpath.h. I also renamed a variable.
Since I just hit it again, I'll take the opportunity to give an example of how
this can happen.
Here, I've pg_upgraded from a pg12 to pg13, but the pg12 cluster has
postgis-3.0, and pg13 has postgis-3.1. Maybe that's not guaranteed/intended to
work, but that's what's been working well so far this year, except that there's
two gis functions which no longer exist. So we fail while "Restoring database
schemas in the new cluster", leaving behind tablespace dirs, which then cause
future pg_upgrades to fail.
pg_restore: from TOC entry 13543; 1255 17106 FUNCTION pgis_geometry_union_transfn("internal", "public"."geometry") postgres
pg_restore: error: could not execute query: ERROR: could not find function "pgis_geometry_union_transfn" in file "/usr/pgsql-13/lib/postgis-3.so"
Command was: CREATE FUNCTION "public"."pgis_geometry_union_transfn"("internal", "public"."geometry") RETURNS "internal"
LANGUAGE "c" PARALLEL SAFE
AS '$libdir/postgis-3', 'pgis_geometry_union_transfn';
I imagine in previous years I hit that some other way, like maybe I installed
postgres RC1 on a customer server, imported their schema (and maybe data),
which then caused upgrade failure 1-2 months later when trying to upgrade their
production instance.
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, bruce@momjian.us
Subject: Re: pg_upgrade: fail early if a tablespace dir already exists for new cluster version
In-Reply-To: <20201012173339.GG9241@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox