PostGIS Connection Issue – How to Fix Problem Connecting to Localhost PostGIS in QGIS

postgisqgis

When I try to connect to a small localhost PostGIS database using QGIS, I get the error message:

FATAL: no pg_hba.conf for host "fe80::4528:3236:418a9e7b%10" user "postgres", database "nyc", SSL off

The file pg_hba.conf contains

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

I can connect just fine using pgAdmin. I noticed that a few others have asked similar questions here, but I can´t find a solution using localhost. Any suggestions on what to try? QGIS 1.8.0 and PostgreSQL 9.2/PostGIS 2 on Win Vista 64 bit.

PostGIS Connection

Best Answer

If the database runs on localhost, you should specify localhost in the Host field which seems to be called "Dator" in your screenshot.

Related Solutions

QGIS PostgreSQL – Connecting to PostgreSQL with QGIS

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.

[GIS] Unable to connect Postgis from QGIS 1.8.0

I am not sure if it matters, but for me, Postgis 1.5 and Postgres 9.0 (x86) are talking to QGIS 1.8.0 without problems.

QGIS is still 32-bit, even on Windows 7 64-bit machines. Give the x86 version a try, and the old postgis version.

Best Answer

Related Solutions

QGIS PostgreSQL – Connecting to PostgreSQL with QGIS

[GIS] Unable to connect Postgis from QGIS 1.8.0

Related Question