[postgis-users] No extension

[Simon SPDBA Greener]

When I tried to access my new Postgresql 14 database using qGIS 3.26 I get this when connecting..

2022-09-29T12:09:14     WARNING    Database connection was successful, but the accessible tables could not be determined.
2022-09-29T12:10:41     WARNING    No PostGIS support in the database.

But the nominated database has PostGIS .... in pgAdmin 4

create extension postgis;

ERROR: extension "postgis" already exists SQL state: 42710

regards

Simon

--

Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w) www.spdba.com.au
(m) si...@spdba.com.au	

[Simon SPDBA Greener]

select postgis_full_version();

"POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""140"" GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"""

[Regina Obe]

Do you have PostGIS tables.

 

What does

 

SELECT *

FROM geometry_columns;

 

Return.  I’m guessing maybe QGIS is complaining about lack of spatial tables.

 

Regarding the PostgreSQL 14 / 13.

 

Looked like your PostgreSQL 13 is running on 5432, you could just disable it.  I assume it’s maybe still running in services?

 

I’m not familiar with bitnami so not sure how you completely remove it.

 

 

If you want set your PostgreSQL 14 to run on 5432

You can do ALTER SYSTEM set port = ‘5432’;

 

And then restart your PostgreSQL 14 service.

 

For good measure, you could also edit the registry so when you install new things, it correctly detects the new port

 

Open regedit.exe

 

HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\postgresql-x64-14\

Key: Port

[Simon SPDBA Greener]

Regina,

Thanks for the reply.

> What does

> SELECT * FROM geometry_columns;

> Return.  I’m guessing maybe QGIS is complaining about lack of spatial tables.

It returns a data grid showing all the spatial layers.

> Looked like your PostgreSQL 13 is running on 5432, you could just disable it.  I assume it’s maybe still running in services?

The strange thing was that it had been uninstalled. I removed the rogue connection in PgAdmin 4 and it fixed the problem.

In my new instance I created a database for each of the backup databases, execute "create extension postgis" for all of them, then restored the original data from the backups successfully. I am still getting this in qGIS logs...

2022-09-29T14:38:36     WARNING    No PostGIS support in the database.

2022-09-29T14:38:36     WARNING    Database connection was successful, but the accessible tables could not be determined.

[Regina Obe]

This I suppose could be a permission issue or search path one.

 

Are you using the same account in QGIS to connect as you are in pgAdmin?

 

Are you connecting from QGIS PostGIS layers or via QGIS DBManager menu option?

 

If you haven’t tried DBManager, I would try that to see what tables you are able to see.  That should give you a clue if it’s a permission issue.

[Simon SPDBA Greener]

Regina,

This I suppose could be a permission issue or search path one.

I used pgAdmin 4 to set the search_path parameter to "$user", public, topology for all the databases.

Didn't seem to make a difference.

 Are you using the same account in QGIS to connect as you are in pgAdmin?

Yes, I believe so. All connections use the postgres user who owns all the databases.

Are you connecting from QGIS PostGIS layers or via QGIS DBManager menu option?

Both.

 If you haven’t tried DBManager, I would try that to see what tables you are able to see.  That should give you a clue if it’s a permission issue.

When I connection using DBManager to one of the databases (dfdb) in the instance I still get the following message....

2022-09-30T08:35:24     WARNING    Database connection was successful, but the accessible tables could not be determined.

If I try to connect to another database in the same instance (gisdb) I get

FATAL: database "gisdb" does not exist.

Sorry if this is not that helpful.

regards

Simon

 

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Simon SPDBA Greener
Sent: Thursday, September 29, 2022 2:03 AM
To: postgi...@lists.osgeo.org
Subject: Re: [postgis-users] No extension

 

Regina,

Thanks for the reply.

> What does

> SELECT * FROM geometry_columns;

> Return.  I’m guessing maybe QGIS is complaining about lack of spatial tables.

