Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Oddly, it should be simple, but I can't connect to a local PostGIS database. My installation is with Mac OS X (10.8), Postgres.app (in doc it's said to have PostGIS included) and Quantum GIS 1.8.

When I want to add a connection to Postgres, I'm specifying name, host (localhost), port (5432), database name, user name and password (service field left empty).

I can connect to a database with Navicat, so database server seems to run correctly. With the exact information in QGIS used in Navicat, it can't connect... why?

Thank you!

share|improve this question
    
can you post postgresql log it might be in /Library/PostgreSQL/8.4/data/pg_log if your postgresql version is 8.4 –  rashad Oct 31 '12 at 5:08
4  
Can you post the error message? –  R.K. Oct 31 '12 at 5:19
1  
have you edited the pg_hba.conf file to give local users access to postgres postgresqlformac.com/support/howto_edit_postgresql_confi.html –  Mapperz Oct 31 '12 at 13:40
    
Good example is pg_hba.conf serverfault.com/questions/224646/… –  Mapperz Oct 31 '12 at 13:43
1  
Ok, first, I can't find log data (no pg_log, only got pg_clog and pg_xlog), even in Console.app, I have a section called PostgreSQL but no entry in it. The error message, from QGIS "Create new PostGIS connection" dialog box (translated) is "Connection failed - Setup connection and retry" or something like that. When I save the connection ignoring error, and click connect in "Add PostGIS Layer" I have "Unable to open the service Postgres / PostGIS". –  elbaid Nov 1 '12 at 13:39

2 Answers 2

You should verify that you can connect using psql. Try psql -U username -h localhost dbname. It should prompt for a password then connect. Run SELECT postgis_version(); to verify that PostGIS is active in the database.

If you can connect but SELECT postgis_version() reports an error, PostGIS isn't installed in the database:

ERROR:  function postgis_version() does not exist
LINE 1: SELECT postgis_version();

Other solution:

SELECT * FROM Postgis_lib_version();

If you want also to see the version of Postgis and the version number of the libraries GEOS and Proj4 just change “lib” to “Full”.

If you get the above error, then presuming you're running PostGIS 2.0 on PostgreSQL 9.1 or above, connect as user postgres and run CREATE EXTENSION postgis;. Eg:

psql -U postgres -h localhost dbname -c 'CREATE EXTENSION postgis;'

You might also need to install some of the extras, like the PostGIS topology support:

CREATE EXTENSION postgis_topology;

or the legacy support script, which isn't packaged as an extension and must be sourced:

psql -U postgres -h localhost dbname -f /path/to/postgis-2.0/legacy.sql

See the PostGIS documentation.

If you can't connect to the DB, it's probably a pg_hba.conf issue, failure to create the DB or user, etc. Hard to say without error messages and log contents.

share|improve this answer
up vote 2 down vote accepted

Ok, in fact, the problem was PostGIS not activated in Postgres.app out of the box. And when we want to CREATE EXTENSION postgis, we have this error:

ERROR: could not load library "/Applications/Postgres.app/Contents/MacOS/lib/rtpostgis-2.0.so": dlopen(/Applications/Postgres.app/Contents/MacOS/lib/rtpostgis-2.0.so, 10): Library not loaded: /usr/local/lib/libjpeg.8.dylib Referenced from: /Applications/Postgres.app/Contents/MacOS/lib/libgdal.dylib Reason: image not found

It's because the file /usr/local/lib/libjpeg.8.dylib is not installed on Mac OS X 10.8 by default. We can install it with MacBrew using brew install libjpeg.

When it's done, we can successfully use the CREATE EXTENSION command, as the previous answer suggests:

psql -U postgres -h localhost dbname -c 'CREATE EXTENSION postgis;'

Note: with Postgres.app, the user postgres doesn't exist by default, you can use any user created with proper privileges, so replace it in previous command.

Et voilà! Quantum GIS can connect to PostgreSQL server.

Source: https://github.com/mattt/PostgresApp/issues/43

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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