2

I have two issues am I am wondering if they are related.

1: I can't get plugins to enable in pgAdmin III I have two PostgreSQL 9.5 instances. In one, the pgAdmin III plugin "PostGIS Shapefile DBF Loader 2.2" works when I highlight my database. In the other instance that plugin menu choice is greyed out. I've been comparing the two instances and I can't figure out what is different. In both cases I am connected to Postgres as a user who is not the owner of the database. PostGIS is installed in both. For the plugin, what are the requirements for it to be active (not greyed out)?

Additional info 3/28/2017: Note: my PostgreSQL is on Linux and my pgAdmin III is on Windows 7. I can successfully run the shapefile loader shp2pgsql-gui.exe found in Program Files> PostgreSQL > 9.5 > bin > postgisgui. I can verify that the shapefile has loaded well by viewing it in QGIS. The PostGIS Shapefile DBF Loader 2.2 and the PSQL Console are both greyed out when I select the database. I am connecting as afreeman which is a superuser. I also tried connecting as enterprisedb which is a superuser and owner of the public schema. My "PG bin path" as seen in Options/Browser/Binary paths is C:/Program Files/PostgreSQL/9.5/bin

2: I can't see a listing of geospatial layers in QGIS My QGIS will connect to one PostGIS instance but not list layers in any schemas. I was able to load shapefiles using the QGIS DBManager into schemas afreeman or public and I can drag those layers into QGIS map and they draw OK. However, choose Layer menu, Add Layer, Add PostGIS layer and connecting to the PostGIS database doesn't list anything. I can do this successfully with a different PostGIS instance. Same version (9.5). One difference though as that in the successful db the public schema is owned by postgres. In the unsuccessful one the public schema is owned by enterprisedb.

Does it matter?

Why would a test by successful but no layers appear?

Additional info 3/28/2017: show search_path; gives me: "$user", public, topology

afreeman is a superuser. I'm running PostGIS 2.1 However, I've also tried connecting as enterprisedb which is the owner of the public schema, same issue with not seeing layers. When I connect as enterprisedb or afreeman and test the connection that is successful. Just no layers in the list. Ran the SQL offered below but no effect. PostGIS is installed in the public schema.

Improve this question

2 Answers 2

Reset to default
2

In your pgAdmin, make sure the binary folder is pointing in the bin folder where your bin/postgisgui is installed.

This is the most common issue why it doesn't appear. Could also be a permission issue in folder postgisgui

I described this issue a while back in this article.

http://www.postgresonline.com/journal/archives/145-PgAdmin-III-Plug-in-Registration-PostGIS-Shapefile-and-DBF-Loader.html

If you got pgAdmin III with PostgreSQL, then the path for PG Bin should be set to.

C:\Program Files\PostgreSQL\9.5\bin

Regarding your other issue with QGIS. There are a number of causes for this

1) PostGIS is not installed in a schema that is in your database search_path, so QGIS doesn't think PostGIS is installed. Though I would expect DbManager to not work either if that were the issue.

2) The account you are using from QGIS doesn't have USAGE rights to public schema or doesn't have read rights to the geometry_columns view.

The CREATE EXTENSION postgis; command sets the GRANT permissions of geometry_columns to

GRANT SELECT ON TABLE geometry_columns TO public;

But before PostGIS 2.3.2, the upgrade wiped these permissions out.

That might explain why Add Layers shows you nothing but DbManager shows the tables, since I don't think DbManager relies as much on geometry_columns as Add Layers does.

To rule out permission issues you can log into QGIS as a super user like postgres and see if you see layers. If you do then it's a permission problem you can fix with something like:

GRANT USAGE ON SCHEMA public TO PUBLIC;
GRANT ALL ON ALL TABLES IN SCHEMA public TO afreeman;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT ALL ON TABLES TO afreeman;
Improve this answer
1
  • I think it would be good to edit the question to include this information, you can add it under a heading of Edit in bold perhaps to indicate that you are adding it after some further research.
    – Martin Hügi
    Commented Mar 28, 2017 at 18:09
0

The shapefile loader can be found in Program Files> PostgreSQL > 9.5 > bin > postgisgui. Its called shp2pgsql-gui.exe

(The 9.5 folder will of course be different for different versions)

This doesn't answer the question of why, but it should help you with a work around.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.