It returns a data grid showing all the spatial layers.

> Looked like your PostgreSQL 13 is running on 5432, you could just disable it.  I assume it’s maybe still running in services?

The strange thing was that it had been uninstalled. I removed the rogue connection in PgAdmin 4 and it fixed the problem.

In my new instance I created a database for each of the backup databases, execute "create extension postgis" for all of them, then restored the original data from the backups successfully. I am still getting this in qGIS logs...

2022-09-29T14:38:36     WARNING    No PostGIS support in the database.

2022-09-29T14:38:36     WARNING    Database connection was successful, but the accessible tables could not be determined.

regards

Simon

-- 

Simon Greener

39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia

(m) +61 418 396 391

(w) www.spdba.com.au

(m) si...@spdba.com.au

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Simon SPDBA Greener]

I can execute spatial queries in pgAdmin 4 OK.

select count(*)
  from df.pit as p
       inner join
       df.path as t
       on (ST_Intersects(t.path_line,ST_Buffer(p.pit_poly,10)))
 where p.pit_id = 50000

count
2

[Regina Obe]

> If I try to connect to another database in the same instance (gisdb) I get

> FATAL: database "gisdb" does not exist.

Hmm strange it says the database doesn’t exist.

 

You confirmed the port is right the 5433 port (not 5432).

 

Just still sounds like it’s trying to connect to the wrong service or server still.

 

I’d triple check the connection settings, make sure the port and IP/hostname is right.

[Simon SPDBA Greener]

You confirmed the port is right the 5433 port (not 5432).

 Just still sounds like it’s trying to connect to the wrong service or server still.

 I’d triple check the connection settings, make sure the port and IP/hostname is right.

I only have one instance running on my box with multiple databases.

In short I connect to the database DFDB using qGIS with port 5432 and it worked.
I connect to database GISDB using qGIS with port 5432 and it failed but then worked with 5433. THANKS!

Any idea why?

[Simon SPDBA Greener]

Hold that thought.

Port 5432 didn't work on DFDB as I thought it did.

Port 5433 works.

Has the default port changed with database installs?

Simon
> --
> Simon Greener
> 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
> (m) +61 418 396 391
> (w)www.spdba.com.au
> (m)si...@spdba.com.au
>

> _______________________________________________
> postgis-users mailing list
> postgi...@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

--
Simon Greener
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia
(m) +61 418 396 391
(w) www.spdba.com.au
(m) si...@spdba.com.au

[Regina Obe]

When you use the PostgreSQL installer on windows,
If you have a PostgreSQL instance running already on Port 5432, it defaults
the port to the next available increment port.

So my guess is you had your bitnami running on Port 5432, it detected that,
so then installed on Port 5433.

So your old would have 5432, your new PostgreSQL 14 would be 5433.

That's why I mentioned, if you wanted it to run on default PostgreSQL 5432,
you can do the

Run the sql:
ALTER SYSTEM Set port = '5432';

Then restart the service.

And that registry change I mentioned.

Open regedit.exe



HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\postgresql-x64-14\

Key: Port

The registry change you do so if you install other extensions such as
postgis or pgAgent that need to connect to the database, they read the port
number from the registry and default to that. I think pgAdmin4 also when you
first setup reads port from registry.

> -----Original Message-----
> From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On
Behalf
> Of Simon SPDBA Greener
> Sent: Thursday, September 29, 2022 7:50 PM
> To: postgi...@lists.osgeo.org
> Subject: Re: [postgis-users] No extension
>

[Simon SPDBA Greener]

The old postgresql 13 was running on 5432.

I thought I had shut it down before running the 14 installer.

No matter, your suggestion about 5433 was the key I needed to open the door.

Thanks for all your patience and help: greatly appreciated.

regards

Simon

[Simon SPDBA Greener]

I decided to switch the port back to 5432 following your recipe (because
I have many apps that access postgresql).

