Power BI Meets PostgreSQL: A Practical Guide to Local and Cloud Database Connections

Most people build their first Power BI dashboard on a CSV or Excel file. But real
business data lives in a database and connecting Power BI directly to a SQL
database instead of exporting spreadsheets means your reports can refresh
automatically as the underlying data changes.

In this guide I connect Power BI to PostgreSQL two ways: a local installation
running on my own machine, and a cloud-hosted instance on Aiven, which
requires SSL. I'll use a car sales and logistics dataset as the working example.

Why connect Power BI directly to a database?

• Real-time-data — refresh the report and it pulls the latest rows, no manual re-export.
• Automation — scheduled refresh means the dashboard stays current without anyone touching it.
• Single source of truth — everyone builds on the same underlying table instead of copies of a spreadsheet.

Part 1: Connecting to a Local PostgreSQL Database

1. Install PostgreSQL locally (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads) and confirm the service is running the default port is 5432.
2. Make sure the target database already contains the table you want to visualize.
3. Open Power BI Desktop, Get Data, More, Database, PostgreSQL database and Connect.
4. Enter:
   • Server: localhost:5432
   • Database: your database name
5. Choose Import or DirectQuery, then enter your PostgreSQL username and password when prompted.
6. The Navigator window lists your tables select the ones you need and click Load, or Transform Data if you want to clean first.

Opening the Get Data window in Power BI Desktop and selecting the PostgreSQL database connector.

The Navigator window listing the tables available in the local PostgreSQL database after connecting.

No SSL setup is needed here since the connection never leaves your machine.

One limitation worth knowing: if you publish this report to the Power BI Service later and want scheduled refresh, a local database needs an On-Premises Data Gateway installed, since the cloud service can't otherwise reach localhost.

Part 2: Connecting to Cloud PostgreSQL on Aiven

Aiven is a managed cloud database provider. Unlike a local database, Aiven
enforces SSL on every connection your credentials and data are encrypted in
transit, which matters a lot more once the database isn't sitting on your own
laptop.

Step 1 Get your connection details from Aiven

Log in to the Aiven console, open your PostgreSQL service, and on the
Overview page copy:

• Host
• Port
• Database name (usually defaultdb)
• Username (usually avnadmin)
• Password

The Aiven PostgreSQL service dashboard showing the host, port, database name, and username needed to connect.

Step 2 Download and trust the CA certificate

Because Aiven uses a self-signed certificate authority, your machine needs to
explicitly trust it before Power BI's driver will accept the connection.

1. On the same Overview page, click Download CA Certificate
2. Rename it
3. Double-click and Install Certificate, Local Machine, Place all certificates in the following store, Trusted Root Certification Authorities and Finish.
4. Restart Power BI Desktop so the trust store change takes effect.

Downloading the CA certificate from the Aiven console, required to establish a trusted SSL connection.

Confirmation that the Aiven CA certificate was successfully installed into the Trusted Root Certification Authorities store on Windows.

Step 3 Connect Power BI to Aiven

1. Get Data, PostgreSQL database, Connect.
2. Server: your-host.aivencloud.com:port (host and port combined with a colon).
3. Database: defaultdb.
4. Expand Advanced options and set SSL mode to require (try verify-ca if you hit certificate errors).
5. Click OK, then in the credentials prompt choose the Database tab (not Windows) and enter your Aiven username and password.

Entering the Aiven host and port into Power BI's PostgreSQL connector, with SSL mode set to require.

The Navigator window listing the jcars_sales table after successfully connecting Power BI to the Aiven-hosted database.

Once connected, the table loads exactly like a local one Power Query, data
modeling, and DAX all work identically regardless of where the data physically lives.

Troubleshooting

"Unable to Connect" connection refused

• Confirm the Aiven service status is "Running," not "Rebuilding."

"Remote certificate is invalid" SSL errors

• Confirm the certificate was actually installed into Trusted Root Certification Authorities, not just downloaded.
• Restart Power BI Desktop after installing the certificate.

Authentication failures

• Re-copy the password directly from the Aiven console typos are the most common cause.
• Confirm you selected the Database authentication tab, not Windows credentials.

Local vs Cloud: Quick Comparison

A local PostgreSQL database doesn't require SSL, since the connection never leaves your machine, while Aiven always requires SSL because the data is traveling over the internet to a cloud server. This also makes local setup slightly simpler overall, since Aiven adds the one-time certificate step.

There's also a difference when it comes to scheduled refresh. If you publish your report to the Power BI Service and want it to refresh automatically, a local database needs an On-Premises Data Gateway installed so the cloud service can reach it. Aiven, being cloud-hosted already, works directly with scheduled refresh no gateway required.

In practice, a local database is a good fit for personal projects or local testing, while a cloud database like Aiven is better suited for team projects and production dashboards that other people need to access.

Conclusion

Connecting Power BI straight to PostgreSQL whether it's sitting on your laptop
or hosted on Aiven turns a one-time spreadsheet export into a living
dashboard. The core workflow (Get Data, PostgreSQL database, enter
connection details, Navigator, Load) is identical in both cases; the only
extra step for the cloud is trusting Aiven's SSL certificate once. After that,
Power Query, data modeling, and DAX work exactly the same either way.