PostgreSQL data refresh using Data Gateway in Power BI report

3 min readSep 29, 2020

Requirement: I have created a Power BI report with PostgreSQL as data source and published the report in Power BI service. I tried to create schedule refresh for the data set but gateway options are not showing

Prerequisites: Power BI Service, Power BI Desktop, Power BI Data Gateway and PostgreSQL foodmart database

Gateway:

1. Download and install Data Gateway

2. Open Gateway and enter the Power BI service credentials

3. Now the gateway is ready to use in Power BI service

Reference Link: https://powerbi.microsoft.com/en-us/gateway/

Using PostgreSQL Datasource:

1. Open Power BI Desktop

2. Click on Get Data and choose PostgreSQL database

3. Enter the database credentials and select the table

4. Create a report and publish in Power BI service

5. Login to Power BI service and go to datasets

6. Click on ellipses of the above published report dataset and select Settings

7. Here we can see the Gateway connection and datasource credentials

8. Expand gateway connection to choose the gateway installed in local machine. We can see that ‘Use a data gateway’ option disabled

9. In the above screenshot we can see Edit credentials under Data source credentials

10. Click on that, enter data source credentials and Click OK. Getting error as below screen shot

11. Now we understood that we are unable to connect PostgreSQL database in Power BI service and we are not getting an option to refresh data. To refresh the data of PostgreSQL database follow the below steps

Connecting to PostgreSQL database using ODBC Connector:

1. Using the below link download the latest version of ODBC connection for PostgreSQL database and Install

https://www.postgresql.org/ftp/odbc/versions/msi/

2. After installation completed, open the Power BI Desktop and choose Get Data

3. Instead of selecting postgresql database, this time we will choose ODBC. Select ODBC and click on Connect

4. A window will open and select “None” as Datasource Name (DSN)

5. In the same window, we have connection string. Here we have to provide proper connection details to connect postgresql database. We won’t be able to click “OK” until we have provided the correct connection string.

6. To get the proper connection string use this link.

https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/

7. We have given the below connection string and able to click on “OK”

Connection String:

Driver={PostgreSQL ANSI(x64)}; Server=localhost; Port=6433; Database=foodmart

8. Enter the credentials and select the tables

9. Create a report and publish in Power BI service

10. Login to Power BI service and open settings of a report dataset

11. Expand gateway connection and now we can see that gateway is enabled and it is connected local gateway