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

An Overview of PgAdmin Tool for Postgres

CodingSight - An overview of PgAdmin tool for Postgres

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

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 can download the PgAdmin4 tool from the URL. 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.

Navigate to the URL and 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. 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 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

Database Dashboard

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

database dashboard in postgres

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

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 Postgres.

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

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

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.

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 Postgres 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

Graphical 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 navigate to the URL and launch the PgAdmin Sandbox for experimenting PgAdmin 4 and PostgreSQL without installing them locally.

(Visited 9 times, 1 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close