Re: Upgrade 96 -> 11 - Mailing list pgsql-general

On 9/2/19 2:04 PM, James Sewell wrote:
Please reply to list also.
Ccing list.
>
>
> On Mon, 2 Sep 2019 at 11:56 pm, Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 9/1/19 9:03 PM, James Sewell wrote:
>      > Hi,
>      >
>      > I'm in the process of upgrading from 96 -> 11 (on RHEL 7.3) .
>     Both the
>      > versions have PostGIS 2.5.1 installed and working.
>      >
>      > pg_upgrade fails with:
>      >
>      > pg_restore: [archiver (db)] Error from TOC entry 440; 1259 537086
>     TABLE
>      > tablename databasename
>      > pg_restore: [archiver (db)] could not execute query: ERROR:
>       relation
>      > "public.spatial_ref_sys" does not exist
>      > LINE 39:     "location_pt" "public"."geography"(Point,4283),
>      >
>
>     You used the 11 version of pg_upgrade, correct?
>
>
> Correct
>
>
>
>
>      > On looking further at the sequence of events I can see that:
>      >
>      >  1. The PostGIS extension is created (but somehow the related tables
>      >     such as spatial_ref_sys do not get created)
>      >  2. The tablename table gets created causing the above error
>      >  3. At some point later in the upgrade spatial_ref_sys is to be
>     created
>
>
>     Questions:
>
>     1) How was PostGIS installed on the 9.6.? and 11.? versions?
>     Where extensions used or was the manual method used?
>
>
> Both from extensions, although 9.6 was and older version which was
> upgraded (with ALTER)
>
>
>
>     2) Did you end up with a working install in 11.?
>
>
> Yes. If I create a cluster and start it and create the extension all is
> fine (postgis tabkes created).

So this is a separate cluster from the one you used pg_upgrade on?

In other words when doing pg_upgrade you could not get a working
cluster, correct?

--
Adrian Klaver
adrian.klaver@aklaver.com