3

Getting the error message/popup stating:

Connection failed - Check settings and try again. 

Extended error information: 
ERROR: relation 'geometry_columns' does not exist. 

LINE|1: ...attnum as attnum FROM pg_class c, pg_namespace n, geometry_c... ^

when trying to connect to a PostgreSQL/PostGIS database remotely on QGis (Mac). The pg_hba.conf and postgresql.conf files on the server don't appear to be the issue here. Perhaps there's a way I can grant all privileges for these tables mentioned in the error, but there aren't any tables called 'geometry_columns', so I'm stumped so far.

Version of postgresql on remote server is:

SELECT version();
#=> PostgreSQL 9.1.24 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
  • 2
    Please provide full error messages as text, rather than pictures, within your question body, so that they are available to future searches. – PolyGeo Feb 3 '17 at 23:16
  • Added error message to image metadata – boulder_ruby Feb 3 '17 at 23:23
  • 4
    it's also helpful to mention postgis and qgis versions (to get postgis version, run the query SELECT PostGIS_full_version(); from psql or pgadmin). – Steven Kay Feb 3 '17 at 23:24
  • 2
    I think it would be far better to have the error message text in the question body so that it can be seen by anyone opening it on any device. – PolyGeo Feb 3 '17 at 23:31
  • 1
    another question; is your postgis extension installed in the same schema as the tables your trying to import into qgis? – Steven Kay Feb 7 '17 at 20:20
2
+50

I think the answer from Alex Leith is the most likely cause of this error; the geometry_columns table/view gets created as soon as postGIS extension is created.

But it could still happen even if postGIS extension has been added, I think there are two aspects to this..


Table in different schema to postGIS

I found this could happen if the table I was importing was in a different schema to that where the postGIS extension was loaded. This meant that QGIS couldn't "see" the postGIS functions, data types and tables/views, as it assumed it was in the same schema as the database.

Got a very similar warning:-

enter image description here

To reproduce this, I took an existing local postgis-enabled database (with tables in 'public' schema), dropped the postGIS extension, and recreated it in a new schema, 'test')

CREATE EXTENSION postgis
SCHEMA test
VERSION "2.1.2";

Now, attempting to add a table from the 'public' schema will result in that error.

There's a setting called search_path which can be set at database level, which will allow qgis to see schemas other than the one the database is in. If you have access, something like

SHOW search_path;

will typically return something like "$user",public or "$user",postgis. If that list doesn't include the schema postGIS is in, that might be the problem.

Be careful tweaking that setting, I found I got QGIS crashing on opening projects with one database connection I'd altered that way. Luckily, you can remove connections without needing to open a project using the 'elephant' icon. I'll raise a ticket if I ever manage to reproduce this problem.


QGIS expects postGIS to be installed in Public schema

It also looks like qgis expects postgis to be installed in the public schema. If I created a database whose tables and postGIS were both in a schema other than public, I got the message

Database connection was successful, but the accessible tables could not be determined.
  • OK, you've figured out what the problem is, but I'm still unable to fix it. As a rails developer with a business administration degree I'm kind of an outsider to the intricacies of database configurations, but am learning at a meager albeit steady pace. Want to award the points before they expire, but I hope to edit this solution with you to a point that it gets me and others out of the hole in addition to knowing how they got there in the first place. Setting up postgis with rails was a blur for me, I moved on as soon as I got it working.. – boulder_ruby Feb 12 '17 at 0:54
0

Sounds to me like there's either a permissions issue (the user you're connecting as can't read the geometry_columns table) or you aren't connecting to a database that has got the PostGIS extensions installed.

Have you enabled PostGIS extensions?

CREATE EXTENSION postgis;

Are you connecting to the right database?

Does your user have the correct permissions (can you connect the the DB using PGAdmin with the same credentials, and if so, can you see the geometry_columns table)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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