A step-by-step account of connecting Aiven cloud to a local database tool and into Power BI — with the frustrations, the certificates, and the small victories included.
The Problem Nobody Warns You About
You have data. It lives somewhere in the cloud — a PostgreSQL service, spinning quietly on a server you have never physically touched. You want to query it, clean it, visualise it. Simple enough in theory.
Then you open your laptop.
Within ten minutes you are staring at connection errors, downloading files called things like ca.pem, and wondering why a database tutorial never mentioned certificates once. This article is my attempt to fix that. Here is the full road — every step, every reason, and every thing that tripped me up — from creating a PostgreSQL service on Aiven to pulling live data into Power BI.
What You Will Need
Before starting, get these ready:
- An Aiven account (free tier works fine to follow along)
- DBeaver Community Edition installed on your computer
- Power BI Desktop installed
- Basic familiarity with what a database is — you do not need to know SQL yet
Step 1 — Creating a PostgreSQL Service on Aiven
Aiven is a managed cloud database platform. Think of it as renting a database that lives on a server somewhere — you do not manage the server, you just use the database. For data analysts who want to work with live, cloud-hosted data without setting up their own server infrastructure, it is a reasonable starting point.
When you log into Aiven for the first time, you land on a dashboard that asks what kind of service you want to create. Select PostgreSQL. You will be prompted to:
- Choose a cloud provider — Google Cloud, AWS, or Azure all work. For a free-tier test, the region does not matter much, but pick one geographically close to you for better connection speed.
- Select a plan — the free tier gives you a single-node PostgreSQL instance, which is enough for learning and small datasets.
- Name your service — pick something meaningful. I used
jcarsto match the dataset I was working with.
After clicking Create Service, Aiven starts building your database. This takes between one and five minutes. The status indicator on your dashboard will show Rebuilding, then Running. Do not try to connect during the Rebuilding phase — the service is not ready and the connection will fail.
Wait until it says Running before moving to the next step.
One thing worth knowing: Aiven generates connection credentials automatically when the service is created. You will need the host, port, database name, username, and password from the service overview page. Keep that page open — you will be copying from it several times.
Step 2 — Connecting the Aiven Service to DBeaver
DBeaver is a free database management tool. It lets you connect to databases, run SQL queries, view table structures, and import data — all from a desktop interface. If you have never used it before, think of it as the Excel of database tools: not the most powerful option available, but accessible and genuinely useful.
To connect DBeaver to your Aiven PostgreSQL service:
- Open DBeaver and click the plug icon in the top left (or go to Database → New Database Connection)
- Search for PostgreSQL and select it
- Click Next
You will see a connection form asking for several fields. Fill them in directly from your Aiven service overview page:
| Field | Where to find it in Aiven |
|---|---|
| Host | Listed as "Host" in the Connection Information panel |
| Port | Listed next to the host, typically 20025 for Aiven PostgreSQL |
| Database | Usually defaultdb on Aiven free tier |
| Username | Listed as "User" in the Connection Information panel |
| Password | Click "Show password" in Aiven to reveal it |
After filling these in, click Test Connection before clicking Finish. If the test succeeds, you will see a green confirmation message. If it fails, the most common cause at this stage is a firewall or SSL issue — which brings us to the certificate step.
Do not skip the connection test. A failed test here is much easier to debug than a failed connection three steps later when other things have changed.
Step 3 — Importing a Table into DBeaver
Once connected, your Aiven database appears in the DBeaver sidebar as a tree structure. You will see your database name, then schemas, then tables. On a fresh Aiven service, the public schema exists but contains no tables.
To import data from your computer into DBeaver:
- Right-click on the
publicschema in the sidebar - Select Import Data
- Choose your source format — CSV is the most common
- Browse to your file and select it
- DBeaver will preview the data and attempt to map columns to data types automatically
Pay attention to the data type mapping step. DBeaver will make educated guesses — a column of dates might be read as text, or a numeric column with occasional blanks might be flagged as incompatible. Correct any obvious errors before proceeding.
Click Import and wait. For larger files this can take a minute or two. When finished, right-click the public schema and select Refresh — your new table should appear in the tree.
Step 4 — Confirming the Import Worked
To confirm everything landed correctly, right-click your table and select View Data. DBeaver runs a SELECT * query against the table and shows you the results in a spreadsheet-style grid. Scroll through the first few rows and check:
- Column names match what you expected
- Row count looks right
- Data types are sensible — dates look like dates, numbers look like numbers
If something looks wrong here, it is better to re-import with corrected settings than to fix it downstream in Power BI or Power Query. Catching data problems close to the source saves time.
Step 5 — Downloading the SSL Certificate from Aiven
Here is where most tutorials go quiet. They tell you to connect Power BI to PostgreSQL, show a screenshot of the connection dialog, and move on. What they skip is the certificate.
Aiven requires SSL connections. That is not optional — it is a security requirement of the platform. Power BI's PostgreSQL connector supports SSL, but it needs the certificate to verify the connection is legitimate. Without it, the connection either fails outright or connects in an insecure mode that Aiven blocks.
To get the certificate:
- Open your Aiven service overview page
- Scroll down to the Connection Information section
- Find CA Certificate and click Download
You will receive a file called ca.pem. Save it somewhere you can find it — a dedicated folder for your project is a sensible choice. You will need to point Power BI to this file during setup.
Step 6 — Importing the Certificate into Your System
Downloading the certificate is one thing. Making your system trust it is another.
From your system's certificate manager — on Windows, search for Manage user certificates in the Start menu — navigate to Trusted Root Certification Authorities, right-click, and select Import.
Walk through the import wizard and point it to your ca.pem file. Accept the default options unless you have a specific reason to change them.
Why does this matter? When Power BI connects to Aiven, it performs an SSL handshake — a verification process where your computer and the Aiven server confirm they trust each other. If your system does not have the Aiven CA certificate in its trusted store, that handshake fails, and the connection is refused before it even starts.
I am not fully certain whether this step is strictly required on all operating systems and Power BI versions — behaviour may vary. If your connection works without it in Step 7, you can skip this step. If the connection fails with an SSL error, come back here first.
Step 7 — Connecting Power BI to Your Aiven PostgreSQL Database
With the certificate sorted, Power BI is the last piece.
Open Power BI Desktop and go to Home → Get Data → More. Search for PostgreSQL database and select it. You will see a connection dialog asking for:
-
Server — paste your Aiven host and port together, formatted as
hostname:port. For example:pg-xxxx.aivencloud.com:20025 -
Database — type
defaultdb(or whatever your Aiven database is named)
Click OK. Power BI will ask for your username and password — use the same credentials from your Aiven service overview.
If the connection is successful, Power BI shows a Navigator panel listing all tables in your database. Select the tables you want to load, click Load (to bring data straight in) or Transform Data (to open Power Query for cleaning first).
That is it. Your cloud database is now live inside Power BI.
What This Setup Actually Gives You
A PostgreSQL database on Aiven connected to Power BI is not just a technical exercise. It is the foundation of a proper data workflow.
Your data lives in one place — the cloud database. Any tool that can connect to PostgreSQL (DBeaver, Power BI, Python with psycopg2, or any SQL client) can read from it. When the data updates, your Power BI reports update on refresh without you having to manually replace CSV files or re-import spreadsheets.
For a data analyst building a portfolio, this kind of setup also demonstrates something that a cleaned Excel file cannot: you understand how production data environments work. Data in the real world lives in databases. It is queried, not downloaded. Being comfortable with that distinction is worth more than it might seem on a CV.
Quick Reference — Connection Details at a Glance
| Tool | Key Setting | Where to Find It |
|---|---|---|
| DBeaver | Host, Port, Database, Username, Password | Aiven service overview → Connection Information |
| DBeaver | SSL mode | Set to require in the SSL tab |
| Power BI | Server field |
hostname:port from Aiven (e.g. pg-xxxx.aivencloud.com:20025) |
| Power BI | Database |
defaultdb or your named database |
| Certificate | CA file | Download from Aiven → Connection Information → CA Certificate |
Top comments (0)