postgres question

[jg895512]

Howdy...

I've spent most of the day going through Provenance and I think it is going to meet our needs so I'd like to give it a try. I'm having the toughest time though with the artifact repo configuration. How can I connect to a remote Postgresql database? I've tried various connection strings (with and without username/password) and I've tried additional fields for username/password, but all with no luck, I get SQLAlchemy errors about bad username or database, but I know it is a valid connection and user (I was able to setup remote connections for my Celery install of Airflow and using Postgresql for that)...

Looking through the project code and examples here.. I'm just stumped. Any help/examples would be much appreciated.

Thanks!
Jeff

[bmabey]

Sorry the config issues got you stumped. The connection string for a postgres DB should be of the form:

postgres://PGUSER:PGPASSWORD@PGHOST:PORT/DB_NAME

For example:

postgres://foo:mypassword@db.foobar.com:5432/stuff

[jg895512]

Thanks!

That was actually one of the first things I tried. Weird.. When I did that, I got this error (notice how it says database does not exist, but using the field that is in the username field):

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: database "PGUSER" does not exist

It was confusing to me, because PGUSER is in the username field, not the database field/location (after the slash).

If I keep everything the same, but remove the ':PGPASSWORD' from the string I get:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied

So at that point, it seems to recognize that PGUSER is a username...

Very odd...

I hit this first in Python 3.7 and then tried another venv and hit it in Python 3.5 as well. I can get you other package versions from my venv if that would help. If you have any other ideas let me know!

Thanks!
Jeff

[jg895512]

So.. turns out not only did I need to create the user in postgres, I did actually need to create the db of the username. (excuse my naivety as I am not usually working with postgres). Anyway, I did this as postgres user on my db server

createdb PGUSER

This was per guidance here: https://postgresapp.com/documentation/troubleshooting.html

(search for) psql: FATAL: database “USERNAME” does not exist

Once I did that, things almost worked, but then I hit a postgres version issue regarding type JSONB. I upgraded my postgres (per this guide: https://blog.andreev.it/?p=4381) and then things ran successfully. With the above, I am not a postgres expert so I'm not sure why not only did I have to create a user PGUSER, but I also had to create a db named PGUSER.

Provenance/SQLAlchemy still puts the new artifacts tables in the DB_NAME (database named in bmabey comment above). I'm still not sure what the purpose of the database named PGUSER is for, but there we have it..

Hope my above notes are helpful to the next person.

Cheers,
Jeff