Quantum GIS

Question:

I've installed the last version of PostgreSQL (8.0.2) and when I try to use it by shell with "psql testdb" everything works... so I'm sure that installation is correct... when I try to use SPIT plugin of QGIS 0.6 to import shape file, I always get an error message: Connection failed - check settings and try again". I'm new user of PostgreSQL, could someone tell me how to configure each parameter of the server connection, please?

Answer:

Did you install PostGIS? It's a GIS extension to PostgreSQL and qgis is only able to connect to PostGIS databases. There is a good documentation at http://postgis.refractions.net.

If PostGIS is install then the other most likely problem is that you have not enabled tcp/ip connections to your database (which is required for spit and postgis layer connections).

Find your postgresql.conf (either in /etc/postgresql/postgresql.conf or in /var/lib/pgsql/data/postgresql usually) and make sure the following line is uncommented and set to true.

tcpip_socket = true

Then restart postgresql e.g.:

/etc/init.d/postgresql restart

and try again!

Configuring the connection from SPIT and PG Layer Dialog

If your pg is installed on same machine as qgis, use localhost for hostname.

After first installing postgresql, I always create a new user:

 
su - root
su - postgres
createuser -a -d -P -E
bob
foopassword
exit
exit

Make the user name the same as your normal linux log in name - then you can use ident authentication on the localhost and save yourself a lot of typing, I usually choose yes when asked if this user can create new users. I never use the postgres account - I reserve that for system stuff like scripts that start & stop pg.

Testing:

you can test from the cli either from a different host or from the same host but forcing a tcp/ip connection:

 
aps02ts@ubuntu:~$ psql -l -h localhost
Password:
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+----------
 qgis      | aps02ts  | LATIN1
 template0 | postgres | LATIN1
 template1 | postgres | LATIN1
(3 rows)

Note you will be prompted for your password and it will default the username to your current logged in account.

One last thing, you may get 'host not specified in pg_hba.conf' errors. This is your host based authentication file. Open this file and add a line like this for each host you plan to connect over tcp/ip from:

 
host    all         all         127.0.0.1         255.255.255.255   md5

md5 option is needed if you are using encrypted passwords (which I recommend). I usually am a bit more explicit with my user host defs. e.g.:

 
host    all         mj99user    114.205.46.46      255.255.255.255   md5 #Tim 
Sutton

That says user aps02ts can connect from this host with this mask using md5 encryption.

As far as I know postgres daemon needs to be restarted whenever updating the pg_hba.conf file.