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

Introduction

Power BI is Microsoft's powerful business analytics tool that enables organizations to visualize data and share insights across the enterprise. PostgreSQL, as a robust, open-source relational database system, has become a popular data source for many businesses. This guide will walk you through connecting Power BI to both locally hosted PostgreSQL instances and cloud-based PostgreSQL databases hosted on Aiven's managed platform.

Prerequisites

Before starting, ensure you have the following:

• Power BI Desktop installed (latest version recommended)
• PostgreSQL database (either local installation or Aiven service)
• Basic understanding of database connections and SQL
• Network access to your PostgreSQL instance
• Appropriate permissions to read from the database

Method 1: Connecting to Local PostgreSQL

Step 1: Install PostgreSQL ODBC Driver

The PostgreSQL ODBC driver is essential for establishing the connection between Power BI and your PostgreSQL database.

Windows Installation:

1. Visit the official PostgreSQL ODBC driver download page: https://www.postgresql.org/ftp/odbc/versions/msi/
2. Download the appropriate version for your system (32-bit or 64-bit)
3. Run the installer and follow the setup wizard

Verification:

1. Open "ODBC Data Source Administrator" (64-bit) from Windows Search
2. Navigate to the "Drivers" tab
3. Look for "PostgreSQL Unicode" or "PostgreSQL ANSI" in the list

Step 2: Gather Connection Information

Collect the following connection details for your local PostgreSQL instance:

• Host: localhost or 127.0.0.1
• Port: 5432 (default PostgreSQL port)
• Database Name: [Your specific database name]
• Username: [Your PostgreSQL username]
• Password: [Your PostgreSQL password]

Finding Your Database Information:

-- Connect to PostgreSQL via psql and run:
\l -- List all databases
\conninfo -- Show current connection information

Step 3: Connect from Power BI Desktop

Follow these steps to establish the connection:

1. Launch Power BI Desktop
2. From the Home ribbon, click "Get Data"

3. In the dropdown, select "Database" → "PostgreSQL database"

4. Configure Connection Settings:

   • Server: localhost
   • Database: your_database_name
   • Data Connectivity Mode:
     • Import: Data is loaded into Power BI (recommended for most cases)
     • DirectQuery: Live connection to the database

5. Authentication:

   • Select "Database" authentication method
   • Enter your PostgreSQL username and password
   • Click "Connect"

Step 4: Select and Load Data

1. In the Navigator dialog, you'll see a list of available tables and views
2. Select the tables you want to import
3. Optionally, click "Transform Data" to clean and shape your data before loading
4. Click "Load" to import the selected data into Power BI

Method 2: Connecting to Aiven Hosted PostgreSQL

Step 1: Set Up Aiven PostgreSQL Service

Using Aiven Web Console:

1. Log in to your Aiven account
2. Click "Create service"
3. Select "PostgreSQL" as the service type
4. Choose your plan (hobbyist, startup, business)
5. Select your cloud provider and region
6. Click "Create service"

Using Aiven CLI:

# Install Aiven CLI first (if not already installed)
pip install aiven-client

# Create PostgreSQL service
avn service create my-pg-service \
  --service-type pg \
  --plan hobbyist \
  --cloud aws-us-east-1

Step 3: Download SSL Certificate

Aiven requires SSL connections for security. Download the CA certificate:

From Aiven Web Console:

Go to your service overview

Click "Download CA certificate"

Using Aiven CLI:

bash
# Download CA certificate
avn service user-creds-download \
  --username avnadmin \
  my-pg-service \
  --file ca.pem

Step 4: Connect Power BI to Aiven PostgreSQL

Open Power BI Desktop and click "Get Data"

Select "Database" → "PostgreSQL database"

Enter Aiven Connection Details:

Server: your-service-project.aivencloud.com:port

Database: defaultdb(or your specific database name)

Data Connectivity Mode: Import(recommended)

Advanced Options:

Check the "Use SSL" checkbox

Add additional SSL parameters if needed

Step 5: Configure SSL Connection Parameters

In the advanced options, you may need to specify SSL parameters:

text
sslcompression=0&sslmode=verify-full&sslrootcert=path\to\ca.pem