I started grass7.7.svn (r73623) and set db.connect to use driver=pg and
database='willamette_river_hg'. Then I copied the vector files (g.copy
vect=current_file,current_file_pg) per the migration instructions on the
databaseintro manual page.
Trying to run db.copy to add attribute tables to the database (except for
one that is already present) using a script with commands like this:
I started grass7.7.svn (r73623) and set db.connect to use driver=pg and
database=‘willamette_river_hg’. Then I copied the vector files (g.copy
vect=current_file,current_file_pg) per the migration instructions on the
databaseintro manual page.
Trying to run db.copy to add attribute tables to the database (except for
one that is already present) using a script with commands like this:
WARNING: Unable to open database
<$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db>
by driver
dbmi: Protocol error
Yet, I can view the attribute table for the vector map within grass.
Should I run db.connect to the sqlite database at the top of the script?
db.copy does not need db.connect. The reason for the error is that the variable $HOME is not expanded because it is within single quotes. Use double quotes instead of single quotes for the from_database argument and it should work.
db.copy does not need db.connect. The reason for the error is that the
variable $HOME is not expanded because it is within single quotes. Use
double quotes instead of single quotes for the from_database argument and
it should work.
Markus,
I was thinking of working within grass rather than at the bash prompt.
You're right: I know the difference between double and single quotes but
blew it on this instance.
I was thinking of working within grass rather than at the bash prompt.
You're right: I know the difference between double and single quotes but
blew it on this instance.
I'm still missing something.
From the grass shell the command, db.connect driver=pg
database=willamette_river_hg, apparently completes because there's no
warning or error displayed.
However, the db.copy command still cannot find the database:
I was thinking of working within grass rather than at the bash prompt.
You’re right: I know the difference between double and single quotes but
blew it on this instance.
I’m still missing something.
From the grass shell the command, db.connect driver=pg
database=willamette_river_hg, apparently completes because there’s no
warning or error displayed.
db.connect only sets the default database, it does not test if it exists or is accessible. For PG, you need to use db.login first in order to actually use the PG database.
HTH,
Markus M
However, the db.copy command still cannot find the database:
db.connect only sets the default database, it does not test if it exists
or is accessible. For PG, you need to use db.login first in order to
actually use the PG database.
Markus M,
Sigh. Neither without or with a username it still fails:
db.connect only sets the default database, it does not test if it exists
or is accessible. For PG, you need to use db.login first in order to
actually use the PG database.
Markus M,
Sigh. Neither without or with a username it still fails:
try to set host and port with db.login. Username and password are not required if a ~/.pgpass file exists. See the manual of db.login, following the link to PostgreSQL that describes the GRASS PG driver.
Markus M
WARNING: DB connection <pg/willamette_river_hg> already exists and will be
overwritten
db.copy from_driver=sqlite from_database=“$HOME/data/grassdata/willamette_basin/mercury/sqlite/sqlite.db” from_table=all_species to_driver=pg to_database=“willamette_river_hg” to_table=all_species.pg
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database “willamette_river_hg” does not exist
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database “willamette_river_hg” does not exist
WARNING: Unable to open database <willamette_river_hg> by driver
no database is open
no database is open
WARNING: Copy table failed
try to set host and port with db.login. Username and password are not
required if a ~/.pgpass file exists. See the manual of db.login, following
the link to PostgreSQL that describes the GRASS PG driver.
Markus M,
Here ~/.pgpass contains host, port, *, and username. Anyway, I changed
that line in the script:
ERROR: DB connection <pg/willamette_river_hg> already exists. Re-run
'db.login' with '--overwrite' flag to overwrite existing settings.
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "willamette_river_hg" does not exist
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "willamette_river_hg" does not exist
I think that I've now tried every variation of options with db.login and
db.connect (which works at the grass shell prompt) with the most current
releases of 7.7.svn and I'm stymied.
I was thinking of working within grass rather than at the bash prompt.
You're right: I know the difference between double and single quotes but
blew it on this instance.
I'm still missing something.
From the grass shell the command, db.connect driver=pg
database=willamette_river_hg, apparently completes because there's no
warning or error displayed.
However, the db.copy command still cannot find the database:
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "willamette_river_hg" does not exist
WARNING: Unable to open database <willamette_river_hg> by driver <pg>
no database is open
WARNING: Copy table failed
I access the database with psql from the bash prompt with no issues:
$ psql willamette-river-hg
psql (10.3)
Type "help" for help.
willamette-river-hg=#
You database seems to be called 'willamette-river-hg', i.e. all hyphens, whereas you are trying to db.copy to 'willamette_river_hg', i.e. one hyphen, one underscore.
Rich Shepard <rshepard@appl-ecosys.com> hat am 31. Oktober 2018 um 14:40 geschrieben:
On Wed, 31 Oct 2018, Markus Metz wrote:
> try to set host and port with db.login. Username and password are not
> required if a ~/.pgpass file exists. See the manual of db.login, following
> the link to PostgreSQL that describes the GRASS PG driver.
Markus M,
Here ~/.pgpass contains host, port, *, and username. Anyway, I changed
that line in the script:
> $HOME/documents/white-papers/geochemistry/willamette-river-mercury/scripts/grass-to-postgres.sh
ERROR: DB connection <pg/willamette_river_hg> already exists. Re-run
'db.login' with '--overwrite' flag to overwrite existing settings.
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "willamette_river_hg" does not exist
DBMI-PostgreSQL driver error:
Connection failed.
FATAL: database "willamette_river_hg" does not exist
I think that I've now tried every variation of options with db.login and
db.connect (which works at the grass shell prompt) with the most current
releases of 7.7.svn and I'm stymied.
You database seems to be called 'willamette-river-hg', i.e. all hyphens,
whereas you are trying to db.copy to 'willamette_river_hg', i.e. one
hyphen, one underscore.
Moritz,
Thank you. I use hyphens in multi-word file names while applications such
as grass and R use underscores. When I get them mixed up I have a tendency
to not see the discrepancy which is obvious to others.
Now I need to fix an issue with db.connect. Perhaps others will see what
I'm overlooking.
WARNING: DB connection <pg/willamette-river-hg> already exists and will be
overwritten
DBMI-PostgreSQL driver error:
Connection failed.
could not connect to server: Connection refused
Is the server running on host "salmo" (192.168.55.1) and accepting
TCP/IP connections on port 5432?
Contents of ~/.pgpass:
localhost:5432:*:rshepard:
Replacing localhost with salmo makes no difference.
The database is on this host, a server/workstation desktop. The postgres
shell has no problems connecting to any database (they're all owned by me).
For example:
$ psql willamette-river-hg
psql (10.3)
Type "help" for help.
willamette-river-hg=#
I've had no issues connecting directly to any postgres database here using
psql over the past couple of decades so why grass is having difficulties
finding this one really puzzles me.
The reason I ask is because you have to configure authentication and enable remote access to connect to the database from an application. For me I had to do that to use PG with GRASS.
psql connects via trusted connection because user postgres connects to the DB from local machine. Postgres connects via network connection usually.
I asked for pgadmin because with that tool you can test this behaviour. If you can not connect with pgadmin I would suppose that the authentication issue is what troubles you.
Regards.
Stefan
Rich Shepard <rshepard@appl-ecosys.com> hat am 31. Oktober 2018 um 15:59 geschrieben:
On Wed, 31 Oct 2018, st_kiefer@web.de wrote:
> is it possible to connect to the DB with PGAdmin?
Stefan,
I don't run pgadmin.
The database is on this host, a server/workstation desktop. The postgres
shell has no problems connecting to any database (they're all owned by me).
For example:
$ psql willamette-river-hg
psql (10.3)
Type "help" for help.
willamette-river-hg=#
I've had no issues connecting directly to any postgres database here using
psql over the past couple of decades so why grass is having difficulties
finding this one really puzzles me.
You database seems to be called 'willamette-river-hg', i.e. all hyphens,
whereas you are trying to db.copy to 'willamette_river_hg', i.e. one
hyphen, one underscore.
Moritz,
Thank you. I use hyphens in multi-word file names while applications such
as grass and R use underscores. When I get them mixed up I have a tendency
to not see the discrepancy which is obvious to others.
Now I need to fix an issue with db.connect. Perhaps others will see what
I'm overlooking.
WARNING: DB connection <pg/willamette-river-hg> already exists and will be
overwritten
DBMI-PostgreSQL driver error:
Connection failed.
could not connect to server: Connection refused
Is the server running on host "salmo" (192.168.55.1) and accepting
TCP/IP connections on port 5432?
This sounds like Stefan might be on the right path: are you sure your Postgresql server accepts connections from the network ? What is 'listen_addresses' set to in your postgresql.conf ? And is password authentication enabled in pg_hba.conf ?
This sounds like Stefan might be on the right path: are you sure your
Postgresql server accepts connections from the network ? What is
'listen_addresses' set to in your postgresql.conf ? And is password
authentication enabled in pg_hba.conf ?
Moritz,
/usr/share/postgresql-10.3/postgresql.conf has, as defaults. localhost for
the listen_addresses and 5432 for the port. I modified that file so
listen_addresses has both 'localhost' and 'salmo' (the localhost's host
name).
There is no password authentication; all methods are 'trust'.
This sounds like Stefan might be on the right path: are you sure your
Postgresql server accepts connections from the network ? What is
'listen_addresses' set to in your postgresql.conf ? And is password
authentication enabled in pg_hba.conf ?
Moritz,
/usr/share/postgresql-10.3/postgresql.conf has, as defaults. localhost for
the listen_addresses and 5432 for the port. I modified that file so
listen_addresses has both 'localhost' and 'salmo' (the localhost's host
name).
I've uncovered a postgres glitch: it's looking for libpq.so.5 in a
postgresql/10.2/ rather than in the 10.3/ directory. I'm looking for the the
wrong pointer now. Will fix that because it should fix the problem.
Trying to run db.copy to add attribute tables to the database (except for
one that is already present) using a script with commands like this:
Markus M./Moritz:
All fixed now. There was an issue with the postgres upgrade from 9.x to
10.x (a long time ago) that did not appear until I tried connecting to a
database with grass. Took the rest of yesterday to fix broken symlinks and
this morning I upgraded from 10.3 to 10.5 (the upgrade to 11.0 can wait a
bit). Running 'db.test test1' in the latest 7.7.svn release required me to
add another host line in pg_hba.conf to allow access from all hosts on the
office network. Now everything's working:
db.test test1
Using DB driver: pg
CREATE TABLE grass_test1 (i1 INTEGER, d1 DOUBLE PRECISION, c1 VARCHAR(20))
EXECUTE: OK
INSERT INTO grass_test1 VALUES ( 1, 123.456, 'abcd' )
EXECUTE: OK
INSERT INTO grass_test1 VALUES ( 2, null, 'xxx' )
EXECUTE: OK
SELECT * FROM grass_test1
EXECUTE: OK
RESULT: OK
SELECT c1 FROM grass_test1 WHERE d1 < 500 / 2 AND i1 <> 2 AND c1 LIKE '%bc%' EXECUTE: OK
RESULT: OK
INSERT INTO grass_test1 VALUES ( 3, 0.0, '_\''_' )
EXECUTE: OK
ALTER TABLE grass_test1 ADD COLUMN i2 INTEGER
EXECUTE: OK
UPDATE grass_test1 SET d1 = 18.6, i2 = 987 WHERE i1 = 2
EXECUTE: OK
SELECT * FROM grass_test1
EXECUTE: OK
2d1
< 3|0|_\\'_|
3a3
3|0|_\'_|
ERROR: RESULT: ******** ERROR ********
DROP TABLE grass_test1
EXECUTE: OK
Had I not started to migrate my grass project databases to postgres I
would not have exposed the broken links (which did not stop psql from
working at the bash prompt for some reason).