Обсуждение: Posgres Upgrade With Postgis

Поиск
Список
Период
Сортировка

Posgres Upgrade With Postgis

От
Roland Che
Дата:
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.