[GRASS-dev] db.login with postgresql and non standard port

GRASS GIS GRASS GIS developer
1

Hi devs,

I have a problem using db backend different to SQLite.
I have a postgresql server running on port 5433, I set up the
connection like this

db.connect driver=pg database=grassdata
db.login user=lucadelu port=5433 host=localhost

but if I try to create new vector with v.random for example I get this error

v.random -z output=random npoints=50 column=zz
D1/1: G_set_program_name(): v.random
D1/1: Vect_open_new(): is_tmp = 0
D1/1: Vect_open_new(): name = random with_z = 1 is_tmp = 0
D1/1: Vect_set_thresh(): thresh = 0.000000
D1/1: G_find_vector2(): name=random mapset=osm_compare
D1/1: V1_open_new_nat(): name = random with_z = 1 is_tmp = 0
D1/1: dig__write_head()
D1/1: write coor size (0) to head
D1/1: dig_spidx_init(), 2 dims
D1/1: Vect_open_sidx(): name = random mapset= osm_compare mode = new
D1/1: Memory based spatial index
D1/1: Vect_default_field_info(): map = random field = 1
D1/1: db_d_init_error(): PostgreSQL
DBMI-PostgreSQL errore del driver:
Connection failed.
connessione al server fallita: File o directory non esistente
        Verifica che il server locale sia in funzione e che
        accetti connessioni sul socket di dominio Unix
"/var/run/postgresql/.s.PGSQL.5432"

DBMI-PostgreSQL errore del driver:
Connection failed.
connessione al server fallita: File o directory non esistente
        Verifica che il server locale sia in funzione e che
        accetti connessioni sul socket di dominio Unix
"/var/run/postgresql/.s.PGSQL.5432"

ATTENZIONE: Impossibile aprire il database <grassdata> dal driver <pg>
ERRORE: Impossibile aprire il database <grassdata> col driver <pg>
D1/1: Vect_close(): name = random, mapset = osm_compare, format = 0,
level = 1, is_tmp = 0
D1/1: close history file
D1/1: V1_close_nat(): name = random mapset= osm_compare
D1/1: get coor info:
/home/lucadelu/grassdata/utm32N/osm_compare/vector/random/coor
D1/1: Vect_coor_info(): Info->size = 0, Info->mtime = 1457452927
D1/1: dig__write_head()
D1/1: write coor size (0) to head
D1/1: Vect_write_dblinks(): map = random, mapset = osm_compare
D1/1: Dblinks written
D1/1: Vect__open_old(): name = random, mapset = osm_compare, layer =
NULL, update = 0, head_only = 1, is_tmp = 0
D1/1: Vect_set_thresh(): thresh = 0.000000
D1/1: dig_spidx_init(), 2 dims
D1/1: G_find_vector2(): name=random mapset=osm_compare
D1/1: Map: name = random, mapset = osm_compare, temporary = 0
D1/1: Vector format: 0 (native)
D1/1: Vect_set_thresh(): thresh = 0.000000
D1/1: Vect__read_head(): vector = random@osm_compare
D1/1: Vect_set_thresh(): thresh = 0.000000
D1/1: Level request = 1
D1/1: Vect__open_old(): vector opened on level 1
D1/1: Vect_read_dblinks(): map = random, mapset = osm_compare
D1/1: Dblinks read
D1/1: dbln file: /home/lucadelu/grassdata/utm32N/osm_compare/vector/random/dbln
D1/1: Vect_close(): name = random, mapset = osm_compare, format = 0,
level = 1, is_tmp = 0
D1/1: close history file
D1/1: G_set_program_name(): g.gisenv

Is this a bug or I'm doing something wrong?

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

2

Hi,

2016-03-08 17:08 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

db.connect driver=pg database=grassdata
db.login user=lucadelu port=5433 host=localhost

what says

db.login -p

? Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa

3

On 08/03/16 17:08, Luca Delucchi wrote:

Hi devs,

I have a problem using db backend different to SQLite.
I have a postgresql server running on port 5433, I set up the
connection like this

db.connect driver=pg database=grassdata
db.login user=lucadelu port=5433 host=localhost

I would expect the port parameter to be set in db.connect, not in db.login. Have you tried that ? See "PostgreSQL with different port" in the db.connect manual.

Moritz

4

Hi,

2016-03-09 17:14 GMT+01:00 Moritz Lennert <mlennert@club.worldonline.be>:

db.connect driver=pg database=grassdata
db.login user=lucadelu port=5433 host=localhost

I would expect the port parameter to be set in db.connect, not in db.login.

connection details (user/password/port/hostname) are part of db.login,
not db.connect (>= 7.1.)

Have you tried that ? See "PostgreSQL with different port" in the db.connect
manual.

Not valid for 7.1. Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa

5

On 09/03/16 19:54, Martin Landa wrote:

Hi,

2016-03-09 17:14 GMT+01:00 Moritz Lennert <mlennert@club.worldonline.be>:

db.connect driver=pg database=grassdata
db.login user=lucadelu port=5433 host=localhost

I would expect the port parameter to be set in db.connect, not in db.login.

connection details (user/password/port/hostname) are part of db.login,
not db.connect (>= 7.1.)

Have you tried that ? See "PostgreSQL with different port" in the db.connect
manual.

