How to Connect PostgreSQL to Power BI Using Local PostgreSQL and Aiven

Source: Dev.to

Power BI doesn’t ship with a native PostgreSQL connector out of the box — but with the right setup, it becomes a clean, reliable pipeline for analytics.

In this guide you’ll learn how to connect Power BI to:

• PostgreSQL running locally on your machine
• PostgreSQL hosted on Aiven, a fully managed cloud service

You’ll see the required configuration steps, drivers, connection settings, and common errors to avoid. This tutorial is built using Windows + DBeaver + PostgreSQL + Power BI Desktop.

Prerequisites

• PostgreSQL installed locally – Windows installer:
• Power BI Desktop installed – Microsoft Store or download page
• Npgsql .NET Data Provider – required for Power BI to talk to PostgreSQL. Download the MSI, install it, and restart Power BI.
• DBeaver (optional but recommended) – used to import and manage your PostgreSQL data.

Part 1 — Connecting Local PostgreSQL to Power BI

Step 1: Collect your PostgreSQL connection details

Open pgAdmin or DBeaver and confirm:

• Host: localhost
• Port: 5432
• Database name (e.g., healthcare_db)
• Username (e.g., postgres)
• Password: your local DB password

Step 2: Install the Npgsql provider

1. Download the provider.
2. Install the MSI.
3. Restart your machine (important).
4. Reopen Power BI.

Step 3: Connect in Power BI

Home → Get Data → More
Search “PostgreSQL”
Select “PostgreSQL Database”

Enter your DB credentials and click OK.

Step 4: Allow native database queries

If Power BI prompts Allow Native Queries?, click Run. Your tables will appear in the Navigator window. Select the tables you need (e.g., patients, doctors, appointments, bills) and click Load.

Part 2 — Connecting Aiven PostgreSQL to Power BI

Aiven is a cloud‑hosted PostgreSQL provider that uses SSL certificates.

Step 1: Log in to Aiven

Go to your Aiven console and open your PostgreSQL service.

Step 2: Download SSL certificates

In the service dashboard: Service Settings → Connection Information → SSL → Download CA Certificate. Save the .crt file somewhere safe.

Step 3: Locate your Aiven connection string

Aiven provides the details in the console. Break it down:

• Host: pg-yourproject.aivencloud.com
• Port: XXXXX
• User: avnadmin
• Password: your generated password
• Database: defaultdb
• SSL: Required

Step 4: Connect Power BI to Aiven

1. Home → Get Data → PostgreSQL

2. In the connection dialog, expand Advanced Options and paste the following into Additional connection string parameters (adjust as needed):

   SSL Mode=Require;Trust Server Certificate=True;

3. Click OK, then enter the Aiven username and password.

Troubleshooting Common Errors

Bonus Section — How to Import CSV Files Into PostgreSQL Using DBeaver

1. DBeaver → Right‑click schema (e.g., hospital) → Import Data
2. Select CSV, check Header, set delimiter to ,.
3. Map columns; rename to snake_case lowercase (best practice).
4. Enable Use Bulk Load, then finish.

Final Output — Power BI Dashboard

Once connected (local or Aiven), you can build visuals such as:

• Appointment trend line
• Total bills: Paid vs. Outstanding
• Doctor workload distribution
• Patient demographic distribution
• Billing per admission
• Specialization performance

Final Thoughts

Connecting PostgreSQL to Power BI is a core data‑engineering skill.

• Local PostgreSQL teaches fundamentals — drivers, ports, credentials, schemas.
• Aiven PostgreSQL teaches real‑world cloud connection handling — SSL, ports, certificates, and connection strings.

Mastering both lets you work across local → cloud workflows, which is how modern analytics pipelines operate.