In this article, we are going to learn about PGAdmin, a PostgreSQL management tool. As you are aware SQL Server Management Studio (SSMS) and the MySQL Workbench are the GUI management tools for SQL Server and MySQL respectively. Similarly, in order to manage the Postgres database and its services, PGAdmin is used. PGAdmin is a web-based GUI tool used to interact with the Postgres database sessions, both locally and remote servers as well. You can use PGAdmin to perform any sort of database administration required for a Postgres database.
How to download and install the PostgreSQL management tool – PGAdmin
Let us now try to understand how to get PGAdmin. In order to install PGAdmin, we need to have a Postgres database installed as a prerequisite to proceed. It doesn’t matter if the Postgres database is installed locally or on a remote server. This is needed to test the connection from the PostgreSQL management tool to the Postgres database. If there is no database, then you cannot connect to any database after installing the PGAdmin tool.
Navigate to https://pgadmin.org and click on the Download section.
Figure 1 – Installing the postgreSQL management tool – PGAdmin
Under the Download section, you can see a list of different options for downloading the PGAdmin to your machine.
Figure 2 – Downloading the postgreSQL management tool – PGAdmin
As you can see in the figure above, there are various ways to install PGAdmin. A brief understanding of all the different options is mentioned below.
- Container – PGAdmin can be installed on a docker container and can be connected to the container. The docker container will expose the endpoints for using the PGAdmin server on a browser. You should use this option if you want your PGAdmin to run independently of the operating system
- macOS – If you are developing and managing a postgres database on a Mac, then you should choose this method to install PGAdmin on your machine. It will create a standalone tool that will run PGAdmin on a browser
- Python – PGAdmin is also available to be installed a python package from the PyPi library. You can view more details about the python package by visiting the official URL
- APT – The APT can be used to install the PostgreSQL management tool on a Ubuntu or Debian 9 instance. You can get more information about installing PGAdmin on a Ubuntu or Debian flavor from the official website
- RPM – Like the APT versions, the RPM versions target the Linux flavors for RHEL and Fedora. You can see more information on installing PGAdmin on RHEL or Fedora from the official links as follows
- Source Code – In case you are associated with some open source development or you want to modify some existing code with the PGAdmin, then you can download the raw source code from the mirrored links. Since installing PGAdmin from a raw source code requires enough technical knowledge, therefore, it is advised to get started by installing any one of the binary packages instead of the source code
- Windows – This allows PGAdmin to be downloaded and installed for the Windows user base. Installing PGAdmin on a windows machine is much more intuitive and easier as you can simply follow the steps and get it installed on your machine
Installing PGAdmin on the local machine
Now that we have some idea regarding what are the different ways to install PGAdmin, let us go ahead and try our hands-on directly. In this tutorial, I am going to follow the download and installation steps on a macOS, however, most of the steps are almost similar for other operating systems as well.
Head over to https://www.pgadmin.org/download/pgadmin-4-macos/ and select the latest download option available. Download the pgadmin4-4.30.dmg file to your computer.
Figure 3 – Downloading the latest version of PGAdmin on macOS
Follow the installation steps one by one and this will get PGAdmin installed on your system. Once it is installed, you can start it up. PGAdmin runs as a web application on your browser using a local port. You should automatically be redirected to the PGAdmin window once it starts.
Connecting to the local PostgreSQL server
Now that we have our PGAdmin running on the browser, we are good to start by setting up our connections to the server database. Right-click on Server and select Create, Server from the context menu. Provide a name to the server and then navigate to the Connection tab. In this Connection tab, provide all the details required to connect to the database server. In my case, I am going to connect to the Postgres running on my local, however, you can also connect to a database that runs on a remote server or on the cloud.
You need to provide the following details in order to connect to a Postgres database server.
- Hostname – The name of the host machine or the IP address
- Port – By default, Postgres databases run on port 5432 if not changed
- Maintenance database – This database is created by default and you should not remove it. This can be used as the default and the maintenance database while connecting
- Username – The username to connect to the database server
- Password – The password to connect to the database server
Click Save once all the above details are provided.
Figure 4 – Connecting to Postgres database using PGAdmin
An important point to take into consideration here is that your database must be running locally otherwise you won’t be able to connect to the server. Once connected, you can see the list of available databases within your database.
Figure 5 – Available database list in PGAdmin
Once you find your desired database, you can expand the nodes to view the database objects like tables, views, stored procedures, functions, etc. All the database objects are found within the specified schema nodes as follows.
Figure 6 – Browsing tables in the database using PGAdmin
You can right-click on a table, select View/Edit Data and then select the First 100 rows. This will fetch the first 100 rows from the table into the query window as follows.
Figure 7 – Querying the database tables
Alternatively, you can also select the table, click on Tools on the Menu bar and then select Query Tool from the options. This will open up the Query Editor window using which you can write your own queries and play around the database tables.
Figure 8 – Using the Query Editor Tool in PGAdmin
Another helpful feature of this management tools is that it provides monitoring facilities to view the real-time status of the database servers. This gives an overview of the number of database sessions that are open, the number of transactions performed every second, any blocking transactions in the current session along with the active users that are currently logged into the PGAdmin service. This is helpful from a DBA perspective as it provides this information directly out of the box without having to query the database server manually.
Conclusion
In this article, we have understood the details about the PostgreSQL management tool, PGAdmin. We have also understood how to install PGAdmin on a local machine and then connect to the database service. We have also looked at the most important features that are required to interact with a Postgres database. Finally, we have seen how to use the Query Tools window to run and execute SQL commands against the Postgres database. In addition to PGAdmin, there are other couples of GUI based tools for managing Postgres like DBeaver, Navicat, Datagrip, OmniDB, etc. You can also evaluate these GUI based tools against a postgres instance and manage it.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021