Top GUI Tools for PostgreSQL

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 https://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.