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
# \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:
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:
- SQL and PostgreSQL: The Complete Developer’s Guide
- The Complete Python/PostgreSQL Course 2.0
- SQL & PostgreSQL for Beginners: Become an SQL Expert
- Learn SQL Using PostgreSQL: From Zero to Hero
- PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
Check more guides that you may like: