In this article, we will take a basic walkthrough of the psql environment and learn the commands to get acquainted with this tool.
Introduction
Postgres is one of the leading open-source databases in the database ecosystem, and pgAdmin is one of the most popular tools that is used to operate on PostgreSQL. Azure offers Azure Database for PostgreSQL as its offering for creating instances of PostgreSQL on Azure cloud. We learned how to create an instance of Azure Database for PostgreSQL, install, and configure pgAdmin and finally invoke the tool from pgAdmin. We also connected to the Azure Database for PostgreSQL instance from the psql in the last article. Once connected, the next step is to get introduced to the basic commands, syntax, and conventions used to work with psql to get started with this tool.
Basics of psql commands and environment
In this following exercise, we will be continuing from the point where we left in the last article, Using psql tool with Azure Database for PostgreSQL, where we created a single-server deployment of Azure Database for Postgresql, installed pgAdmin, registered the instance with pgAdmin, configured the firewall settings of the instance to allow incoming connections from pgAdmin, and finally invoked psql from pgAdmin which automatically connects to the instance using the same connection credentials with which pgAdmin is connected to the instance. It is assumed that this setup is already in place, pgAdmin is already open and connected to the instance.
By default, when an instance of Azure Database for PostgreSQL is created, it comes with a built-in database named Postgres. To try outcome connection-related commands in the tool, we would need another database. So, let’s create a new database first before jumping into the psql environment. Right-click on the databases icon in the browser pane and select the menu option to create a new database. This would invoke a new wizard as shown below. The first step in the database creation process is to provide basic details like the name of the database, database owner which is by default the user who is connected to pgadmin and invoking this wizard, and optionally any comments that describe more information about the database.
There are more options available to configure the database which can be accessed from different tabs like definition, security, parameters, and the advanced tab. All these deal with different aspects of the database configuration. For now, as we are focused on psql, we will continue with the default options. To view the entire configuration briefly, one can just click on the SQL tab and check the SQL that gets generated from the configuration settings in all these tabs. The SQL tab would look as shown below.
We can click on the Save button and the database would get created. Refresh the browser pane and then we would be able to find the new database listed here. To invoke the tool and connect to this database directly, click on the database in the browser pane and then click on the psql icon to invoke it. Once done, we should be able to see the psql in the right pane with a connection to the selected database and the prompt name would be the name of the selected database as shown below.
The first command that one should learn to operate in any terminal-based tool is the command to find help regarding the command reference itself, the syntax of any specific commands, and help regarding the details of the environment. The command for the same in psql is “\?”. All the commands generally start with a backslash “\”. When we type the command for help, it would display a long list of all the reference commands, an excerpt of which is shown below.
All terminal-based tools have different options available to configure the operating environment of the tool as well as it uses system variables that hold configuration or paths of different artifacts like libraries or system folders. As seen in the commands listed in the help section, here also we have the option to view these details. First, we will explore the command to look at the different options available for configuration in psql. Type the command “\? options” in the console and the result would look as shown below. It will show a long list of options that can be used in the commands. For example, if we intend to execute the commands or script in quiet mode, we can use the -q option as shown below.
Continue to click enter to view the rest of the options as shown below. The bottom section of the results shows the details related to connection options. These options are already used by the tool while making a connection to the Azure Database for PostgreSQL instance when the tool is invoked. Here the hostname is the endpoint name of the instance, the default port for PostgreSQL is 5432, and the rest of the options are for connection credentials.
Every console or terminal-based tool often makes use of the system variables in the scripts. For that, we need to know what all the variables are available at our disposal. To find these specially treated variables, type the command “\? variables” and the output would look as shown below. We can use the set command to change the value of these variables as desired, but this should be done very carefully as changing the value of these system variables can have a global impact on all the scripts that may be using these variables.
The present connection is on the Test database as shown below. Let’s say that we intend to connect to change the connection to another database – Postgres. In that case, we can use the “\c” command followed by the name of the database as shown below. This will change the connection from the present database to the new database as well as change the prompt name as well as shown below.
A connection is typically formed of a few variables like the hostname, username, and other additional information like whether the connection uses SSL and other such details. While we know the name of the database to which we are having a connection by looking at the prompt name generally, but it is not self-evident what are the other details of the connection. We can use the command “\conninfo” to learn about the details of the existing connection as shown below.
When working with a database, an administrator typically needs to switch or assign roles to test the grants or revoking of privileges that need to be done of database objects. We can use the command “\dg” to list all the available roles in an instance of Azure Database for PostgreSQL as shown below.
Password reset is another common task that many users perform from time to time. It’s very easy to perform this from the psql command prompt as well. Just type the “\password” prompt and it will provide options to change the password. If one intends to change the password for a different user or role, then one can use additional options with this command to change to password accordingly.
In real production environments, typically there are tens of databases, and an administrator may need to switch between different databases. For that, one may often need to list the databases with their respective details to inspect them. The command to list databases is “\l” and the output would look as shown below. It would list even the hidden databases that won’t be visible in the browser pane.
These are some of the basic commands and options to look out for, especially for database administrators during their first encounter with the pgsql tool for working with an instance of Azure Database for PostgreSQL. One can always explore the exhaustive reference of commands by using the command for help.
Conclusion
In this article, we learned how to get acquainted with the basic psql command options, inspect the connection-related settings as well as explore the database and the objects within it, which is generally the first step before working with database objects itself using the tool.
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