Posgres Upgrade With Postgis

From: Roland Che <rolandngwa31(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Posgres Upgrade With Postgis
Date: 2023-08-11 14:13:17
Message-ID: CAKnNPjx=knozebp_ykxMZggb3jesAU9Bc+1tVN1NrTRyyhjfkw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello Everyone i am trying to upgrade postgres and postgis with below
steps following
the postgis compartibility with postgis.

postgres 9.6( postgis 2.3) -> postgres 10 ( postgis 2.3) ( postgres
upgrade)

postgres 10 (postgis 2.3) -> postgres 10 (postgis 2.5) ( postgis upgrade)

postgres 10 ( postgis 2.5) -> postgres 11 ( postgis 2.5) ( postgres upgrade)

postgres 11 ( postgis 2.5) -> postgres 11 ( postgis 3.1) ( postgis upgrade)

postgres 11 ( postgis 3.1) -> postgres 14 ( postgis 3.1) ( postgres upgrade)

So what i have done so far is .

1) First i have upgrade postgres 9.6 with postgis 2.3 to postgres 10 with
same postgis 2.3.
i installed postgres 10 and installed postgres contrib, then for postgis,
i copied the postgis
libraries from postgres 9.6 to postgres 10 and also installed "yum install
postgis23_10"
and i think its the compartible postgis 2.3 for version 10 i installed
made the pg_upgrade successful
and as postgres 9.6 and 10 postgis functions are compartible.
>>>>>>So from postgres 9.6 to 10, i was able to run pg_upgrade
successfully.

2) Upgrading from postgres 10 to postgres 11 but have to upgrade postgis
2.3 to postgis 2.5 as those are compartible.

So far postgres 10 already has postgis23_10 installed as i did when i
wanted to upgrade from 9.6 to 10.
So i went ahead and installed "yum install postgresql11-contrib and yum
install postgis25_11" so as to see if i can
have an upgrade path from postgis 2.3 to 2.5 as postgres 11 is compartible
with postgis 2.5.

Upgrade of Postgis will fail and reading from postgis website i see not all
can be upgraded using the alter command.

\dx that list entensions will not bring up postgis.

xxxxx=# \dx
List of installed extensions
Name | Version | Schema |
Description
--------------------+---------+------------+--------------------------------------------------------------
hstore | 1.4 | public | data type for storing sets of
(key, value) pairs
pg_repack | 1.4.6 | public | Reorganize tables in
PostgreSQL databases with minimal locks
pg_stat_statements | 1.6 | public | track execution statistics of
all SQL statements executed
pgcrypto | 1.3 | public | cryptographic functions
pgstattuple | 1.5 | public | show tuple-level statistics
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
uuid-ossp | 1.1 | public | generate universally unique
identifiers (UUIDs)
(7 rows)

xxxxx=# SELECT postgis_full_version();
NOTICE: Function postgis_gdal_version() not found. Is raster support
enabled and rtpostgis.sql installed?
NOTICE: Function postgis_raster_scripts_installed() not found. Is raster
support enabled and rtpostgis.sql installed?
NOTICE: Function postgis_raster_lib_version() not found. Is raster support
enabled and rtpostgis.sql installed?

postgis_full_version

---------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="2.3.7 r16523" GEOS="3.9.2-CAPI-1.14.3" PROJ="Rel. 4.9.3, 15
August 2016" LIBXML="2.9.1" LIBJSON="0.11" (core procs from "2.3.2 r15302"
need upgrade)
(1 row)

xxxxx=# SELECT postgis_extensions_upgrade();
ERROR: function postgis_extensions_upgrade() does not exist
LINE 1: SELECT postgis_extensions_upgrade();
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.

xxxxx=# ALTER EXTENSION postgis UPDATE TO '2.5.5';
ERROR: extension "postgis" does not exist
xxxxx=# ALTER EXTENSION postgis UPDATE TO '2.5';
ERROR: extension "postgis" does not exist
xxxxxx=# ALTER EXTENSION postgis UPDATE;
ERROR: extension "postgis" does not exist

PLEASE CAN SOMEONE ASSIST TO EXPLAIN MY OPTIONS TO UPGRADE POSTGIS?

THANKS ALL.

Browse pgsql-admin by date

  From Date Subject
Next Message Ron 2023-08-11 14:24:43 Re: .history': No such file or directory - a symptom of ?
Previous Message Srinivasan Ramkumar 2023-08-11 10:40:59 Re: Postgres -repmgr issue