cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juanca
Helper II
Helper II

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 design, etc.).

The issue is that when I try to set the PostgreSQL origin in the Gateway Configuration in app.powerbi.com, the specific option for this database doesn't exist.

Could I set the PostgreSQL origin through another origin (i.e. SQL Server or MySQL) ? If this is not possible, which is the alternative?

Thanks a lot,
Kind regards.

1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
martina
Advocate I
Advocate I

Hi,

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

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.

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

View solution in original post

May I know which postgresql odbc driver to install on gateway server? 

I am run win server 2012. 

 

Greatly appreciate it!

 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.

 

Hello, to avoid recreating all queries you can just create ODBC connection for of them and then via advanced editor modify the source (i am using select query in SQL statement)

postgres: 

Source = PostgreSQL.Database("server", "db_name", [Query="select …. "])

 

to ODBC:

Source= Odbc.Query("driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name", "select … ")

 

or (if not SQL statement used)

Source= Odbc.DataSource("driver={PostgreSQL Unicode(x64)};server=server_name;port=5432;database=db_name",...

 

Kind regards

M

Hi, I have sybase quries how change it to postgreSQL?

Source=PostgreSQL.Database(.......)...

Hi @martina,

Thanks for your advice, I'll try to apply it this weekend, I'll let you know the output.

Best regards.

Hi @martina,

 

I had no problem in order to modify the data model in my PBI Desktop, nice!

 

Thanks a lot for your help till now, I'll tell you a new problem that I'm facing, maybe you have deal with something similar yet and you have an answer.

 
The problem now is with the On-Premise Gateway, it's properly configured as we can see in the "Gateweay Manager" screen below, but it does not appear in my dataset when I try to refresh it for example:

 

ODBC gateway connection.png

 

Do you have any idea? It seems that I'm not the only one with this problem as you can see here.

 

Thanks a lot again,

Kind regards.

 

 

Hi @martina,

 

All is working fine now, I've been able to have my on-premise connector up and running without issues and after that, the problem with ODBC connector was related with the ODBC configuration in my Power BI Desktop, I wrote a DataSourceName when not necessary.

 

Thanks again for your support,

Kind regards.

linas
Frequent Visitor

@juanca can you please explain the following in more details "was related with the ODBC configuration in my Power BI Desktop, I wrote a DataSourceName when not necessary."? I'm also stuck where my on-premises gateway + odbc/postgresql works fine, but the dataset published cannot see it (data gateway option greyed out).

Hi,

data source in power bi desktop must match to data source created on a gateway. In other words DSN name used in ODBC connection must be the same (on desktop as well as gateway).

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors