# How to Connect Power BI to PostgreSQL: Local and Aiven Cloud Setup

Power BI is a powerful business intelligence tool that enables you to visualize and analyze data from various sources. PostgreSQL is a popular open-source relational database that can serve as an excellent data source for Power BI. In this article, we'll walk through two scenarios: connecting Power BI to a local PostgreSQL instance and connecting to PostgreSQL hosted on Aiven cloud.

Prerequisites

• Power BI Desktop: Download and install from the Microsoft Power BI website
• PostgreSQL: Either a local installation or an Aiven account
• Npgsql: PostgreSQL data provider for .NET (required for Power BI connection)

Part 1: Connecting Power BI to Local PostgreSQL

Step 1: Connect Power BI to Local PostgreSQL

Open Power BI Desktop and follow these steps:

1. Click on Get Data in the Home ribbon
2. Search for PostgreSQL database and select it
3. Click Connect
4. In the PostgreSQL database dialog, enter:
   • Server: localhost (or 127.0.0.1)
   • Database: defaultdb
   • Data Connectivity mode: Import or DirectQuery
5. Click OK
6. Enter your PostgreSQL credentials:
   • User name: postgres (or your username)
   • Password: Your PostgreSQL password
7. Click Connect

Step 2: Load Data into Power BI

After authentication, the Navigator window will appear showing available tables:

1. Select the products table from the list
2. Click Load to import the data directly, or Transform Data to open Power Query Editor for data transformation

Part 2: Connecting Power BI to PostgreSQL on Aiven

Step 1: Create a PostgreSQL Service on Aiven

If you don't have an Aiven account, sign up at aiven.io. Then:

1. Log in to your Aiven console
2. Click Create Service
3. Select PostgreSQL as the service type
4. Choose your cloud provider and region
5. Select a service plan (you can start with the free tier for testing)
6. Give your service a name (e.g., "powerbi-postgres")
7. Click Create Service

Wait for the service to start (this usually takes 2-5 minutes). The status will change to "Running" when ready.

Aiven console showing a running PostgreSQL service

Step 2: Retrieve Connection Information

Once your service is running, navigate to the service overview page to find connection details:

• Host: Your Aiven PostgreSQL hostname (e.g., powerbi-postgres-project.aivencloud.com)
• Port: Usually 12345 (varies by deployment)
• User: avnadmin (default)
• Password: Click the eye icon to reveal

This is the aiven dashboard.

Step 3: Open PowerBI

On opening power bi click on blank report and follow the images below to be able to upload data from my postgres database on aiven . Follow the images below to be able to upload focusing on the marked areas on the images.

leads to:

leads to:

leads to:

Set Data Connectivity to DirectQuery

leads to:

The error happens when Power BI tries to connect to PostgreSQL using SSL, but your PostgreSQL server does NOT have a valid SSL certificate.To fix this we download the CA certificate on aiven dashboard.Go to your local machine and search “manage computer certificates”,then click on trusted root certification authorities,right click on certificates,then to all tasks,move to import ,then browse to find the downloaded certificate then make sure you have a successful import.After this restart your PowerBI.

Then start all over the process of connecting powerbi to aiven from the first screenshot but now you wont encounter the error but you will get to see all tables you have in your postgres database,just as below:

you check the tables you need just like i have done on your left side of the screen., and we end up with this on PowerBI with the tables uploaded(on the right).