[postgis-users] Postgis docker upgrade error

Thu Feb 2 21:30:00 PST 2023

Okay st_value, st_summarystats those are part of postgis_raster extension now.

PostGIS raster support was broken out as a separate extension from postgis extension in 3.0.

So for PostGIS 3+, do in addition to CREATE EXTENSION postgis;

CREATE EXTENSION postgis_raster;

And you’ll have the:

st_value()

st_summarystats()

st_count()

st_quantile()

and 100 other functions.

Now your others:

st_accum()  - this was removed a while back.  

I thought we did it before PostGIS 2.5, but it’s possible it was done in 3.0.

This function is redundant with the array_agg built into PostgreSQL, so we removed this function since people are better off using array_agg.

I think st_accum was a fork of array_agg in the early days so we could provide such functionality to users on something like PostgreSQL 8.1 that didn’t have the new array_agg function.

st_shift_longitude() – this was renamed to ST_ShiftLongitude to be consistent with naming of other functions

st_point_inside_circle() – this function again was renamed

https://postgis.net/docs/ST_PointInsideCircle.html

But you really shouldn’t be using it at all, it was only useful if ever it was useful in the early PostGIS pre- 1.0.  ST_DWithin is a much better function

So:

SELECT ST_PointInsideCircle(ST_Point(1,2), 0.5, 2, 3); 

Is equivalent to: 
ST_DWithin(ST_Point(1,2), ST_Point(0.5,2), 3);

These still exist in PostGIS in the postgis extension
CREATE EXTENSION postgis;

st_askml()

st_buffer() – this is still there (one for geometry and one for geography) but has changed over the years to allow more args, so the older versions were dropped - https://postgis.net/docs/ST_Buffer.html 

st_distance()

Maybe you are looking at the output of your restore and it not restoring these.

I suspect it’s just older versions of these, the args of these functions have changed over the years. When we added another default arg to these functions, we dropped the older version.  Also if you are restoring from say a PostGIS < 3  backup that was installed without using extensions, then the functions were part of the backup, and the library files where named differently back then, so all the functions in your backup will fail restore.  This is expected and nothing to be concerned about.

If you installed with extensions then these functions shouldn’t be in your backup at all.  The only thing you need to be cautious of is if you were using raster.

You need to create the extensions first in your database

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_raster;

# or if you installed in specific schema

CREATE EXTENSION postgis SCHEMA name_schema_you_installed_in;

CREATE EXTENSION postgis_raster SCHEMA name_schema_you_installed_in;

For restoring a backup from a database that used extensions and didn’t user any raster functionality, you can just restore your backup as the CREATE EXTENSION postgis; 

command will already be part of the backup.

Hope that helps,

Regina

From: Devendra Yadav [mailto:devendrayadavuae at gmail.com] 
Sent: Friday, February 3, 2023 12:02 AM
To: Regina Obe <lr at pcorp.us>
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: Postgis docker upgrade error

Thanks Regina for extending support, really appreciate it

There are quite a lot, I'll list a few of them - 

st_accum()

st_value()

st_summarystats()

st_shift_longitude()

st_point_inside_circle()

st_askml()

st_buffer()

st_count()

st_quantile()

st_distance()

Regards,

Devendra Yadav 

On Thu, 2 Feb, 2023, 6:14 pm Regina Obe, <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

Which function is that?

From: Devendra Yadav [mailto:devendrayadavuae at gmail.com <mailto:devendrayadavuae at gmail.com> ] 
Sent: Thursday, February 2, 2023 3:49 AM
To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: Re: Postgis docker upgrade error

Yeah actually I wanted to find a function that is available with version 2 of postgis and couldn't see in postgis version 3. 

So was trying to upgrade to 3.3.2 to see if that works.

Regards

Devendra Yadav 

On Wed, 1 Feb, 2023, 7:57 pm Regina Obe, <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

I didn’t understand the below

“It didn't solve the purpose I was doing so, but some simple things to always remember while working with docker.”

You are still having issues?

Thanks,

Regina

From: Devendra Yadav [mailto:devendrayadavuae at gmail.com <mailto:devendrayadavuae at gmail.com> ] 
Sent: Wednesday, February 1, 2023 10:26 AM
To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: Postgis docker upgrade error

That helped

Thanks a lot Regina, much appreciated ☺️

It didn't solve the purpose I was doing so, but some simple things to always remember while working with docker.

Regards

Devendra 

On Wed, 1 Feb, 2023, 7:04 pm Regina Obe, <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

Devendra sorry just realized I was getting your email because I’m an owner of postgis-users.

So perhaps you aren’t registered or were unregistered because your email bounced at one point in time.

You can reregister here - https://lists.osgeo.org/mailman/listinfo/postgis-users

Regarding your question below:

I just tested and ran into the same issue.  That issue happens if when you setup your docker container, you didn’t specify a POSTGRES_USER.  I think this is a bug either in instructions or the container script not defaulting to postgres if a POSTGRES_USER is not specified when docker run is set.

Work around for that, you can specify at exec time as follows:

docker exec -e POSTGRES_USER=postgres some-postgis update-postgis.sh

Hope that helps,

Regina

From: Devendra Yadav [mailto:devendrayadavuae at gmail.com <mailto:devendrayadavuae at gmail.com> ] 
Sent: Wednesday, February 1, 2023 9:29 AM
To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
Cc: postgis-users-owner at lists.osgeo.org <mailto:postgis-users-owner at lists.osgeo.org> 
Subject: Re: Postgis docker upgrade error

Thanks for confirming Regina

I am able to connect via psql using postgres user

I am using the image from here - https://registry.hub.docker.com/r/postgis/postgis/ 

And I am running the following command to upgrade postgis  - 

docker exec some-postgis update-postgis.sh

Regards,

Devendra Yadav 

On Wed, 1 Feb, 2023, 6:20 pm Regina Obe, <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

This came thru fine. 

1) Which docker image are you using and how are you connecting to it?

2) What command are you using – exactly to connect via psql

Can you connect by entering the docker container

Sounds like you are using the root account instead of something

psql -h localhost -p 5432 -U postgres

Thanks,

Regina

From: postgis-users [mailto:mailman-bounces at lists.osgeo.org <mailto:mailman-bounces at lists.osgeo.org> ] On Behalf Of Devendra Yadav
Sent: Wednesday, February 1, 2023 5:13 AM
To: postgis-users-owner at lists.osgeo.org <mailto:postgis-users-owner at lists.osgeo.org> 
Subject: Re: Postgis docker upgrade error

Why is it getting rejected even when I have subscribed?

On Wed, 1 Feb, 2023, 2:11 pm , <postgis-users-owner at lists.osgeo.org <mailto:postgis-users-owner at lists.osgeo.org> > wrote:

Your message has been rejected, probably because you are not
subscribed to the mailing list and the list's policy is to prohibit
non-members from posting to it.  If you think that your messages are
being rejected in error, contact the mailing list owner at
postgis-users-owner at lists.osgeo.org <mailto:postgis-users-owner at lists.osgeo.org> .

---------- Forwarded message ----------
From: Devendra Yadav <devendrayadavuae at gmail.com <mailto:devendrayadavuae at gmail.com> >
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
Cc: 
Bcc: 
Date: Wed, 1 Feb 2023 14:11:16 +0400
Subject: Postgis docker upgrade error

Hello Experts,

I am running a docker image of postgis (latest one) from docker hub.

I am unable to proceed with postgis update. Could someone be able to assist with the error below - 

Updating PostGIS extensions 'template_postgis' to 3.3.2

PSL: error connection to server on socket "var/run/postgresql/s.PGSQL.5432" failed: FATAL: role "root" does not exist

I tried passing th postgres use explicitly, didn't help. 

Regards

Devendra Yadav 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230203/a7f8f2dd/attachment.htm>