How to Connect PostgreSQL to Power BI Using Local PostgreSQL and Aiven (Full Guide)
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, I’ll walk you through how to connect Power BI to:
•PostgreSQL running locally on your machine
•PostgreSQL hosted on Aiven, a fully managed cloud service
You’ll learn all the required configuration steps, drivers, connection settings, and common errors to avoid. This tutorial is built using Windows + DBeaver + PostgreSQL + Power BI Desktop.
Prerequisites
Before you begin, ensure you have:
✔ PostgreSQL installed locally
Windows installer: https://www.postgresql.org/download/
✔ Power BI Desktop installed
Microsoft Store or https://powerbi.microsoft.com/
✔ Npgsql .NET Data Provider
Required for Power BI to talk to PostgreSQL.
Download: https://www.npgsql.org/
Install the MSI → restart Power BI.
✔ DBeaver (optional but recommended)
Used to import and manage your PostgreSQL data.
https://dbeaver.io/
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
Power BI requires a PostgreSQL driver to connect.
1. Download from: https://www.npgsql.org/download.html
2. Install the MSI.
3. Restart your machine (important).
4. Reopen Power BI.
Step 3: Connect in Power BI
Inside Power BI:
1. Home → Get Data → More
2. Search PostgreSQL
3. Select PostgreSQL Database
- Enter your DB credentials
Step 4: Allow native database queries
If Power BI prompts:
Allow Native Queries?
Click Run.
Your tables will appear in the Navigator window.
[Screenshot: Power BI Navigator listing your PostgreSQL tables]
Select the tables (patients, doctors, appointments, bills, etc.) → Load.
Part 2 — Connecting Aiven PostgreSQL to Power BI
Aiven is a cloud-hosted Postgres provider.
It uses SSL certificates — this is the only tricky part.
Step 1: Log in to Aiven
Go to https://console.aiven.io
Open your PostgreSQL service.
[Screenshot: Aiven PostgreSQL dashboard]
Step 2: Download SSL Certificates
In the service dashboard:
Service Settings → Connection Information → SSL → Download CA Certificate
You will get a .crt file.
Save it somewhere safe.
Step 3: Locate your Aiven connection string
Aiven provides this in the console,
Break it down:
• Host: pg-yourproject.aivencloud.com
• Port: 00000
• User: avnadmin
• Password: your generated password
• Database: defaultdb
• SSL: Required
Step 4: Connect Power BI to Aiven
In Power BI:
1. Home → Get Data → PostgreSQL
2. Enter:
- Go to Advanced Options → Paste this in Additional connection string parameters:
- Click OK Enter username and password.
Troubleshooting Common Errors
❌ “We couldn’t authenticate using the credentials provided.”
✔ Check username and password
✔ Ensure you copied the Aiven password without spaces
❌ “The Npgsql Provider is not installed.”
Install the Npgsql driver again → restart Power BI.
❌ “Certificate not trusted.”
Use either:
• Trust Server Certificate=True
or
• Import Aiven CA cert into Windows Certificate Store
Bonus Section — How to Import CSV Files Into PostgreSQL Using DBeaver
Many students struggled with this in class, so here’s a fast version.
✔ Step 1 — DBeaver → Right-click schema (e.g., hospital) → Import Data
[Screenshot placeholder]
✔ Step 2 — Select CSV
Check “Header”
Set delimiter = ,
✔ Step 3 — Map columns
Rename columns to snake_case lowercase (best practice)
If you skip renaming, PostgreSQL will lock your life with "QuotedNames" forever.
✔ Step 4 — Bulk Load
Enable Use Bulk Load → 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.
Two big takeaways from this assignment:
Local PostgreSQL teaches fundamentals — drivers, ports, credentials, schemas.
Aiven PostgreSQL teaches real-world cloud connection handling — SSL, ports, certs, and connection strings.
Once you can connect both, you can work across local → cloud workflows, which is how modern analytics pipelines operate.
Top comments (0)