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
- Cross-platform support: The PgAdmin tool is available on the cross-platform operating system as below:
- Microsoft Windows
- macOS
- Linux
- 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
- 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
- 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
- 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.
Suppose you require PgAdmin4 on a Windows server. Therefore, click the Windows icon and select the required version for downloading.
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.
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:
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.
On the password page, enter the admin password of your Postgres instance.
You can review the configurations on the Pre-Installation Summary page.
PgAdmin Features Overview
Once the setup is done, launch the PgAdmin 4 from the Start menu.
You get the following screen once you click the PgAdmin 4 icon in Windows:
It uses a master password for securing the credentials in the PgAdmin tool.
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.
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.
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
PostgreSQL Database Dashboard
If you click a database (here Postgres), it gives a database dashboard for a specific database.
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.
Similarly, if you click a table name, it gives 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.
Similarly, I get the script for an existing table in my database.
Database Statistics
In the Statistics tab, you can view database stats, object stats in the console without querying the internal tables.
Database Statistics
Table Statistics
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.
Similarly, the dependent column gives information such as foreign key dependency.
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.
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.
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
Specify a name for your server group and click Save.
To add a server, right-click Server Group and fill in the general information such as server name, background, foreground color, comments.
Enter the connection details such as hostname or IP address, the port number in the connection tab.
You can also add a foreground or background color for your servers in the Server Group. Here, we choose a color for the background.
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.
You can also view the query history in the PgAdmin. Click the Query History tab and view queries, their runtime, number of rows affected.
Backup, Restore Windows
Once you right-click the database, you get various options in the database, as shown below:
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:
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.
Enter the required column names, data types, constraints, parameters, and security in the Table Wizard.
You can get the SQL statement for your actions in the SQL tab, as shown below.
Conclusion
While PgAdmin is an excellent tool for Postgres developers and database administrators, offering a graphical interface and query execution capabilities, there’s an alternative to pgAdmin worth considering. dbForge Studio for PostgreSQL is a comprehensive Postgres GUI tool that not only provides similar functionalities but also offers advanced features like code completion, database comparison and synchronization, data editing, and robust data analysis and reporting tools. This makes dbForge Studio a powerful and versatile solution for managing PostgreSQL databases.
