how to fix Cannot run program "psql" error?

bk211

https://community.dataiku.com/t5/Using-Dataiku-DSS/Job-fail-when-run-a-postgresql-code-recipe/m-p/1203

This thread above discuzz the same issue that i've encountered,

here's a bit of info about my current config,

dataiku is running in a docker container,

postgres is running in a separate docker container too,

dataiku does connect to the right database but im having this error when I try to run SQL script.

If I uncheck the "Use psql" in advanced settings i will get this error :

Job failed: ERROR: could not open extension control file "/usr/share/postgresql/13/extension/postgis.control": No such file or directory

Since my past data were running on a different machine but with this option checked on,

is there way to indicate to dataiku the path of psql?

Sergey

Hi @bk211

There are several issues with your setup. When you check psql on the Advanced tab, it fails sooner as we first check its presence, and indeed psql is not available on the DSS image by default. When you uncheck it, your SQL script just fails later on the destination Postgres instance because of missing binaries there. So here are items you need to fix:

1) Missing postgis.control binary on the destination PostgreSQL docker container.

ERROR: could not open extension control file "/usr/share/postgresql/13/extension/postgis.control": No such file or directory

To get this fixed you will need to have PostGIS installed (or build a new image from the base one with PostGIS installed) on the destination PostgreSQL container.

2) Missing psql binary on the DSS container:

Cannot run program "psql": error=2, No such file or directory, caused by: IOException: error=2, No such file or directory

You will need to install psql or rebuild the DSS image with installing it during docker runtime so it will be available via PATH.

Here my DSS image with psql installed:

git:(master) ✗ docker exec -it dss_psql /bin/bash
[dataiku@f9a00b233ef1 ~]$ psql -V
psql (PostgreSQL) 13.3

And here are two recipe execution snippets (with and without psql) executed on the separate PostgreSQL container:

# WITH psql: 
[22:37:15] [INFO] [dku.flow.sql] - SQL runner running
[22:37:15] [INFO] [dku.flow.sql] - Executing with psql: psql -h postgres -U dataiku -f /home/dataiku/dss/jobs/DKU_TUTORIAL_AUTOMATION/Build_PSQL_test_2021-07-02T22-37-14.441/compute_PSQL_test_NP/sql-recipe/script.sql -p 5432 dataiku --set ON_ERROR_STOP=1
[22:37:15] [INFO] [dku.utils]  - CREATE TABLE

# NO psql (JDBC driver used):
[22:42:22] [INFO] [dku.flow.sql] - SQL runner running
[22:42:22] [INFO] [dku.connections.sql.provider] - Connecting to jdbc:postgresql://postgres:5432/dataiku with props: {"user":"dataiku","password":"***"} conn=postgres-YgVHs56
[22:42:22] [DEBUG] [dku.connections.sql.driver] - Driver version 9.0
[22:42:22] [INFO] [dku.connections.sql.provider] - Driver: PostgreSQL Native Driver (JDBC 4.0) PostgreSQL 9.0 JDBC4 (build 801) (9.0)

3) You don't need to upload any PostgreSQL drivers to DSS as indeed we bundle one by default. The above test confirms that.

bk211

Is it an issue with jdbc installation?

the documentation says that postgresql jdbc is included, but on my case, it fail to found the binary

so I have downloaded an postgresql jdbc on its website, and placed it to the dataiku installation dir/lib/jdbc

but the issue remains. any step that I have missed?

bk211

So i have tried to install postgresql, my VM provide it but its only up to 9.2,

therefore, I still have some 11/12/13 postgres function that i can't use, so I need to install the a newer one

Then I have installed the 13.3 version at this folder

/usr/pgsql-13/bin

But dataiku still failling with

can't not run program "psql"

[root@dataiku dataiku]# ls
Miniconda3.sh dataiku-dss-8.0.2 dss miniconda3 run.sh

Where i can add this to dataiku's path?