10 Command-Line Utilities in PostgreSQL
In this tutorial, discover the PostgreSQL Client Applications, and learn ten of the most useful command-line tools.
When you decided to work with PostgreSQL databases, you probably used graphical user interfaces such as PGAdmin, DBeaver, or even paid ones like DataGrip. These software applications provide user-friendly interfaces for working more effectively with databases, and they definitely make database management much easier for database administrators. But there's a robust set of tools called PostgreSQL Client Applications that will truly leverage your efficiency while working with PostgreSQL databases. The good news is that these valuable tools are bundled and come with the PostgreSQL installation package for free.
The PostgreSQL Client Applications bring a rapid and powerful set of command-line tools to the table to make interacting with the PostgreSQL servers across platforms, local or remote, more exciting. In this tutorial, we're going to discover the PostgreSQL Client Applications and learn how to get the most out of them by discussing ten of the most useful command-line tools.
We assume you know the fundamentals of SQL, including the basics of working with database management systems. If you're unfamiliar with these or eager to brush up on your SQL skills, you might like to try our SQL Fundamentals Skill Path — Dataquest. We also assume you have already installed PostgreSQL on your computer; if you haven't, please refer to the official PostgreSQL website to download and install it from here.
PostgreSQL Client Applications
As I mentioned, this bundle contains different tools that assist database administrators and data experts in getting the most out of PostgreSQL databases hosted on a local server, across networked servers, or in the cloud platforms. These command-line utilities, available for Windows, MacOS, and Linux operating systems, are designed to work with and manage database objects. There are particular tool sets for creating and removing databases, roles, and users. There are also some valuable tools for making and restoring backups of databases.
However, among these utilities, the pick of the litter is the psql command-line tool, which allows us to connect to databases, explore their contents, execute SQL queries, and output the result sets in various formats. This utility is really a game changer for leveraging the capabilities of PostgreSQL.
The psql Client Application
Let's start exploring the PostgreSQL Client Applications with the psql command-line utility and its different options.
But before starting work with this utility, let's check the PostgreSQL version installed on your local computer by running the following command:
The PostgreSQL version installed on my MacBook is 14.5, which allows me to connect to any PostgreSQL server that's running version 14.5 or earlier.
1. Connect to a Database
The first step to getting into the psql command-line tool is to connect to a local or remote PostgreSQL server.
To connect to a PostgreSQL database, you can use the command template:
psql --host HOSTNAME --port PORT --user USERNAME --dbname DATABASE_NAMEThe HOSTNAME is a remote server name or its IP address. If the PostgreSQL server is running on your local machine, you must use localhost instead of a server's IP address.
You also need to identify the communication port with --port. The default communication port is 5432. You may omit the --port PORT argument if the port number is 5432.
The --dbname and --username determine the database name to connect to and the username to connect with, respectively.
The following psql command makes a connection to the mydb database with username postgres that resides on the same computer that you are working on, localhost.
Executing the command above opens up a connection to the PostgreSQL server running on your local computer, and the command prompt will be changed as follows:
mydb=#
Now, you can run psql's meta-commands, which we will discuss soon. Meanwhile, let's close the connection to the PostgreSQL server by typing \q and pressing Enter, which will return you to the operating system command prompt.
mydb=# \q
Meta Commands
The psql utility offers a great feature called meta, or backslash, commands. These commands or instructions are processed by the psql client application directly and not executed by the PostgreSQL server. The meta-commands provide a variety of options. We'll learn more about them in the following sections.
One of the meta-commands is \q, which we tried earlier to quit the psql environment.
Also, to get a list of all available meta-commands, you can type \? and press Enter.
mydb=# \?
2. The \l Meta-Command
The \l meta-command allows you to list all the databases stored on the PostgreSQL server you are connected to. First, connect to the database server, and then run the \l meta-command, as shown in the image below. The command lists all the available databases along with all the details.
3. The \dt Meta-Command
We've connected to the mydb database; now we're interested in listing all the existing tables on this database. To do so, you can use the \dt meta-command as follows:
As shown in the image above, all the database's relations (tables) are listed with useful details such as schema and the owner of tables.
4. The \c Meta-Command
Sometimes, we need to change the active database and switch to another one. The \c meta-command allows us to connect to a new database. Let's try it.
So, we've made chinook the active database. Now, we can write queries against the database. Let's see how we can write a simple query against the actor table in the chinook database.
5. The \d Meta-Command
To reveal the details of each relation (table, view, etc.), we can use the \d relation-name meta-command.
For example, \d employees returns the table's columns and their data types along with additional information for each column, as shown below:
6. The \dn Meta-Command
The \dn meta-command allows us to list all the schemas existing in a PostgreSQL database. To see the output of this meta-command, let's connect to the Adventureworks database and list all the existing schemas using the \dn meta-command.
7. The \df Meta-Command
The \df meta-command returns all the available functions of the current database. First, connect to the Adventureworks database, type \df, and press Enter to see how it works. It will show all the functions existing in the Adventureworks database.
8. The \dv Meta-Command
The \dv meta-command is another useful psql's backslash-command that enables us to list all the views in the current database.
For example, we can use this meta-command after connecting to the database to show the available views in the chinook database.
NOTE
The psql client application has dozens of meta-commands, and discussing them all is beyond this tutorial's scope. If you're interested in learning more about these meta-commands, please refer to PostgreSQl's documentation portal.
Backup and Restore PostgreSQL Databases
So far, we've focused on using the psql client application. But there are some additional utilities bundled with PostgreSQL that make life easier for database administrators and data engineers — especially for those data experts who work on database maintenance tasks.
9. The pg_dump Client Application
The pg_dump client application generates a file with SQL commands to make a backup of a database. If we run these SQL statements, we will recreate exactly the same database with the same content.
The following statement makes a text file containing all the SQL statements required for recreating the mydb database.
10. Restoring Plain-Text Backups
Restoring a database from a plain-text file generated by the pg_dump client application is simple.
Most of the database experts use the psql utility with the following options to recreate a database that we have a backup file of in plain-text format:
The three commands above will create a new database called mydb_restored, then the dump file will be restored into it, which will recreate all the database's objects that we already made a backup of it using the pg_dump utility.
The last command connects to the mydb_restored database. So, we can list all the existing relations in this database to ensure it is exactly the exact copy of the mydb database.
Summary
This tutorial explored the PostgreSQL Client Applications by focusing on the psql utility and its meta-commands, making a database backup in a plain-text format using pg_dump, then restoring it into a new database with psql.
This tutorial is just an introduction to some of the possibilities of the PostgreSQL Client Applications. Now the doors to discovering more about these utilities are open to you, and the best reference is PostgreSQL Client Applications reference.
I hope that you have learned something new today. Feel free to connect with me on LinkedIn or Twitter.
