Managing databases from the command line does come with a learning curve to get the most out of it.

The command line can sometimes be arduous and the display may not be optimal for what you are doing.

Browsing through databases and tables, checking indexes or user privileges, monitoring, managing, and even coding can get really messy when trying to handle it through the console.

It’s not that you don't need to manage the command line commands (it's for sure a must), but there are some tools that can help you speed up many of the daily DBA tasks.

Let's look at what these tools are about and review some of them.

What is a GUI Tool?

A GUI or Graphical User Interface is a software that simplifies the tasks of the users through graphical icons and visual indicators. The actions are performed by using graphical elements.

Why Should I Use a GUI Tool?

Using a GUI is not a must, but it can be useful. One of the main advantages of the GUIs is that they are, in general, easier to learn than a lot of commands and probably one action on the GUI could generate a few commands to perform the task.

Another advantage could be that the GUI is more friendly than the command line, and in most cases, you don't need any programming or sysadmin knowledge to use it.

But, you should be careful before performing a task from the GUI, because by using the wrong button, you could generate a big issue like deleting a table; and for this reason, do be careful when using this kind of tool.

Top GUI Tools for PostgreSQL

Now, let's see some of the most commons GUI tools for PostgreSQL.

Note that, for the installation examples, we'll test it on Ubuntu 18.04 Bionic.

pgAdmin

pgAdmin is one of the most popular Open Source administration and development platforms for PostgreSQL.

It's designed to meet the needs of both novice and experienced PostgreSQL users alike, providing a powerful graphical interface that simplifies the creation, maintenance and use of database objects.

It's supported on Linux, Mac OS X, and Windows. It supports all PostgreSQL features, from writing simple SQL queries to developing complex databases. It's designed to query an active database, allowing you to stay current with modifications and implementations. pgAdmin 4, the current version, can manage PostgreSQL 9.2 and above.

Features

Graphical query plan display

Grant wizard for rapid updates to ACLs

Procedural language debugger

Auto-vacuum management

Monitoring dashboard

Backup, restore, vacuum and analyze on demand

SQL/shell/batch job scheduling agent

Auto-detection and support for objects discovered at run-time

A live SQL query tool with direct data editing

Support for administrative queries

A syntax-highlighting SQL editor

Redesigned graphical interfaces

Powerful management dialogs and tools for common tasks

Responsive, context-sensitive behavior

Supportive error messages

Helpful hints

Online help and information about using pgAdmin dialogs and tools

Installation

First, we need to import the repository key.

$ sudo apt-get install curl ca-certificates $ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

And create the /etc/apt/sources.list.d/pgdg.list file. The distributions are called codename-pgdg. In our example should be bionic-pgdg.

$ deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

To determine the codename of your distribution you can run the lsb_release -c command.

After this, you need to update the package lists, and install the pgadmin package:

$ sudo apt-get update $ sudo apt-get install pgadmin4

Then, you only need to run the pgadmin4 command:

$ pgadmin4

Configuration

The installation creates a pgAdmin server listening in a specific port. This port changes every time you run the pgadmin4 command. After the program is running, you can manage your database from a web interface accessing by the pgAdmin icon on the taskbar.

To connect to your database, you need to choose the Add New Server option and complete the connection information.

Then, you can manage your database using pgAdmin 4.

The design looks good and it's an intuitive interface. The charts in the main screen could help to detect some issue on your system.

The installation requires adding a repository, so it could require some additional skills.

ClusterControl

ClusterControl supports deployment, management, monitoring and scaling for PostgreSQL.

Each deployed PostgreSQL instance is automatically configured using ClusterControl’s easy to use point-and-click interface.

You can manage backups, run queries, and perform advanced monitoring of all the master and slaves; all with automated failover if something goes wrong.

The automation features inside ClusterControl let you easily setup a PostgreSQL replication environment, where you can add new replication slaves from scratch or use ones that are already configured.

It also allows you to promote masters and rebuild slaves.

There are two versions: Community Edition or Enterprise Edition.

Features

Backup Management

Monitoring and Alerting

Deployment and Scaling

Upgrades and Patching

Security and Compliance

Operational Reporting

Configuration Management

Automatic Recovery and Repair

