Blog Post

Power BI Community Blog
2 MIN READ

Configuring Power BI Connectivity to PostgreSQL Database

ashishrj's avatar
ashishrj
Icon for Power Participant rankPower Participant
10 years ago

Prerequisites:

Power BI Desktop, PostgreSQL Database, pgAdmin III, Visual Studio 2008 or higher

 

Introduction

 

Power BI supports connectivity to different databases such as SQL Server, MySQL, Oracle and many more (list of all supported databases given here ). By providing necessary details such as server name, database name, username & password, you can easily connect to database and start exploring data using different visualizations in the form of reports/dashboards.

 

This article will walk you through the necessary steps required to configure in your system before establishing connection of Power BI Desktop Tool to PostgreSQL Database.

After installation of PostgreSQL database in your machine, if you try connecting Power BI Desktop Tool to PostgreSQL Database, you will get following error as shown in the screenshot below:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Following are the steps required to connect Power BI Desktop Tool to PostgreSQL Database:

 

  1. Setup PostgreSQL Client
  2. Connect Power BI Desktop Tool to PostgreSQL

Below are the steps that will walk you through setting up PostgreSQL Client:

 

Step 1: Download the latest MSI from this page and install it in your machine.

 

Step 2: Once this is done, you can now try connecting Power BI to PostgreSQL. However you will notice below error

 

 

 

Once you restart your machine, you can now connect to PostgreSQL and start playing with your data. Below are the steps for connecting Power BI Desktop Tool to PostgreSQL:

 

Step 1: Open pgAdmin III interface to create database, table and few records to display in your report as shown in screen capture below

 

 

 

Step 2: Open Power BI Desktop Tool and click ‘Get Data’ and select PostgreSQL Database as shown below

 

Step 3: Enter server name (in our case it is localhost), database name, username and password as shown in below screen capture

 

 

 

 

Step 4: Select required table from database and click load as shown in screen capture below

 

 

 

Step 5: Once data is loaded in model, you can start playing around with the data and create beautiful visualizations/reports as shown in image below and later publish it to Power BI Online Service

Updated 9 years ago
Version 2.0
how to
tips & tricks
ashishrj's avatar
Icon for Power Participant rankPower Participant
Joined July 12, 2015
View Profile
Power BI Community Blog
On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.

27 Comments

  • jason_rmh's avatar
    jason_rmh
    New Member
    10 years ago

    Great post ashishrj.

    If using npgsql v3.x.x there no longer any need for monosecurity.dll.

    https://github.com/npgsql/npgsql/releases

     

    Also, if you select the option to install to the GAC during install, you will not need to copy the dll into windows assembly.

     

    This may help others as it took me a while to find the answer.

    Thanks

    Jason

  • BetoFranco's avatar
    BetoFranco
    New Member
    10 years ago

    Hello,
    We follow all the steps suggested, but can not connect to our PostgreSQL base.
    We have a hosted basis of AWS.

    Thanks

  • kevinstruckhoff's avatar
    kevinstruckhoff
    New Member
    10 years ago

    Hi, why is VS 2008 or later needed? If I don't have it, am I out of luck for using postgres w/ power bi? That doesn't make sense.

  • jaykilleen's avatar
    jaykilleen
    Icon for Advocate II rankAdvocate II
    9 years ago

    Any way to do Native Query with SELECT WHERE statements?

  • jwbecalm's avatar
    jwbecalm
    Frequent Visitor
    9 years ago

    hi,  ashishrj, I have installed Npgsql-3.2.4.msi, and restart computer, but the issue is still. How should I do?

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    If you have a 4.1.1 version of npgsql installed and run into could not load assembly System.Runtime.CompilerServices.Unsafe. Downgrade to npgsql 4.0.10 to get unblocked.

  • JasonC_XBI's avatar
    JasonC_XBI
    Icon for Advocate II rankAdvocate II
    6 years ago

    Does anyone know why the onboard PostgreSQL connector doesn't work with PostgreSQL databases on AWS? 

  • GALROY's avatar
    GALROY
    New Member
    10 years ago


    Hi ashishrj,

     

    Quick question. I got the connection up and running and I am able to bring data from PostgresSQL to PBI Desktop. However, every time I refresh the data manually, I need to call the refresh twice. First time it will fail, then it will succeed in the second attempt.

    In the gateway, it consistently fails (I am not sure whether it does retries or not).

     

    Any thoughts?

     

  • ashishrj's avatar
    ashishrj
    Icon for Power Participant rankPower Participant
    10 years ago

    Hi GALROY, this is something unusual. You can send a frown as shown below or you can post this issue/bug on Power BI community (under post your questions) section so that it can be notified by Power BI Team. 

     

     

  • Ali414's avatar
    Ali414
    New Member
    10 years ago

    Thank you for your post. but it doesn't helped me. Is there any other idea? Could you please provide us with the ininformation ?