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.
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:
% psql --version psql (PostgreSQL) 14.5 (Homebrew)
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_NAME
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.
--username determine the database name to connect to and the username to connect with, respectively.
psql command makes a connection to the
mydb database with username
postgres that resides on the same computer that you are working on,
% psql --host localhost --port 5432 --dbname mydb --username postgres
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:
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.
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
Also, to get a list of all available meta-commands, you can type
\? and press Enter.
\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.
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.
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
To reveal the details of each relation (table, view, etc.), we can use the
\d relation-name meta-command.
\d employees returns the table's columns and their data types along with additional information for each column, as shown below:
\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
\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
\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
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.
pg_dump Client Application
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
% pg_dump mydb > dump_file.sql
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:
% createdb mydb_restored
% psql --host localhost --dbname mydb_restored --username postgres --file /Users/mohammadmehdi/dump_file.sql
% psql --host localhost --port 5432 --dbname mydb_restored --username postgres
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
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
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
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.