Performance Management

Automated Performance Advisors

Installation

For the installation, you can use the automatic, manual or offline installation.

In this example, we'll use the automatic installation.

You need to download the following script and run it with root privileges on the ClusterControl server:

$ wget http://www.severalnines.com/downloads/cmon/install-cc $ chmod +x install-cc $ sudo ./install-cc

Then, you must complete the information like passwords or configuration and it's done.

Configuration

After the installation is finished, you should be able to open the ClusterControl UI on the web browser by using the hostname or IP address of your server, for example: http://192.168.100.191/clustercontrol/

Here you can perform several tasks like deploy, import, monitoring, and even more.

After you have your PostgreSQL cluster imported or deployed by ClusterControl, you can manage it from a complete, friendly web interface.

It runs on a server, so you can use it from everywhere. All the software is installed by ClusterControl, so you don't need to do any installation manually.

Adminer

Adminer is a full-featured database management tool written in PHP.

It consists of a single file ready to deploy to the target server.

Adminer is available for MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch, and MongoDB. The current version is 4.7 and it was released in November.

Features

Connect to a database server with username and password

Select an existing database or create a new one

List fields, indexes, foreign keys and triggers of a table

Change name, engine, collation, auto_increment, and comment of table

Alter name, type, collation, comment and default values of columns

Add and drop tables and columns

Create, alter, drop and search by indexes including full-text

Create, alter, drop and link lists by foreign keys

Create, alter, drop and select from views

Create, alter, drop and call stored procedures and functions

Create, alter and drop triggers

List data in tables with search, aggregate, sort and limit results

Insert new records, update and delete the existing ones

Supports all data types, blobs through file transfer

Execute any SQL command from a text field or a file

Export table structure, data, views, routines, databases to SQL or CSV

Print database schema connected by foreign keys

Show processes and kill them

Display users and rights and change them

Display variables with links to documentation

Manage events and table partitions

PostgreSQL Schemas, sequences, user types

Extensive customization options

Installation

It runs in a web server, so first, you need to install Apache2, php, php-pdo and php-pgsql packages.

$ sudo apt install apache2 php php-pdo php-pgsql

We need to download the PHP file from the Adminer web page:

$ wget https://github.com/vrana/adminer/releases/download/v4.7.1/adminer-4.7.1.php

And we need to move the PHP file to our apache document root:

$ sudo mv adminer-4.7.1.php /var/www/html/adminer.php

Then, if you're installing it on your local machine, you need to open the URL http://localhost/adminer.php in your web browser.

Configuration

To start using the tool, you need to login into your database.

After login, you can see the following web page.

The installation is really easy because you only need to put the PHP file in the document root of your web server, but the interface looks a bit old-fashioned.

It's a web application, so you can access it from everywhere only by using a web browser.

SQL Workbench/J

SQL Workbench/J is a free, DBMS-independent, cross-platform SQL query tool.

It is written in Java and should run on any operating system that provides a Java Runtime Environment.

Its main focus is on running SQL scripts and export/import features. Graphical query building or more advanced DBA tasks are not the focus and are not planned.

Features

Edit, insert and delete data directly in the query result

Powerful export command to write text files, XML, HTML or SQL.

All user tables can be exported into a directory with a single command. Export files can be compressed "on-the-fly".

Powerful text, XML and spreadsheet import. A set of files can be imported from a directory with a single command. Foreign key constraints are detected to insert the data in the correct order

Compare two database schemas for differences. The XML output can be transformed into the appropriate SQL ALTER statements using XSLT

Compare the data of two databases and generate the necessary SQL statements to migrate one to the other.

Supports running SQL scripts in batch mode

Supports running in console mode

Search text in procedure, view and other sources using a SQL command or a GUI

Search for data across all columns in all tables using a SQL command or a GUI

Reformatting of SQL Statements

Select rows from related tables according to their foreign key definitions

Tooltips for INSERT statements to show the corresponding value or column

Copy data directly between to database servers using a SQL command or a GUI

Macros for frequently used SQL statements

Variable substitution in SQL statements including smart prompting for values

Auto completion for tables and columns in SQL statements

Display database objects and their definitions

Display table source

Display view, procedure, and trigger source code