I can connect using qGIS/pgAdmin 4 etc but when I try to connect using
GeoServer I get this:

...
   at java.base/java.lang.Thread.run(Thread.java:831)
Caused by: java.lang.RuntimeException: Unable to obtain connection:
ERROR: function postgis_lib_version() does not exist
  Hint: No function matches the given name and argument types. You
might need to add explicit type casts.
  Position: 8
    at
org.geotools.jdbc.JDBCDataStore.createConnection(JDBCDataStore.java:2132)

Running the function in pgAdmin 4 returns:

select postgis_lib_version();

"postgis_lib_version"
"3.2.3"

Any ideas?

All other apps work correctly, only GeoServer is returning this error.

regards

Simon

[Alexandre Neto]

Did you uninstall PostgreSQL 13?

[Simon Greener]

Yes, it is uninstalled.

⁣39 Cliff View Drive
Allens Rivulet Tas 7150
(W) 0418 396 391​

>------------------------------------------------------------------------

[Regina Obe]

That is weird. I would have thought maybe a JDBC driver issue, but the error suggests it was able to connect and just not able to access that function. Or that error is just bogus and it really can't connect.

1) Is it using the same account as other apps. Could be a permission issue.
2) Can you do a quick non-postgis test like?

SELECT version();

3) I know there were big changes between PG 11 / PG 12 that necessitated upgrades of drivers, but didn't think much difference between PG13 and PG14. If option 1 and 2 were not useful, I'd suggest just upgrading to the latest JDBC driver from https://jdbc.postgresql.org/download/

> -----Original Message-----
> From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf
> Of Simon Greener
> Sent: Friday, September 30, 2022 4:56 AM
> To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
> Subject: Re: [postgis-users] No extension
>

> Yes, it is uninstalled.
>
> ?39 Cliff View Drive
> Allens Rivulet Tas 7150
> (W) 0418 396 391?

[Bo Victor Thomsen]

What about search path for Postgres ? It could be different for each
client accessing Postgres.

--
Med venlig hilsen / Best regards

Bo Victor Thomsen

[Regina Obe]

Yah that's one I forgot to mention.
Search paths can also be set by applications per session, so you might want
to make sure your java app is not doing that. I've been bitten by that with
some frameworks, where they have a search_path set which is different from
the one I set at the database or user level.

[Simon Greener]

Search path is set. Because it is set in the database it is independent of the port, correct?

Because I need to move forward, I've gone back to using port 5433 and all is working.

Thanks to all for taking the time to help.

Simon

⁣39 Cliff View Drive

Allens Rivulet Tas 7150
(W) 0418 396 391​

[Regina Obe]

Gone back to using 5433 and your java app can connect to that?
If that's the case, then maybe your java app had been changed to 5433 all along and was a connection issue.

Search path is independent of port, but it can be set on a number of levels

1) at the session level
2) at the database, user level - meaning a particular user can have a custom path per database
3) at the database level (applies for all users connecting to the db)
4) at the user level
5) At the server cluster level

The priority order I believe is 1,2,3,4,5 - meaning session trumps all, followed by 2, (3 and 4 I forget which trumps what), and lastly if no search_path set in 1-4 the one at the server level wins.

> -----Original Message-----
> From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf
> Of Simon Greener
> Sent: Tuesday, October 4, 2022 4:57 PM
> To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
> Subject: Re: [postgis-users] No extension
>

> Search path is set. Because it is set in the database it is independent of the
> port, correct?
>
> Because I need to move forward, I've gone back to using port 5433 and all is
> working.
>
> Thanks to all for taking the time to help.
>
> Simon
>
>
>

[Simon Greener]

This is most helpful. Thanks. Turns out one app I'm working with is built on GeoTools. I wonder if it does anything with setting/using search paths?
Simon

⁣39 Cliff View Drive
Allens Rivulet Tas 7150
(W) 0418 396 391​