Data manipulation is one of the key areas of a database administrator. There are various database management tools that enable a database administrator to effectively interact with the data in a database. It is a good practice to use a database client instead of directly interacting with the database server.

DBeaver is a database management tools that supports many databases. It is a free and multi-platform database tool, supporting all databases with JDBC while the EE version also includes support for non-JDBC databases. Basically, DBeaver can be used to access/ manage any database or a cloud application having OBDC or JDBC drivers. The tool is beneficial for Database Administrators, developers, SQL programmers and analysts. Some of the things that one is able to do with DBeaver include:

  • SQL queries and execution
  • Manipulating data as if one is using a regular spreadsheet
  • Metadata browsing and editing
  • Metadata browsing and editing
  • Creating analytical reports basing on records from varying data storages
  • Test data generation
  • Exporting information in the right formats
  • Dbeaver is a powerful SQL editor with a lot of administration features, ability to perform data and schema migration, monitoring connection sessions and many more.

The main goal for DBeaver is usability and it offers the following:

  • A lot of powerful features
  • Carefully designed User Interface
  • Support for Enterprise security standard
  • Supports cloud datasources
  • Can be integrated with other products such as Git, excell etc
  • Multi-platform support

For my installation, I am running Fedora 34. We are therefore going to see how to install Dbeaver CE on Fedora and how to use DBeaver to manage Postgresql.

Step 1: Install Dbeaver CE on your Linux System

DBeaver requires Java to run and Open JDK 11 is already included in all versions. You can always change the default JDK version by replacing directory jre in dbeaver installation folder.

Install Dbeaver on Fedora / CentOS Linux

Run the following commands to download Dbeaver CE on Fedora / CentOS Linux:

sudo yum -y install wget
wget https://dbeaver.io/files/dbeaver-ce-latest-stable.x86_64.rpm

Once downloaded, install the package with rpm/dnf or yum package manager as below:

sudo rpm -Uvh ./dbeaver-ce-latest-stable.x86_64.rpm 

Install Dbeaver on Debian / Ubuntu Linux

For Ubuntu / Debian Linux operating system perform installation of Dbeaver by running the commands below:

wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb
sudo apt install ./dbeaver-ce_latest_amd64.deb

Step 2: Install PostgreSQL Database Server

Next, we need to install Postgresql database server. Follow the steps below:

Install PostgreSQL Database Server on Fedora / CentOS

For Fedora / CentOS Linux, PostgresSQL module is already available. We need to enable the module and install it using the below commands:

# CentOS 8 / CentOS Stream 8 / Fedora Linux
sudo dnf module reset postgresql -y
sudo dnf module enable postgresql:13 -y
dnf install postgresql-server postgresql -y

For CentOS 7 check: How To Install PostgreSQL on CentOS 7

Next, initialize postgresql database, then start and enable postgresql database service.

sudo /usr/bin/postgresql-setup --initdb
sudo systemctl enable --now postgresql
sudo systemctl start postgresql

Confirm that PostgreSQL database service is running:

$ systemctl status postgresql
● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: active (running) since Wed 2021-06-16 12:33:58 EAT; 10h ago
    Process: 710 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
   Main PID: 725 (postmaster)
      Tasks: 8 (limit: 4667)
     Memory: 21.0M

Install PostgreSQL on Ubuntu / Debian

Update system package index and install PostgreSQL packages on Ubuntu / Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib

PostgreSQL creates a default user called postgres. Change to the user and access postgreSQL console as below:

sudo su - postgres

To access postgresql console

$ psql

Check databases

# \l
  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Create Database and Database Users in PostgreSQL

We are going ahead to create a database and an associated user as below:

CREATE DATABASE test;
CREATE USER lorna;
ALTER USER lorna WITH ENCRYPTED PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE test TO lorna;

Step 3: Managing PostgreSQL Database with DBeaver

Once you have prepared your database, it is time to launch your Dbeaver. You can either launch from the command line or from the desktop applications. To launch from the terminal, enter the below command:

$ dbeaver

On your desktop, search for dbeaver and double-click to launch

Once launched, it will prompt you to select the database to you. In our case, we are using postgreSQL

Click next. If you are prompted about missing drivers, click on download. In the next window, fill in the database specifications. You can verify connectivity by clicking on ‘Test Connection’

Then click Finish and you should see your database added.

To start working on your database through SQL Scripts, click on SQL then New SQL Script to get a window for writing sql scripts.

For my database called ‘test’, I have two tables as shown:

If you want to add a database, right click on your existing database, for my case, it is ‘test’ and you should see an option to create a new database and other options as shown in the image below:

To view or manipulate the tables in any way, right click on the specific table and you should see the different options you can do with the table.

DBeaver is a free and powerful tool for managing various databases. It has many features to enable you to manipulate your tables and effectively manage your databases. This tutorial was meant to bring you upto speed on how to install DBeaver and how you can use it with your databases. In our case here, we have looked at how to manage PostgreSQL databases with DBeaver. I hope the guide has been helpful.

Best Udemy courses to Learn PostgreSQL Database:

Check more guides that you may like:

LEAVE A REPLY

Please enter your comment!
Please enter your name here