Display foreign key constraints between tables

Full support for BLOB data in query results, SQL statements, export, and import.

Installation

It's written on Java, so you need this software to run it.

First, you must check if you have Java installed on your system:

$ java --version

Then, you need to download the SQL Workbench package:

$ wget https://www.sql-workbench.eu/Workbench-Build124.zip $ unzip -d sqlworkbench Workbench-Build124.zip

To run it, you must execute the jar file named sqlworkbench.jar using the java command with the jar flag:

$ java -jar sqlworkbench/sqlworkbench.jar

Configuration

To connect to your PostgreSQL database, you need to download the JDBC Driver:

$ wget https://jdbc.postgresql.org/download/postgresql-42.2.5.jar $ mv postgresql-42.2.5.jar sqlworkbench/

And configure the driver in your SQL Workbench. For this, go to File -> Manage drivers -> Select PostgreSQL and select the driver.

Then, go to File -> Connect window, and complete the Connection Profile information.

After the connection is finished, you can manage your database using it.

The installation is easy but you need to download the driver and configure it manually. Also, the interface is not too friendly.

DBeaver

DBeaver is free and open source universal database tool for developers and database administrators.

Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.

Usability is the main goal of this project, program UI is carefully designed and implemented. It is based on an opensource framework and allows writing of various extensions (plugins). It supports any database having a JDBC driver. There are two versions: Community Edition and Enterprise Edition.

Features

Connection manager

Metadata browser

SQL Editor

Data viewer/editor

Data/metadata search

Database structure compare

Data transfer (export/import)

ER Diagrams

Query Manager

Projects

Extra views

Driver manager

Supported relational databases

Supported NoSQL databases

Supported OSes

PostgreSQL Execution plan explain Stored procedures source Views DDL Sequences



Installation

First, you must download the package and install it:

$ wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb $ dpkg -i dbeaver-ce_latest_amd64.deb

And then, just run the following command to open the application:

$ dbeaver

Configuration

When you run the application for the first time, you need to configure your database connection.

So, you need to select PostgreSQL and complete the information.

Then, by selecting Test Connection, you must download the driver files. You should receive the following message after the testing.

When you finish the configuration, you can manage your database by using the DBeaver application.

The installation is, basically, a piece of cake, and the interface looks friendly and intuitive.

Navicat

Navicat for PostgreSQL is an easy-to-use graphical tool for PostgreSQL database development.

This tool will fit all, from beginners to seniors, and fit all tasks from simple queries to development. Connect to local/remote PostgreSQL servers and compatible with cloud databases like Amazon Redshift, Amazon Aurora, Amazon RDS, Google Cloud, Microsoft Azure, Alibaba Cloud, Tencent Cloud and Huawei Cloud, and all PostgreSQL database objects. It's a paid application but you can use the trial version to test it.

Features

Supports PostgreSQL 7.3 or later and Cloud services like AWS, Google Cloud or Microsoft Azure among others.

Secure connection: SSH/HTTP/SSL

Navicat Cloud

Data Viewer and Editor

SQL Processing

Data Modeling

Import/Export

Data Manipulation

Backup/Restore

Automation

Manage user

Server Monitor

Installation

First, we must download the Navicat package and uncompress it.

$ wget http://download3.navicat.com/download/navicat121_pgsql_en_x64.tar.gz $ tar zxvf navicat121_pgsql_en_x64.tar.gz

Then, we need to run the start_navicat script to start it.

$ cd navicat121_pgsql_en_x64 $ ./start_navicat

This will use Wine to run the Navicat application and it could ask you to install some required dependency during the initialization.

Configuration

When you access the application, you need to create a new connection.

Go to Connection -> PostgreSQL and complete the information.

After this, you can start to use the application to manage your database.

The software runs over Wine on Linux and the trial is for 14 days. The interface looks pretty and friendly.

Conclusion

In this blog, we reviewed some of the most commons GUI tools for PostgreSQL.

Regardless of the fact that using a GUI tool is not mandatory, it can help you ease some of the daily DBA tasks by providing you with a more friendly way of managing things.

These tools aren't a replacement for the command line (as a DBA you need to master it), but they are extremely helpful and you will really benefit from them.