Update postgis in a replicated PostgreSQL 12 database now with postgis-3

[Andrew Hardy]

Hi Sandro,

Thank you so much for your kind input to this issue, I really
appreciate it.  I have one final question:

-- Subsequently I have now decided we do not actually need the raster
functionality, so I am thinking I could remove it before trying to load v12
database into v16.

-- However I want to remove it carefully, giving thought to my earlier mistaken
update of pg_proc that we have been discussing.

--

-- I am following
https://postgis.net/documentation/tips/tip-removing-raster-from-2-3/

-- Here are my findings so far and my KEY QUESTIONS at the bottom:

--



ALTER EXTENSION postgis UPDATE;

-- This has already been run a while earlier to solve the more general
problem of not recognising geo types etc after our move to Red Hat 9 and
postgis 3


SELECT postgis_extensions_upgrade();

-- I have not yet run this (neither once nor twice) - I was in the process
in my earlier post of discussing if it would still work given my first
mistake



SELECT postgis_full_extension();

-- function not found


SELECT postgis_full_version();

--POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="120"

--GEOS="3.12.2-CAPI-1.18.2"

--PROJ="9.4.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org
USER_WRITABLE_DIRECTORY=/var/lib/pgsql/.local/share/proj
DATABASE_PATH=/usr/proj94/share/proj/proj.db"

--LIBXML="2.9.13"

--LIBJSON="0.14"

--LIBPROTOBUF="1.3.3"

--WAGYU="0.5.0 (Internal)"

--(raster procs from "2.5.5 r0" need upgrade)


-- I DO NOT SEE [UNPACKAGED!]



SELECT count(1) FROM raster_columns;

-- 0


-- KEY QUESTIONS

--

-- Q1:

-- Given my earlier mistaken update of pg_proc first and the subsequent
findings above following
https://postgis.net/documentation/tips/tip-removing-raster-from-2-3/

-- Taking this mistake into account, I am wondering since I now believe I
do not need the raster functions at all...

-- Whether I can simply successfully run
https://postgis.net/stuff/uninstall_rtpostgis.sql . I presume its dropping
of the functions will also remove rt related rows from pg_proc whether I
broke them or not.

--

-- Q2:

-- Given I have already ran ALTER EXTENSION postgis UPDATE; to solve the
basics of postgis not working

-- If running https://postgis.net/stuff/uninstall_rtpostgis.sql is
successful, will this make loading the v12 DB into v16 a lot easier?

-- Are there any SELECT checks I should make after running
https://postgis.net/stuff/uninstall_rtpostgis.sql to see all is good.


Presumably once things are fully stable in v16 and fully without raster
functions, at some later date we could reintroduce raster if we suddenly
needed it.


Andrew










On Wed, 7 Aug 2024 at 18:07, Sandro Santilli <strk at kbt.io> wrote:

> On Mon, Jul 01, 2024 at 04:37:37PM +0100, Andrew Hardy wrote:
> > Thank you so much for your response, I appreciate it.
> >
> > I omitted to mention that before I messaged I had tried to resolve this
> in
> > the  pg_proc table and ran the following query:
> >
> > UPDATE pg_proc SET probin = '$libdir/postgis-3' WHERE probin =
> > '$libdir/rtpostgis-2.5';
> >
> > This was probably a mistake (not least that postgis-3 is not the raster
> > library) and I am wondering if this is resolvable.
>
> Yes, it was a mistake.
> Running this should fix it:
>
>   SELECT postgis_extensions_upgrade();
>
> > Q1: And I am wondering if the postgis_extensions_upgrade()  mentioned in
> > the upgrade instructions assumes one is starting from a particular state
> > and I need to get back there from my mistake above or if this upgrade
> > function will resolve things whatever the state.
>
> It tries its best to resolve things whatever the state, but I guess it
> could fail, you'll tell us.
>
> > Q2: Should I still run postgis_full_version() - is this function a
> > benign query, unlike postgis_extensions_upgrade()  ?
>
> Always run `postgis_full_version()` to tell what the state of the
> database is.
>
> --strk;
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20240905/91faecd6/attachment.htm>