Forum Discussion

juanca's avatar
juanca
Icon for Helper II rankHelper II
9 years ago
Solved

PostgreSQL connection through On-premise Data Gateway

Hi everyone, I need to connect my app.powerbi.com to a PostgreSQL database. I have an on-premise data gateway installed on my computer (the computer where I have my PowerBI Desktop and my report d...
Need Help
  • martina's avatar
    martina
    9 years ago

    Hello,

    for ODBC connection to postgres you need to get installed ODBC driver for postgres;i am using PostgresSQL Unicode(x64).

    After that you have 2 options:

    1) create user DSN via ODBC data source administrator (C:\Windows\System32\odbcad32.exe). In that case the connection string for ODBC data source is "dsn=dsn_name" where dsn_name represents the user dsn created via tool above

    2) alternative option for connection string is: driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name

    where server_name represents a server name or its IP, db_name is name of database.

    For using on-premise gateway you need to use ODBC connection to postgress on power bi desktop. Then you need to configure data sources using ODBC the same way on on-premise gateway.

     

    I am using above approach for connection to multiple different postgres databases.

    I hope it helps.

     

    Kind regards

    M

martina's avatar
martina
Icon for Advocate I rankAdvocate I
9 years ago

Hi,

you need to use ODBC. ODBC must be used also on power bi desktop to ensure that data sources would match.

  • juanca's avatar
    juanca
    Icon for Helper II rankHelper II
    9 years ago

    Hello,


    Thanks for your reply.


    I'm not finding anything about how to do that in the Documentation, could you give some advice? For example, I don't know how to build the "connection string" for the ODBC connection.

     

    Thank you,
    Kind regards.

    • martina's avatar
      martina
      Icon for Advocate I rankAdvocate I
      9 years ago

      Hello,

      for ODBC connection to postgres you need to get installed ODBC driver for postgres;i am using PostgresSQL Unicode(x64).

      After that you have 2 options:

      1) create user DSN via ODBC data source administrator (C:\Windows\System32\odbcad32.exe). In that case the connection string for ODBC data source is "dsn=dsn_name" where dsn_name represents the user dsn created via tool above

      2) alternative option for connection string is: driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name

      where server_name represents a server name or its IP, db_name is name of database.

      For using on-premise gateway you need to use ODBC connection to postgress on power bi desktop. Then you need to configure data sources using ODBC the same way on on-premise gateway.

       

      I am using above approach for connection to multiple different postgres databases.

      I hope it helps.

       

      Kind regards

      M

      • juanca's avatar
        juanca
        Icon for Helper II rankHelper II
        9 years ago

         Hi martina,

         

        I've chosen the second option and I've just tried to access the PostgreSQL through ODBC in Power BI Desktop, it works properly!

         

        Unfortunately this is not the solution I prefer because it forces me to create new queries and rebuild all my data model... too much time consuming.

        I will try to connect directly to PostgreSQL through the personal-gateway instead of on-premise.

        Anyway, I will tag your message as solution because at least it works in PBI Desktop (for any future reader, I haven't checked the solution on PBI Service).

        Thanks a lot,
        Kind regards.