Not valid for 7.1. Martin

Oh, yes, I see. Thanks for the clarification. I must have missed that change. What was the reasoning behind this change ? Is this in the idea that one might want to move a database to another host and/or port, but not change the mapset and vector map connection settings ? Or something else ?

Moritz

6

On 8 March 2016 at 17:13, Martin Landa <landa.martin@gmail.com> wrote:

Hi,

Hi,

2016-03-08 17:08 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

db.connect driver=pg database=grassdata
db.login user=lucadelu port=5433 host=localhost

what says

db.login -p

db.login -p
sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||
pg|host=localhost,dbname=pygrassdb_doctest|lucadelu|||
pg|grassdata|lucadelu|||

why duplicate rows?

? Ma

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

7

Hi,

2016-03-10 9:01 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

db.login -p
sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||

well, the lines are duplicated after substitution. I don't know, your
run command which defined db connection ones without and ones with
substitution. Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa

8

Hi,

2016-03-10 9:01 GMT+01:00 Moritz Lennert <mlennert@club.worldonline.be>:

Oh, yes, I see. Thanks for the clarification. I must have missed that
change. What was the reasoning behind this change ? Is this in the idea that
one might want to move a database to another host and/or port, but not
change the mapset and vector map connection settings ? Or something else ?

I would say, you are right. db.login was prepared, the code was just
commented out and dbmi library needed to implement this functionality.
Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa

9

On 10 March 2016 at 09:10, Martin Landa <landa.martin@gmail.com> wrote:

Hi,

Hi,

2016-03-10 9:01 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

db.login -p
sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||

well, the lines are duplicated after substitution. I don't know, your
run command which defined db connection ones without and ones with
substitution. Ma

No, I donit think so. I just run

db.connect driver=pg schema=public database=grassdata
db.connect -p

driver: pg
database: grassdata
schema: public
group:

db.login driver=pg database=grassdata user=lucadelu port=5433 host=localhost --o
db.login -p

sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||
pg|host=localhost,dbname=pygrassdb_doctest|lucadelu|||
pg|grassdata|lucadelu|||

db.tables

DBMI-PostgreSQL driver error:
Connection failed.
could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

DBMI-PostgreSQL driver error:
Connection failed.
could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

ERROR: Unable to open database <grassdata>

So the port it is not stored, and also in db.login -p there is
something strange with

pg|host=localhost,dbname=pygrassdb_doctest|lucadelu|||

where dbname=pygrassdb_doctest should be dbname=grassdata

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

10

On 10/03/16 09:42, Luca Delucchi wrote:

On 10 March 2016 at 09:10, Martin Landa <landa.martin@gmail.com> wrote:

Hi,

Hi,

2016-03-10 9:01 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

db.login -p
sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||

well, the lines are duplicated after substitution. I don't know, your
run command which defined db connection ones without and ones with
substitution. Ma

No, I donit think so. I just run

db.connect driver=pg schema=public database=grassdata
db.connect -p

driver: pg
database: grassdata
schema: public
group:

db.login driver=pg database=grassdata user=lucadelu port=5433 host=localhost --o
db.login -p

sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||
pg|host=localhost,dbname=pygrassdb_doctest|lucadelu|||
pg|grassdata|lucadelu|||

db.tables

DBMI-PostgreSQL driver error:
Connection failed.
could not connect to server: No such file or directory
         Is the server running locally and accepting
         connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

DBMI-PostgreSQL driver error:
Connection failed.
could not connect to server: No such file or directory
         Is the server running locally and accepting
         connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

ERROR: Unable to open database <grassdata>

So the port it is not stored, and also in db.login -p there is
something strange with

pg|host=localhost,dbname=pygrassdb_doctest|lucadelu|||

where dbname=pygrassdb_doctest should be dbname=grassdata

Could this be "left-overs" from pre-grass71 ? How about erasing (or renaming $HOME/.grass7/dblogin) and trying again ?

Moritz

11

Hi,

2016-03-10 9:42 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

db.login -p
sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||

No, I donit think so. I just run

these two lines are apparently some leftover from other commands you
entered in the past. There is no relation to PG. Ma

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa

12

On 10 March 2016 at 09:59, Martin Landa <landa.martin@gmail.com> wrote:

Hi,

2016-03-10 9:42 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

db.login -p
sqlite|$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db|lucadelu|||
sqlite|/grassdata,nc_spm_08,lucadelu,sqlite.db|lucadelu|||

No, I donit think so. I just run

these two lines are apparently some leftover from other commands you
entered in the past. There is no relation to PG. Ma

ok, removed the file as proposed by Moritz, set again the login

db.login driver=pg database=grassdata user=lucadelu port=5433 host=localhost
db.login -p

pg|grassdata|lucadelu|||

but the file is correct

cat /home/lucadelu/.grass7/dblogin

pg|grassdata|lucadelu||localhost|5433

Probably something is wrong in the db library because it seems to not
consider the port (and probably also the host)

--
ciao
Luca

http://gis.cri.fmach.it/delucchi/
www.lucadelu.org

13

Hi,

2016-03-10 10:15 GMT+01:00 Luca Delucchi <lucadeluge@gmail.com>:

Probably something is wrong in the db library because it seems to not
consider the port (and probably also the host)

right, please fill a ticket, Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa