postgis after pg_upgrade

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

postgis after pg_upgrade

Slavcho Trnkovski
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
Reply | Threaded
Open this post in threaded view
|

Re: postgis after pg_upgrade

Paul Ramsey
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: postgis after pg_upgrade

Slavcho Trnkovski
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.

Regards,
Slavcho

On Fri, Nov 30, 2018 at 6:01 PM Paul Ramsey <[hidden email]> 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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: postgis after pg_upgrade

ichbinrene
Are the postgis functions usable? do they currently work ? any error messages in the database server logs ? have you installed the postgis binaries for postgres 9.6? if you follow the given advise by Paul Ramsey, what happens? 

Am So., 2. Dez. 2018 um 14:24 Uhr schrieb Slavcho Trnkovski <[hidden email]>:
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.

Regards,
Slavcho

On Fri, Nov 30, 2018 at 6:01 PM Paul Ramsey <[hidden email]> 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 <[hidden email]> 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



--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Reply | Threaded
Open this post in threaded view
|

Re: postgis after pg_upgrade

Adrian Klaver-4
In reply to this post by Slavcho Trnkovski
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 <[hidden email]
> <mailto:[hidden email]>> 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
>>     <[hidden email] <mailto:[hidden email]>> 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
[hidden email]

jer
Reply | Threaded
Open this post in threaded view
|

Re: postgis after pg_upgrade

jer
In reply to this post by Slavcho Trnkovski
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

Reply | Threaded
Open this post in threaded view
|

Re: postgis after pg_upgrade

Slavcho Trnkovski
Hi,
But my understanding is that this approach is used when upgrading PostGIS.
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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: postgis after pg_upgrade

Adrian Klaver-4
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
> <[hidden email] <mailto:[hidden email]>> 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
[hidden email]