In this article, we will learn how to connect the pSQL tool with Azure Database for PostgreSQL.
Introduction
Databases come in a variety of formats and with it comes a variety of tools that can be used along with it. By default, generally, every database vendor provides an IDE (Integrated Development Environment), or a set of management tools suite which can be used to perform a variety of tasks related to management, administration, database development, etc. These tools may be commercial in nature and may involve license costs. Another category of tools generally belongs to open source or community-based tools which are developed by the developer community and are available generally for free. All these tools can be generally categorized into two different categories from a usability perspective – GUI-based tools and terminal-based tools. The GUI-based tools have some form of graphical user interface using which the end-user interacts with the tool and these actions on the UI are then converted by the tools into specific database-related actions. These types of tools are usually used equally by administrators as well as developers. Unlike GUI-based tools, terminal-based tools rely heavily on the command line interface, and tasks are carried out directly by using the code.
PostgreSQL is one of the leading open-source relational databases and it has a very rich community-based tools ecosystem. Microsoft Azure cloud has Azure Database for PostgreSQL as its offering of PostgreSQL on Azure cloud. While it comes with a lot of features for configuring the server instance directly from the web console of the Azure portal, it does not provide any stand-alone or desktop-based tools to work with the instance of Azure Database for PostgreSQL. One of the reasons for the same can be since tools like pgAdmin is a very feature-rich community developed administration and database development console, that is usually used by the PostgreSQL community, is already available for free. pgAdmin can be considered as an equivalent of what SQL Server Management Studio (SSMS) is for SQL Server database. There are several terminal-based tools too that are available for use with PostgreSQL. One such terminal-based tool is called pSQL which can be used with Database for PostgreSQL as well.
Creating an instance of Azure Database for PostgreSQL
It is assumed that one already has an Azure account and administrative access to create an instance of Database for PostgreSQL instance. To test the pSQL tool with an Azure Database for PostgreSQL instance, first, we need to create an instance, and that’s what we are going to do now. Navigate to the Azure Database for PostgreSQL service and click on the New button. This would invoke a new wizard to select the options and configuration to create a new instance. The first setting to choose is the deployment option i.e., the edition of the Azure Database for PostgreSQL instance that we intend to create. For our use-case, while all the options will work, we can select the simplest and the cheapest one, as we intend just to test the connectivity of the psql tool with this instance without incurring any heavy cost. So, we will select the Single Server option and click on the Create button.
In the next step, we need to select the subscription and the resource group in which the instance would be created.
The next section is the server details section, in which we need to provide an appropriate instance name, location in which the instance would be deployed, the version of PostgreSQL that we intend to deploy, and the compute as well as storage capacity. To use the psql tool with PostgreSQL, these options won’t have any impact on our use case. So, here we can proceed with the default selection.
The next section is the Administrator account section in which we need to provide the credentials using which an administrator would connect with the account. These credentials will be used when we will use psql to connect with the instance being created. So, provide appropriate credentials and take a note of them to keep them handy.
The rest of the options can be configured with their default settings as they are not going to have an impact on the use case in question. Complete the rest of the steps in the wizard and create a new instance of Azure Database for PostgreSQL. Once the instance is created, the first part of this exercise is done.
Working with pgAdmin and psql
Now that the instance is in place, the next step is to install the pgAdmin tool. One may wonder that why are we not installing the psql tool and instead of installing the pgAdmin tool. The reason for the same is that when we install the pgAdmin tool, psql too gets installed with it. And pgAdmin is a management suite of tools which usually is the first tool that developers and administrators use alike to work with PostgreSQL. So instead of installing the psql tool separately, it’s advisable to install it as a part of pgAdmin. To install pgAdmin, one can navigate to the product site as shown below, download the release of the product depending on the environment on which the tool will be installed, and then install and set up the tool.
Once pgAdmin is installed, it would look as shown below. When you access the tool for the first time, it would provide a prompt to set up a master password optionally. Once set, it would then ask for this master password each time pgAdmin is opened as shown below. Configure the same as desired and navigate to the home screen of pgAdmin.
Before we start using pgAdmin and its set of tools with the Azure Database for PostgreSQL instance, we need to first register this instance. Click on the Server Group… in the browser pane on the left side and click the option to create a new server as shown below.
This would open a connection wizard as shown below. Provide a name for the server, the hostname would be the name of the endpoint of the instance, provide the administrator credentials, and set the SSL mode to “Require” from the SSL tab and click on the Create button. Once done, if the pgAdmin tool can connect successfully with the instance, the instance would get registered. One also needs to add the machine IP on which pgAdmin is installed to the connection security settings of the Azure Database for PostgreSQL instance so that the firewall would allow incoming connection requests from pgAdmin.
Now as pgAdmin can connect with the instance, this would mean that the connectivity is successfully established. But we need to use the psql tool which is a separate tool. We can invoke and access this tool from pgAdmin, though it would use its own protocol and connection to connect to the instance. To invoke the psql tool, select the database from the server in the browser pane and then click on the last icon in the toolbar as shown below, which invokes the psql tool.
Once the tool window opens, it would use the same connection credentials that are used by the pgAdmin tool and attempt to connect to the selected database. Once the connectivity is successful, it would show the prompt name as the name of the database as shown below.
To test the connectivity and just to get a glimpse of how powerful a terminal-based tool can be, just type a command – “\l” and press enter. It will show a list of all the databases in the instance with details as shown below. These commands can be used to develop powerful yet concise-sized scripts that often administrators tend to use on the database to perform a variety of tasks.
In this way, we can use the psql tool from pgAdmin to connect to Azure Database for PostgreSQL instance to perform a variety of tasks.
Conclusion
In this article, we created an instance of Azure Database for PostgreSQL, installed pgAdmin utility which ships with the psql utility as well, and used psql to connect with the instance.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023