Written by 09:57 Code Management, Languages & Coding, Tools & technologies

PgAdmin GUI client for PostgreSQL: Comprehensive Overview

The PgAdmin is a popular tool for the open-source database – Postgres or PostgreSQL. It provides various graphical user interfaces for managing the Postgres relational database. It simplifies database management activities such as database creation, maintenance, database objects export\import, query tool, backup\restores, managing cluster objects.

PgAdmin Features

  1. Cross-platform support: The PgAdmin tool is available on the cross-platform operating system as below:
    • Microsoft Windows
    • macOS
    • Linux
  2. Development models: It supports two development models suitable for both developers and administrators.
    • Desktop runtime mode for individual users
    • Server mode for multiple users with a web client
  3. Graphical features
    • Query writing with color coding and syntax highlighting
    • Graphical query plan
    • Data grid to view query results
    • It supports PL/pgSQL and EDB-SPL as a procedural language debugger
    • In-built schema diff tool
    • ERD tool for documenting and designing database objects
    • Create, view and edit all common PostgreSQL objects
  4. Monitoring and Maintenance
    • Database monitoring dashboard
    • Ability to run the backup, restore, analyze and vacuum process
    • SQL scheduling agent
    • Auto-vacuum management
    • Highlighted long-running queries on the dashboards
  5. PgAdmin supports PostgreSQL server-side encodings such as SQL_ASCI, UNICODE/UTF-8, Latin.

How to Download PgAdmin Tool

The current PgAdmin version is PgAdmin4 v5.5, and it was released on 15th July 2021. The PgAdmin tool is available in both desktop and server modes.

You need to download the PgAdmin4 tool. Select the platform of your choice, such as Container, macOS, Windows.

Platform to download PgAdmin Tool

Suppose you require PgAdmin4 on a Windows server. Therefore, click the Windows icon and select the required version for downloading.

PgAdmin on Windows Server

Alternatively, you require a Postgres database for connecting using the PgAdmin. Therefore, we will install Postgres and PgAdmin together in this article.

Choose your required PostgreSQL version in the supported operating system.

PostgreSQL Database Download

For this article, I’ve chosen version 13.3 on the Windows platform.

How to Install PgAdmin Tool on Windows

Download the installer and launch it. You’ll see the following page:

PostgreSql Setup

We will cover several installation steps as other options are self-explanatory.

In the select components page, verify that you have PostgreSQL Server, and PgAdmin 4 is selected.

Setup Components

On the password page, enter the admin password of your Postgres instance.

password for  Postgres instance

You can review the configurations on the Pre-Installation Summary page.

pre installation summary

PgAdmin Features Overview

Once the setup is done, launch the PgAdmin 4 from the Start menu.

PgAdmin Overview

You get the following screen once you click the PgAdmin 4 icon in Windows:

Management tools for PostgreSQL

It uses a master password for securing the credentials in the PgAdmin tool.

Master password for Postgre

Click OK, and you can see your locally installed PostgreSQL. To connect to this server, you need to enter the admin credentials we specified while setting up the Postgres instance.

locally installed postgreSQL

It connects to the local instance, and you can see a database named Postgres. The database contains the subfolders for catalogs, schemas, extensions, event triggers.

Postgres Database

Server Dashboard

If you click the database folder, it gives you a server dashboard. It gives the following information:

  • Server sessions
  • Transactions per second
  • Tuples in and out
  • Block IO
  • Server activity
server dashboard in postgres

PostgreSQL Database Dashboard

If you click a database (here Postgres), it gives a database dashboard for a specific database.

database dashboard in postgres

Database Properties

In the properties tab, it gives details of the object selected in the PgAdmin tool. For example, currently, it is showing details of the Postgres database.

properties in postgres

Similarly, if you click a table name, it gives table properties.

table properties

Get SQL Script for PostgeSQL

It is pretty easy to generate SQL Script for the database or its objects using PgAdmin. Select the object in the left-hand tree and click the SQL tab.

For example, it gives the following SQL Script for database PostgreSQL.

SQL script for database Postgres

Similarly, I get the script for an existing table in my database.

script for an existing table in postgres database

Database Statistics

In the Statistics tab, you can view database stats, object stats in the console without querying the internal tables.

Database Statistics

database statistics in postgres

Table Statistics

table statistics in postgres

Dependencies

The Dependencies tab gives the details of dependency of any object such as schema, table, view. For example, we can see that the table is dependent on the schema in the below screenshot.

endencies in postgres
dep

Similarly, the dependent column gives information such as foreign key dependency.

foreign key dependencies in postgres

GUI Query Panel

You can run your queries in the PgAdmin tool for the Postgres database. To launch the query tool, right-click the database and select Query Tool.

query tool in postgres

You can write your query in the query editor and execute it. Alternatively, you can navigate to Tools -> Query Tool. It gives the output in the Data Output tab, as shown below. If you need to learn more about query optimization navigate to this article.

query editor in postgres

Create Server Groups

You might need to work on various database environments such as production, development, QA. You also need to manage servers application-wise. Therefore, you can add a server group in PgAdmin and add servers belonging to the group.

To add a server group, right-click the Servers tab and select Server Group

create server groups in postgres

Specify a name for your server group and click Save.

name for server group

To add a server, right-click Server Group and fill in the general information such as server name, background, foreground color, comments.

add a server to server group

Enter the connection details such as hostname or IP address, the port number in the connection tab.

connection details in postgres

You can also add a foreground or background color for your servers in the Server Group. Here, we choose a color for the background.

background color for server group

It is how the PostgreSQL server looks in the PgAdmin console. It helps to identify a server using the background or foreground color. For example, to avoid accidental query execution, you can add the connection color to visualize the production server.

training environment in postgres

You can also view the query history in the PgAdmin. Click the Query History tab and view queries, their runtime, number of rows affected.

query history in postgres

Backup, Restore Windows

Once you right-click the database, you get various options in the database, as shown below:

Backuo, restore windows

It includes a Wizard for Maintenance, Backup, Restore, Grant Wizard, Search Objects, CREATE script, Generated ERD.

If you click Generate ERD, it gives a database diagram:

database diagram

GUI Wizard for Object Creation

PgAdmin tools give a graphical Wizard for creating tables, schemas, views, stored procedures, functions, types, triggers. You can right-click the respective folder under the database and choose the action.

For example, to create a new database table, right-click the Tables folder and select Create Table.

graphical wizard for object creation

Enter the required column names, data types, constraints, parameters, and security in the Table Wizard.

create table wizard

You can get the SQL statement for your actions in the SQL tab, as shown below.

get SQL Statement for your action

Conclusion

The PgAdmin is an excellent tool for both Postgres developers and database administrators. You can use it for the graphical interface and executing queries. You can launch the PgAdmin Sandbox for experimenting PgAdmin 4 and PostgreSQL without installing them locally.

Tags: , Last modified: October 27, 2022
Close