cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

I am not able to connect Postgre SQL DATABASE

Hi Friends,

 

I am not able to connect post gre sql db in power BI desktop. getting the trail mail error.

 

The connector is not  configured correctly.

this connector requires one or more additional components to be  installed before  it can be used.

 

Kindly check the above error and please give me some solutions.

 

Awaiting for your help.

 

Regards,

Rajendra

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: I am not able to connect Postgre SQL DATABASE

Hi @Anonymous,

 

From the error message, the issue is caused by that the connector is not be configured properly. Please refer to this article to configure connector again: Power BI Connectivity to PostgreSQL.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Moderator v-qiuyu-msft
Moderator

Re: I am not able to connect Postgre SQL DATABASE

Hi @Anonymous,

 

From the error message, the issue is caused by that the connector is not be configured properly. Please refer to this article to configure connector again: Power BI Connectivity to PostgreSQL.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: I am not able to connect Postgre SQL DATABASE

Hi Yu,

 

we have raised firewall request to connect our POSTGRE SQL to access our Data available in DB.

 

But still we are facing the below issue. 

 

Kindly check and let us know if any changes required.

 

pg.png

Highlighted
markcaple New Member
New Member

Re: I am not able to connect Postgre SQL DATABASE

Having tried to get this going for a few hours with no success decided to change the approach and use a postgreSQL ODBC driver and it worked straight away.

 

First get the driver from

 

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

 

I downloaded the psqlodbc_09_06_0100-x64.zip and ran psqlodbc_x64.msi

 

Now click on 'Get Data' in PowerBI and select ODBC. Choose  <None> in Data source name (DSN) and click on Advanced Options and enter a connection string which you can find at

 

https://www.connectionstrings.com/postgresql/

 

I used something like

 

Driver={PostgreSQL ANSI(x64)};Server=rds-postgresql-xxxx-warehouse.cftg4xxxxxt.ap-southeast-2.rds.amazonaws.com;Port=5432;Database=myDatabase;

 

My server is an AWS server. The most important part is the driver name which you can search for in your ODBC settings and you will see a warning icon by the side of the connect string until you get this right.

 

jaykilleen Regular Visitor
Regular Visitor

Re: I am not able to connect Postgre SQL DATABASE

I have read so much trying to get this all to work.  Your suggestion of bypassing all the GUI crap and just using the connection string and OLDB worked perfectly.

 

Thank you for taking the time to share your connection string so I could do what should have been a simple process and connect to a remote PostgreSQL database (behind SSL encryption) and access my data.

 

- npgsql didn't work

- getting remote certificates and adding through MMC didn't work (hostname didn't match)

- even ODBC didn't work for me because of some architecture mis match

 

Selecting ODBC, None, and using a connection string worked. 

 

GG!, Well Played!! That was fun! Everybody Dance!

jaykilleen Regular Visitor
Regular Visitor

Re: I am not able to connect Postgre SQL DATABASE

Further to your excellent answer. You can create a blank query and put in (replace hostname with your server ip address or name (ie google.com or 172.217.25.174) and database_name with the name of your database.

 

This requires the ODBC driver installed as per @markcaple. A good way to double check (what I did) if things are working is to use PGAdmin4 and try and connect using that. It comes with everything built in and/or has a Stack Builder to bring in things like npgsql or the OLDB drivers so that things can work. Whereas PowerBI says it can be done in the brochure and then ... ... ...

 

You will probably need admin rights so if you are an analyst in a big enterprise with an IT department then probably just uninstall and use something else. Sounds like Tableau works OOTB.

 

let
  Source = Odbc.DataSource(
    "
      Driver={PostgreSQL ANSI(x64)};
      Server=hostname;
      Port=5432;
      Database=database_name;
    ", 
    [HierarchicalNavigation=false]
  )
in
  Source
Pavithra Frequent Visitor
Frequent Visitor

Re: I am not able to connect Postgre SQL DATABASE

Hi @markcaple,

 

I am trying the approach you have mentioned to connect Power BI to Postgre SQL DB. I have downloaded and installed the latest version of the driver (psqlodbc_09_06_0310-x64.zip) from https://www.postgresql.org/ftp/odbc/versions/msi/

 

However, I get a connection timeout error ( but a direct connection to Postgre SQL DB with the same credentials is working fine).

 

Any pointers to what I could be missing? The only option I can think of is some firewall blocking the connection.

 

Thanks,

Pavithra

 

 

purnima13 Regular Visitor
Regular Visitor

Re: I am not able to connect Postgre SQL DATABASE

Following the advice of @markcaple
I just had to tweek the connection sting a bit with sslmode=require; at the end.
Worked like charm 🙂

 

Driver={PostgreSQL ANSI(x64)};Server=rds-postgresql-xxxx-warehouse.cftg4xxxxxt.ap-southeast-2.rds.amazonaws.com;Port=5432;Database=myDatabase; sslmode=require;

paulob_involves Regular Visitor
Regular Visitor

Re: I am not able to connect Postgre SQL DATABASE

I did it and it worked in Power BI Desktop, but web refresh still won't work. Is there a way to make web refresh of Postgres data work?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 58 members 1,168 guests
Please welcome our newest community members: