Connecting DBeaver to PostgreSQL via JDBC and ODBC Driver

PostgreSQL is one of the most advanced Relational Databases offering standard SQL features along with some modern ones like triggers, transaction integrity, etc. However, Data Management is one of the key areas for any Database Administrator.

DBeaver is a free and universal Database Management Tool for Database Administrators, Developers, Data Analysts, and everyone dealing with Databases. DBeaver comes with many powerful built-in features that make it easy and fun to access the databases. It can be used to access and manage any Database or Cloud Application that has an ODBC or JDBC driver, such as PostgreSQL, Microsoft SQL Server, MySQL, or Oracle.

Before getting into the DBeaver PostgreSQL connection, let’s get into the need of this integration first!

Need for DBeaver PostgreSQL Connection

DBeaver is a Database Management Client that allows a Database Administrator to effectively interact with the data in any Database. It is always a good practice to use a Database Client instead of directly interacting with the Database Server. With the DBeaver PostgreSQL connection, you can easily manage PostgreSQL data with the help of powerful features and Data Visualization Tools offered by DBeaver. Here’s what you get when connecting PostgreSQL data to DBeaver.

• Connecting DBeaver PostgreSQL allows you to execute SQL queries on Postgres data from within DBeaver.
• It becomes easy to manipulate Postgres data in DBeaver with its powerful administration features and interactive Graphical User Interface (GUI).
• You can further create and generate Analytical Reports based on the records from various Data Storage.
• DBeaver provides you with the ability to export information in convenient and accepted formats. For example, ER Diagrams can be exported in multiple formats such as GIF, PNG, BMP, and GraphML.
• On top of all that, you get to browse and edit metadata, manage SQL Scripts, generate DDL, test data generation, render ER Diagrams, etc. with a DBeaver PostgreSQL connection.

Connecting DBeaver PostgreSQL via PostgreSQL JDBC Driver

You can easily connect your PostgreSQL data to your DBeaver Database Tool in order to optimize your PostgreSQL management in the Cloud. To establish the DBeaver PostgreSQL connection, you’ll need to download the PostgreSQL JDBC Driver. The integration process is pretty much straightforward and doesn’t require any programming experience. For the purposes of this demonstration, the Community Edition of DBeaver is used to retrieve data from PostgreSQL via the PostgreSQL JDBC Driver.

Follow the below-mentioned steps to establish the DBeaver PostgreSQL connection.

• Step 1: Install DBeaver Community Edition
• Step 2: Download PostgreSQL JDBC Driver
• Step 3: Create a Connection to PostgreSQL Data
• Step 4: Query Data

Install DBeaver Community Edition

• You can download the relevant DBeaver Community Edition based on your Operating System from its official website.

• Once downloaded, you can open and run the installer. A Setup Wizard will now be displayed on your screen, kind of things that you already know. Just click on “Next”.

• Accept the License Agreement and select the users for whom you want to install DBeaver. Then, click on “Next”.

• Select the features of the DBeaver Community Edition that you would like to install. If you want to associate all your .SQL files to DBeaver, you can tick the “Associate .SQL files” checkbox. Then, click on “Next”.

• Select a location for the installation and proceed ahead with the installation. Once the installation finishes, click on the “Finish” button to complete the installation process.

Download PostgreSQL JDBC Driver

• You can download the recent version of PostgreSQL JDBC Driver from PostgreSQL’s official website.

• Just click on the version of the Driver that you want to download, and it will be downloaded automatically on your local system.

Create a Connection to PostgreSQL Data

• This brings us to the main part of the DBeaver PostgreSQL connection. Launch your DBeaver application and click on the “Database” button located above the toolbar. Then, click on “New Database Connection”.

• It will now prompt you to select the Database you want to connect to. Select PostgreSQL and click on “Next”.

• The “Connection Settings” tab will open up and it will by default pick up the details of your system/host (the default port is 5432). Specify the Database properties, and feed in the correct password of the Postgres Database User.

• Now, click on “Edit Driver Settings”.
• Click on the “Add File” button to add the .jar file of the PostgreSQL JDBC Driver downloaded in the previous step. Locate the postgresql.jar file in the installation directory and click on “OK”.

