Обсуждение: postgis after pg_upgrade

On 12/1/18 8:56 AM, Slavcho Trnkovski wrote:
> Hi,
> 
> This will not resolve the issue I have because extension is already to 
> the latest version, but it is using postgres 9.4 and it should use 9.6.

Well according to below:

POSTGIS="2.4.5 r16765"

According to this:

http://postgis.net/source/

postgis-2.4.6.tar.gz

So I am thinking it is not at the latest version.


> 
> Regards,
> Slavcho
> 
> On Fri, Nov 30, 2018 at 6:01 PM Paul Ramsey <pramsey@cleverelephant.ca 
> <mailto:pramsey@cleverelephant.ca>> wrote:
> 
>     If you install the new version, and then use ‘ALTER EXTENSION
>     UPDATE’ to update the SQL-side bindings, everything should improve.
> 
>>     On Nov 30, 2018, at 5:11 AM, Slavcho Trnkovski
>>     <strnkovski@gmail.com <mailto:strnkovski@gmail.com>> wrote:
>>
>>     Hi,
>>
>>     I have postgresql 9.4 with postgis extension installed (latest
>>     version, 2.4.5). I upgraded postgresql from 9.4 to 9.6.
>>     After upgrading to 9.6 I get the following result
>>     from PostGIS_full_version()
>>      select PostGIS_full_version();
>>                                                                      
>>                                      postgis_full_version
>>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>      POSTGIS="2.4.5 r16765" *PGSQL="94" (procs need upgrade for use
>>     with "96") *GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15
>>     August 2016" GDAL="GDAL 1.11.4, released 2016/01/25"
>>     LIBXML="2.9.1" LIBJSON="0.11" RASTER
>>     (1 row)
>>
>>     Is there any way to resolve this besides recreating the extension?
>>
>>     Regards,
>>     Slavcho
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/30/18 05:11, Slavcho Trnkovski wrote:
> I have postgresql 9.4 with postgis extension installed (latest version,
> 2.4.5). I upgraded postgresql from 9.4 to 9.6. 
> After upgrading to 9.6 I get the following result
> from PostGIS_full_version()
>  select PostGIS_full_version();
> 
>  ... (procs need upgrade for use with "96") ...
> 
> Is there any way to resolve this besides recreating the extension?

If I'm reading the postgis docs correctly, this message specifically
means that you must do a full dump/reload of postgis-enabled databases.

http://postgis.net/docs/manual-2.4/postgis_installation.html#upgrading

> If you can't find the postgis_upgrade*.sql specific for upgrading
> your version you are using a version too early for a soft upgrade
> and need to do a HARD UPGRADE.
>
> The PostGIS_Full_Version function should inform you about the need
> to run this kind of upgrade using a "procs need upgrade" message.
>
> By HARD UPGRADE we mean full dump/reload of postgis-enabled
> databases.

-- 
http://about.me/jeremy_schneider

On 12/6/18 12:40 AM, Slavcho Trnkovski wrote:
> Hi,
> But my understanding is that this approach is used when upgrading PostGIS.

Which maybe necessary when upgrading the database:

http://postgis.net/docs/manual-2.4/postgis_installation.html#upgrading

Which seems to be what is happening in your case, as before you 
reinstalled the extension you got(from OP):

select PostGIS_full_version();

                        postgis_full_version

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  POSTGIS="2.4.5 r16765" *PGSQL="94" (procs need upgrade for use with
"96") *GEOS="3.6.2-CAPI-1.10.2
4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released
2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER
(1 row)



> I'm upgrading postgresql from 9.4 to 9.6 and PostGIS version remains the 
> same (2.4.5).
> If I execute:
>    drop extension postgis;
>    CREATE EXTENSION postgis SCHEMA postgis;
> select PostGIS_full_version();
>                                                                          
>          postgis_full_version
>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   POSTGIS="2.4.5 r16765" PGSQL="96" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" 
> PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 
> 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER
> 
> everything looks OK. Is this wrong?
> 
> Regards,
> Slavcho
> 
> On Tue, Dec 4, 2018 at 7:41 PM Jeremy Schneider 
> <schneider@ardentperf.com <mailto:schneider@ardentperf.com>> wrote:
> 
>     On 11/30/18 05:11, Slavcho Trnkovski wrote:
>      > I have postgresql 9.4 with postgis extension installed (latest
>     version,
>      > 2.4.5). I upgraded postgresql from 9.4 to 9.6.
>      > After upgrading to 9.6 I get the following result
>      > from PostGIS_full_version()
>      >  select PostGIS_full_version();
>      >
>      >  ... (procs need upgrade for use with "96") ...
>      >
>      > Is there any way to resolve this besides recreating the extension?
> 
>     If I'm reading the postgis docs correctly, this message specifically
>     means that you must do a full dump/reload of postgis-enabled databases.
> 
>     http://postgis.net/docs/manual-2.4/postgis_installation.html#upgrading
> 
>      > If you can't find the postgis_upgrade*.sql specific for upgrading
>      > your version you are using a version too early for a soft upgrade
>      > and need to do a HARD UPGRADE.
>      >
>      > The PostGIS_Full_Version function should inform you about the need
>      > to run this kind of upgrade using a "procs need upgrade" message.
>      >
>      > By HARD UPGRADE we mean full dump/reload of postgis-enabled
>      > databases.
> 
>     -- 
>     http://about.me/jeremy_schneider
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com