Introduction
Structured data (typically in rows and columns) resides in databases such as PostgreSQL or MySQL. These databases can be hosted locally or offered as a cloud-managed service. When data spans multiple databases or is hosted both locally and in the cloud, a universal data management system provides a centralized platform to handle this dynamic scenario. One of the most common centralized platforms is DBeaver. In this read, we'll set up PostgreSQL locally and as a cloud-managed service with Aiven, access both databases in DBeaver, and link them to Power BI.
The Setups.
Local PostgreSQL setup.
PostgreSQL is an open-source database that is largely contributed to by EnterpriseDB. You can download the installer from EDB shown below. Choose your operating system; in this case, the Windows installer, to start the download.
After completing the download, run the installer as an administrator to get to the setup welcome page.
Proceed to select the components for the installer. You can have all the components checked, or uncheck pgAdmin 4 and StackBuilder since we'll use DBeaver - the universal database management system.
pgAdmin is a PostgreSQL native Graphical User Interface (GUI) used to interact with PostgreSQL databases.
Without a GUI, database interaction can only happen with terminal shell commands using psql.
After selecting the components, you'll be prompted to select your preferred data directory. This directory stores all your database-related data, which can be left as the default.
The next step is choosing your superuser password. The default superuser is postgres. Ensure you provide a password you can easily remember, since it will be required to access the database.
After providing a password, select the port that routes traffic from the internet to the database. The default port is 5432, which can be left as is for a 1st time installation. In case you need to install a later version, you can assign port 5433, but this installation will be left at the default.
Proceed to the preinstallation summary, which details the elements selected and ready to be installed.
After checking the summary, proceed with the installation.
Once the installation is complete, the setup wizard prompts the launch of StackBuilder. This step is optional, as StackBuilder is a useful package manager if you intend to install database drivers or extensions. Click Finish to complete the setup.
Cloud PostgreSQL setup.
One of the cloud-managed PostgreSQL services available is Aiven. To get started, create an account by signing up through the Aiven console.
After signing up, proceed to Services in the left pane to create a new PostgreSQL service using the Create service button at the top right.
Proceed to select the PostgreSQL database.
Select the service tier and proceed to create the service.
The service will first build, then switch to running. This pane provides the credentials required to connect to other software, such as the database management system and visualization tools.
Universal database management system setup.
This system can be installed by downloading the installer from the DBeaver official site. The community version is open-source. Alternatively, it can be downloaded from the Microsoft Store and run the installer.
The installer will start by asking you to select the language.
Proceed to the welcome note and licence agreement.
After agreeing to the licence, you'll select the components and the installation directory.
Finally, proceed with the installation.
The Connections.
Connecting to the localhost and cloud-hosted service.
Launch DBeaver and create a new connection using the plug icon below the File button, select the PostgreSQL database, and proceed to the next pane.
By default, the host is set to localhost on port 5432, and the username is postgres. To complete the connection, enter your password.
As a best practice, test the connection before establishing the database connection.
To connect to the cloud-managed PostgreSQL, create a new connection and use the credentials provided by the Aiven service.
For the connection to be established, the host, port, database name, username, and password are required.
Test the connection before establishing it.
Once the connection is established, create a new script using the SQL editor to write SQL queries.
Connecting to Power BI
To establish a connection with Power BI, launch it and choose the PostgreSQL database using the Get Data button.
For a connection to localhost, the server IP address is 127.0.0.1 on port 5432 or 5433, and the database name is postgres.
The username is postgres; enter your password to connect.
Once the connection is established, access is granted to any existing tables in the database.
To connect to the cloud-managed service, use the hostname and port as the server IP and the database name as defaultdb.
Once the connection is established, access to the existing tables in the database is granted for further manipulation and visualization.
Final thoughts
Data stored across different sources should not remain confined to static, siloed systems. Instead, it should be accessible and transferable between specialized tools and applications, enabling users to manipulate, analyze, and transform the data through secure connections and connection strings.
Top comments (0)