DirectQuery for PostgreSQL? Gateways on Linux?

[ldacey]

My infrastructure is built on Linux and PostgreSQL, but my company is transitioning to Power BI. I was able to install PBI Desktop and connect to a PostgreSQL database on a Windows machine with a Personal Gateway and it works. I want to confirm:

• I am not able to install a Gateway on a Linux OS, correct?
• There is no option to do a DirectQuery or Live Connection to a PostgreSQL source?
• There is no Enterprise Gateway (only Personal) for PostgreSQL

There is a subset of reports which need to be refreshed every 15-30 minutes. which is beyond the limit of 8 scheduled refreshes per day. Since a DirectQuery does not seem to work with PostgreSQL, my potential solution is to purchase an Azure Windows Server with SQL Server 2016 instance. I would then funnel my data (using BCP?) from my PostgreSQL to the Azure instance. Any experts out there who can comment on whether this would be a decent option? For reports which only need to be refreshed daily, I can retain the data in PostgreSQL and use the gateway to run some scheduled refreshes. 

[Anonymous]

@ldacey 

This is something that I'm actively working on since my business ran into a situation that made working around this an impossibility. Including the usage of an enterprise gateway to facilitate real time availability all the time.

Read more about my progress so far here:

https://justreadrtheinstructions.com/post/2019-06-15-directquery-postgres-howto/

[v-yuezhe-msft · Accepted solution]

Hi @ldacey,

Firstly, as per this article, gateway installation requires 64-bit version of Windows 7 / Windows Server 2008 R2 (or later).

Secondly, yes, there is no option to do a DirectQuery or Live Connection to a PostgreSQL source and connecting to PostgreSQL is not supported yet with the Enterprise Gateway currently.

Thirdly, in order to utilize DirectQuery feature, you would need to transfer data from PostgreSQL to other database management system such as SQL Server or Azure SQL Server. You can check the following similar thread and blogs about how to move data from PostgreSQL to as SQL Server or Azure SQL Server.

Best tool to migrate a PostgreSQL database to MS SQL 2005
Export data from Postgres to SQL Server using SSIS
PostgreSQL to SQL Azure Migration and Sync

Thanks,
Lydia Zhang