In this article I will provide a step by step guide on how to connect to a local PostgreSQL database and a PostgreSQL database hosted on an online service, in particular I will be using Aiven to host and connect to the online database.
I will use powerBI to illustrate how to connect and pull data from both the local and online database.
This guide presumes one is familiar with PostgreSQL, DBeaver, powerBI and Aiven. You must have PostgreSQL, DBeaver and powerBI installed and set up properly in your device, as well as a running Aiven service.
Connecting to local database
Connecting to the local PostgreSQL database:
step 1
Start by creating a new database connection through DBeaver and select PostgreSQL from the dropdown menu.
step 2
Enter the connection details prompted ensuring you enter the correct username and password that you set up when installing PostgreSQL in your device.
For a local PostgreSQL database the host is localhost and the port number 5432.
Ensure you test your connection before you finish setting up your connection.
step 3
With the connection established, it it time to create a new schema in your database.
To view the schemas on your database open through:
postgres *(localhost:5432)> Databases > postgres > Schemas
When data is imported into a postgres database with no schema specified, the data will be pushed to the default public schema.
In order to avoid this and to ensure our data is properly arranged, we will create a new schema where we will store our data.
Right click on Schemas and select create new schema.
step 4
To import data into our schema, right click on the created schema and select import data.
Browse through your local files, and select the file you want to import into your local database, then click proceed until the importing process completes.
Connecting powerBI to local PostgreSQL database
Now that we have created a new local database connection and imported data into it, we can open up powerBI and pull the data in our database into powerBI where we can clean, model and perform analysis on it.
step 5
Start with opening a blank report on powerBI and select get data from the HOME ribbon.
Search for PostgreSQL in the data source, and select PostgreSQL database.
step 6
Fill in the connection details in the connect to data source page. Ensure these details match with the details you used when making the database connection through DBeaver.
At this point you should have successfully connected your local database to powerBI and a powerBI should display a list of all the schemas in your local database, from where you can select the file you want to work on and load it into powerBI.
Connecting to an online database.
To connect to a database hosted on an online service, I will start by guiding on how to create a PostgreSQL database connection to an existing Aiven service.
For any of the steps from this point to work, you must note that your Aiven service must be powered on and running.
step A
Begin with creating a connection to the running Aiven service through DBeaver. This process is similar to Step 2 when connecting to a local database, however, it differs in that you must use the connection details provided by your Aiven service.
Copy and paste the connection details from your Aiven service into the required slots, then test your connection to ensure it functions properly before finishing setting up the connection.
step B
With your connection established, you can use the steps 3 and step 4 shown above when discussing working with the local database to create a new schema and import data into your online database.
step C
Before we attempt to connect powerBI to our database, we must ensure we install our certificate, to ensure our connection works properly.
Go to your Aiven service overview and download the CA certificate provided.
Go to the search bar on your windows task bar and search for manage user certificates and open it.
Open the Trusted Root Certification Authorities folders, and right click on the Certificates folder. Click on the ALL Tasks on the pop up then click on Import.
Click next, then browse. Change the file type to all types on the file explore and select the CA certificate you had downloaded, click next until the process completes.
step D
Now its time to access our data through powerBI.
Similar to step 5 open a blank report in powerBI and select get data from the HOME ribbon.
Search for PostgreSQL in the data source, and select PostgreSQL database.
step E
This time you will insert the connection details provided on your Aiven service when setting up the connection settings.
NOTE:
The server detail is filled by inserting the host provided in Aiven, adding a full colon, then adding the port number provided by Aiven.
HOST:port number
Failure to setup the host in this manner leads to a connection failure.
Ensure not to add any blank spaces in the server details.
AT this point powerBI should list the schemas in your Aiven database, you can now select a file to import into powerBI.
Top comments (0)