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.

Simplify PostgreSQL Data Analysis with Hevo’s No-code Data Pipeline

Looking for easy ways to replicate your PostgreSQL data? Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up data integration from PostgreSQL and 150+ Data Sources (including 40+ Free Data Sources) and will let you directly load data to a Data Warehouse or the destination of your choice. Its fault-tolerant architecture makes sure that your data is secure and consistent.

Sign up here for a 14-day free trial!

Install DBeaver Community Edition

  • You can download the relevant DBeaver Community Edition based on your Operating System from its official website.
DBeaver PostgreSQL: Download
DBeaver Community Editions
  • 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”.
DBeaver PostgreSQL: Setup
DBeaver Community Setup
  • Accept the License Agreement and select the users for whom you want to install DBeaver. Then, click on “Next”.
DBeaver PostgreSQL: Install
License Agreement in BHeaver
  • 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”.
DBeaver PostgreSQL: DBeaver Components
Choose Features of DBeaver Community
  • 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.
DBeaver PostgreSQL: JDBC
PostgreSQL JDBC Driver
  • 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”.
DBeaver PostgreSQL: New Database Connection
Create a Connection to PostgreSQL Data
  • It will now prompt you to select the Database you want to connect to. Select PostgreSQL and click on “Next”.
DBeaver PostgreSQL: Connect to a Database
Select Database You Want to Connect
  • 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.
DBeaver PostgreSQL: Connection Settings
PostgreSQL Connection Settings
  • 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”.
DBeaver PostgreSQL: Add JDBC Driver
Edit Driver PostgreSQL
  • 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”.
DBeaver PostgreSQL: Connected
Connection Test
  • 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.
DBeaver PostgreSQL: View Data
Query Data
  • 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.
DBeaver PostgreSQL: SQL
Run Your Query

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.

CategoryFeature
Data TypesPostgreSQL Arrays
PostgreSQL Structures
ExtensionsPostgreSQL Extensions
SecurityPostgreSQL Permissions
PostgreSQL Policies
PostgreSQL Roles
Data OrganizationPostgreSQL Partitions
Database ManagementPostgreSQL Dependencies
PostgreSQL Tools
External DataPostgreSQL Foreign Tables

Some additional features:

CategoryFeature
Data TransferData Import
Data Export
Session ManagementSession Manager
Lock ManagementLock Manager
Backup and RestoreHow to Backup/Restore data
Schema ManagementSchema Compare
Data VisualizationGIS 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.
PostgreSQL DBeaver: create ODBC source

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.
postgresql DBeaver: new database connection
  • Type in the formerly configured DSN in the Database/Schema.
postgresql DBeaver: configuration
  • Select Test Connection. A success image wil be displayed if you did it right.
postgresql DBeaver: test connection

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

Raj Verma
Business Analyst, Hevo Data

Raj is a skilled data analyst with a strong passion for data analysis and architecture, having a flair for writing technical content as well. With extensive experience in handling marketing data, Raj has adeptly navigated abstract business problems to derive actionable insights that drive significant results.

No-code Data Pipeline For Your Data Warehouse

Get Started with Hevo