SQL recipe psql error

[Meirkhan]

While executing SQL recipe with default settings (notably checked case Use psql For PostgreSQL recipes, use psql binary instead of the JDBC driver) I received an error  

psql: error: connection to server at "blabla" (1.2.3.4), port 5432 failed: SSL error: certificate verify failed
[16:49:44] [INFO] [dku.utils]  - connection to server at "blabla" (1.2.3.4), port 5432 failed: FATAL:  no pg_hba.conf entry for host "195.154.x.x", user "postgres", database "postgres", SSL off

which at first glance might indicate that I have a bad connection for my DB. However, connection is tested and working.

Here is the list of thing I tried:

1. - if case Use psql is unchecked I don't have  any error but it takes looong time to execute the recipe and mostly it fails at some point.
2. - The same code works fine if launched via SQL notebook
3. - If psql command launched manually on dataiku hosting server via ssh, it works fine (I guess because it uses psql binary and not JDBC driver)  Ex: psql -h -U postgres -f path/to/sql/file.sql -p 5432 postgres
4. - I get the exact same error as I mentioned above if I intentionally set false password for PGPASSWORD on terminal of the dataiku hosting server

Why there is such a difference between JDBC driver and psql binary? And more importantly how do I solve it?

Thanks,

Meirkhan

---

Operating system used: Linux

[AlexT]

Hi,

There shouldn't be any noticeable performance difference between psql and jdbc driver. 

In a SQL notebook the query is limited by default to 1000 rows hence it may be much faster as it won't execute the full query. 

If you want to use psql you will need to modify the pg_hba.conf file to include an entry for :

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

"195.154.x.x" 

e.g
host all all 195.154.x.x/32 trust

Then run 
SELECT pg_reload_conf();

Try your connection again but again you shouldn't see a performance difference between issuing a command via PSQL vs JDBC as long as the network path is the same.

In your case, if you run the query directly on the Postgres server itself locally and compared this from DSS issuing then the cause of the slowness is either network or intermittent performance 

[Meirkhan]

I know that it does not make any sense but, in fact it is not just slow, it never finishes the job.

I think my configs in pg_hba.conf are ok since I manage to launch the commands from terminal of server on which dataiku is hosted.