• If you are prompted about the missing Drivers, click on “Download”. You can then verify the connection by clicking on “Test Connection”.
• If it displays the “Connected” message, click on “Finish”.

• You can now see your Database added to the list.

Query Data

• Now that you’ve successfully connected PostgreSQL DBeaver, you can view and manipulate the Postgres data in a table. Just right-click on the specific table and click on “View Data”. The content of the table will then be displayed in the main workspace.

• You can even query information from the Database tables. You can write a custom SQL Query to start working with your Database. To do so, click on the “SQL” tab and then click on “New SQL script” to write SQL Scripts. Write your query and run it by clicking on the “Execute SQL Statement” button.

That’s it, this is how you can get started with a DBeaver PostgreSQL connection, as simple as that.

Bonus section: Features in DBeaver for PostgreSQL 

Let’s take a look at the features in DBeaver that supports PostgreSQL.

Category	Feature
Data Types	PostgreSQL Arrays
	PostgreSQL Structures
Extensions	PostgreSQL Extensions
Security	PostgreSQL Permissions
	PostgreSQL Policies
	PostgreSQL Roles
Data Organization	PostgreSQL Partitions
Database Management	PostgreSQL Dependencies
	PostgreSQL Tools
External Data	PostgreSQL Foreign Tables

Some additional features:

Category	Feature
Data Transfer	Data Import
	Data Export
Session Management	Session Manager
Lock Management	Lock Manager
Backup and Restore	How to Backup/Restore data
Schema Management	Schema Compare
Data Visualization	GIS Guide
	ERD Guide

In the following sections, let’s see how to use ODBC to connect PostgreSQL data from DBeaver. First, let’s look into the steps to create an ODBC data source.

Create an ODBC Data Source to Use PostgreSQL Data in DBeaver

You need to use the correct connection parameters (like the hostname assigned to your computer, the username, etc.) in the client software to connect to the PostgreSQL server.

You need to alter the default values against the parameters for your needs. This can be done either from the command line or in an option file.

1. Select the Start menu -> Click on Control Panel.
2. Click Administrative Tools, then select ODBC Data Sources.
3. Select the System DSN tab if you want to set up a DSN name for all users of the system and User DSN to configure DSN only for your account.
4. Select the Add button and double-click Devart ODBC Driver for PostgreSQL in the list.
5. Set up the connection parameters.
6. Select the Test Connection to validate the correctness of the configured DSN.

To prevent SQL_WVARCHAR data types displayed incorrectly in DBeaver, set the string data types to Ansi either,

• driver configuration dialog -> Advanced Settings tab
• or directly in the connection string (String Types=Ansi). This returns SQL_CHAR, SQL_VARCHAR and SQL_LONGVARCHAR.

Using ODBC Driver to Connect PostgreSQL Data from DBeaver 

Let’s understand the steps involved.

• Click on New Database Connection in the Database menu.
• Click ODBC and select Next in the Connect to database wizard.

• Type in the formerly configured DSN in the Database/Schema.

• Select Test Connection. A success image wil be displayed if you did it right.

Conclusion

PostgreSQL has been active in development for about 30 years now and is used by companies and individual Developers all over the world. However, it becomes important to use a Database Client to interact with PostgreSQL data effectively. DBeaver is a SQL Client and a Database Administration Tool used by Data Administrators and Developers to access Databases. This DBeaver to PostgreSQL tutorial shows how to establish a DBeaver PostgreSQL connection to browse and query Postgres data in the DBeaver GUI.

Nowadays, more and more companies want to associate themselves with PostgreSQL. To get a complete overview of your business performance, it is important to consolidate data from PostgreSQL and other Data Sources into a Cloud Data Warehouse or a destination of your choice for further Business Analytics. If you are looking for a reliable and error-free way of moving data from PostgreSQL to a destination of your choice, then Hevo is the right choice.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs; check them out!

Share your learning experience with this DBeaver PostgreSQL tutorial in the comments below.

References:

Documentation of DBeaver

PostgreSQL ODBC connection details