After a very frustrating afternoon with a colleague, we figured it out.
The reason it had always worked before this is because all my code was previously configured to work on a local Postgres instance. (i.e. on localhost).
My explanation here is based on what appears to have happened, I am not an expert on the inner workings on GRASS and my brain feels like a pretzel after this afternoon, so don't take this explanation as the absolute truth.
It seems that v.external -o input="PG:dbname=lenistore user=store_sa host=x.x.x.x port=5432" layer="store_sa.all_roads" out=network_to_clean --overwrite
does not store the postgres login credentials as part of the created layer, nor does it store the data locally. It is a one-time read of the data, and seems to store it as a layer in your GRASS session which serves as a kind of pointer to the data source.
That means that the second command, v.clean -c input=network_to_clean output="routing_network" tool=break,chdangl threshold=0.00025,0.0005 --overwrite
, has no concept of how to access the database, because it only sees the name of the layer in grass (network_to_clean
), and not the credentials to go actually get the data.
We have to run another couple of commands before running v.clean
:
db.connect driver=pg database=lenistore
db.login user=store_sa pass=<password> host=x.x.x.x port=5432 --overwrite
At first I was confused because I thought that db.login
would perform the action of logging into the database. No - it saves the login credentials to a config file used by GRASS to make future connections. So by running this command first, we seem to save the signature of the login credentials for future uses, similar to a .pgpass
file.
So, in short, the full process is:
db.connect driver=pg database=lenistore
db.login user=store_sa pass=<password> host=x.x.x.x port=5432 --overwrite
v.external -o input="PG:dbname=lenistore user=store_sa host=x.x.x.x port=5432" layer="togo.all_roads" out=network_to_clean --overwrite
v.clean -c input=network_to_clean output="routing_network" tool=break,chdangl threshold=0.00025,0.0005 --overwrite
... do whetver else at this point
You probably don't need to run the first two commands every time if this is being executed as a script in the same GRASS context, but it seems to not hurt my process (which is internal and not sensitive, so I don't care), so I'm just leaving it like that.
Best Answer
If you are working on your workstation it's more a matter of taste. Knowing how to use psql is useful for some situations like running sql scripts from files, pipe it with other tools, etc. It depends on your needs. My everyday work is done using pgAdmin and I only go down to the CLI when needed.
On the other hand psql is sometimes your only option when you are on a remote server that you can connect directly so having some knowledge is always fine. psql has some nice features like autocompletion and database objects listing that can be really time saving.