how to fix Cannot run program "psql" error?

bk211
bk211 Registered Posts: 12 ✭✭✭✭
edited July 16 in Setup & Configuration

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?

Best Answer

  • Sergey
    Sergey Dataiker, Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts Posts: 365 Dataiker
    edited July 17 Answer ✓

    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.

Answers

  • bk211
    bk211 Registered Posts: 12 ✭✭✭✭

    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
    bk211 Registered Posts: 12 ✭✭✭✭

    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?

Setup Info
    Tags
      Help me…