setting a connection to PostgreSQL database on "Connection"

sarra

Hello beautiful people,

I have would like to set a connection to a PostgreSQL database that I have on my computer, on Administration >> connections , as an admin of my DSS, I tried to create a new connection but when arriving the section below, the login and password, host and database name (that i have locally do not pass)

how do we do it in general please?

the target of this connection is that i want to import some projects on my new dss environment, but the postgresql dataframe do not display even by checking the box import input data while exporting.

Alexandru

Hi Sarra,

Even locally your SQL client( e.g psql , PgAdmin) would still be indirectly passing the default port ( 5432), host as localhost, and the default user postgres. You are not prompted for a password because the connection is LOCAL so by default would be trusted by Postgres config and not prompt for a password. https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html

You can set up a dataset/user following these steps which you can then use in connection details.

https://knowledge.dataiku.com/latest/courses/sql-integration/tech-prerequisites/configure-database.html

Are you running DSS locally?

Otherwise, this may not work since the DSS needs to connect to Postgres database. You would need to make sure Postgres default port 5432 is open and available on your IP which DSS can could connect to.

Let me know if that helps

sarra

hello sir,

yes I run dss locally, I have a database and would like to connect to it,

i'm still confused ...

tgb417

@sarra

In addition to @AlexT
‘s great guidance. You might also find the Dataiku academy course to be a helpful start in setting up PostgreSQL. I know that I found this useful.

https://academy.dataiku.com/integration-with-sql-databases-1

Getting PostgreSQL to work with Dataiku Data Science Studio (DSS) can be done and makes DSS even more powerful. That said it can also be a bit tricky to get to work. If you continue to have challenges with the setup. It might be helpful to share a bit more about your configuration. Some of the questions that come to my mind include:

How are you running DSS?

• what operating system are you using to run DSS
  • linux
  • mac os
  • windows (note this can not be done directly you need to figure out how to run a Linux environment via some form of virtualization)
• where is that Operating system running
  • your local computer
    • mac
    • pc
      • inside a VM
      • windows subsystem for Linux?
      • running as the base OS on my computer
  • containerized
  • in the cloud
• Where is the PostgreSQL running?
  • In the same OS as DSS
  • in a different OS as DSS but on the same computer. Please explain.
  • In a different OS as DSS and on different computer.
• what version and implementation of PostgreSQL are you using?
• can you connect to the database with any other tools,
  • if so how are you doing that test?
  • is this connection from the same OS as Dataiku or a different OS as Dataiku DSS.
• What if any firewall software is running in the operating system hosting DSS, And or PostgreSQL?

Each of the items above might lead to configuration differences. The easies way to get this setup the first time is to run PostgreSQL and DSS in the same OS on the same computer. Also I know that some times I have problems with making the connection because I have forgotten to start PostgreSQL. When starting DSS you are not automatically starting PostgreSQL, you have to do that separately. One has to remember to start PostgreSQL before trying to make the connection.

Hope these questions are helpful. Answering these questions in the forum might help the community better assist you.

Also the Tech Support team at Dataiku are very good. If you are using a paid license they can be very very helpful.

Angelo

Hi Tom (@tgb417
)

Thank you for such a detailed post and great suggestions you've made.

I'm having similar problems with the original post so I'd like to provide some more info in case it helps resolve it.

- I'm running DSS in VirtualBox, and have installed DSS 10.0.2

- My host OS is Windows 10, so when I open up the DSS server it's on Windows Chrome browser using the following address: 127.0.0.1:10000

- I run Postgres 14.0 in Windows on the same laptop I use to kick launch the DSS server using Chrome

- I'm currently re-doing the https://academy.dataiku.com/integration-with-sql-databases-1 course you suggested. In the past, when I first tried it, it seemed to have worked but can't get to connect now for some reason ( I wonder if I need to remove Postgres and reinstall it just in case)

- I have followed the instructions in https://knowledge.dataiku.com/latest/courses/sql-integration/tech-prerequisites/configure-database.html but the "Hands-On: Sync Recipe" or "Hands-On:Prepare Recipe" of the course above doesn't seem to work when I try to select the "dku" database created in Postgres

- I can see the created "dku" databases both via the terminal (SQL Shell for windows) and the PgAdmin tool (see example attached from SQL shell

Any suggestion would be great