Postgres Common Command-line Utilities

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re getting started with PostgreSQL, you may not be familiar with all the tools available to help you accomplish different database tasks. PostgreSQL provides a variety of command-line utilities that allow users to interact with the database more efficiently. In this article, we’ll discuss some Postgres common command-line utilities and show how they can be used to make database administration easier.

Prerequisite

Before we begin our discussion of Postgres common command-line utilities, let’s review some prerequisites that are essential for this tutorial:

  • First, you’ll need to ensure that PostgreSQL is installed and configured on your machine.
  • You’ll also need some knowledge of PostgreSQL to get the most out of this article.

Starting Postgres Shell

We’ll need to use the Postgres shell in order to interact with a PostgreSQL database on the command line. We can open the command-line application in two ways:

  • First, we can open the application the easy way by typing the word psql at the start menu bar. Simply accept the default values by pressing Enter and provide the password when prompted.

alt text

  • Another way of opening the application involves the terminal or command line. Just navigate to the installation folder of the Postgres application; in our installation, it’s located at this directory path: C:\Program Files\PostgreSQL\12\bin. Then, type the command psql -U postgres and provide the password when prompted.

alt text

List All Databases

If you’re a database administrator or a developer, it’s essential to be able to obtain a list of the existing databases on your Postgres server. To get this information, we can use the \l command:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# \l
                                               List of databases
   Name    |  Owner   | Encoding |         Collate          |          Ctype           |   Access privileges
-----------+----------+----------+--------------------------+--------------------------+-----------------------
 cars      | postgres | UTF8     | English_Philippines.1252 | English_Philippines.1252 |
 flowers   | postgres | UTF8     | English_Philippines.1252 | English_Philippines.1252 |
 inventory | postgres | UTF8     | English_Philippines.1252 | English_Philippines.1252 |
 personnel | postgres | UTF8     | English_Philippines.1252 | English_Philippines.1252 |
 postgres  | postgres | UTF8     | English_Philippines.1252 | English_Philippines.1252 |
 template0 | postgres | UTF8     | English_Philippines.1252 | English_Philippines.1252 | =c/postgres          +
           |          |          |                          |                          | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_Philippines.1252 | English_Philippines.1252 | =c/postgres          +
           |          |          |                          |                          | postgres=CTc/postgres
(7 rows)

We can see that we have the following databases listed under the ‘name’ column:

  • cars
  • flowers
  • inventory
  • personnel
  • postgres
  • template0
  • template1

List tables in a database

You may also need to obtain a list of tables that exist within a given database. We can see the existing tables in a database using the \dt command; however, we first need to connect to or switch to the target database using the \c command before we can use \dt. In the example shown below, we connect to the personnel database:

1
2
postgres=# \c personnel
You are now connected to database "personnel" as user "postgres".

Once we’re connected to the database, we can use the \dt command:

1
2
3
4
5
6
personnel=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | person | table | postgres
(1 row)

In this case, the personnel database contains only one table named ‘person’.

Describe a table

When we used the \dt command, we were able to see the table that existed within the personnel database, but the output only provided limited details about the table. To see a table’s structure, we can use the \d command to describe the table:

1
2
3
4
5
6
7
8
9
10
personnel=# \d person
                      Table "public.person"
   Column   |       Type        | Collation | Nullable | Default
------------+-------------------+-----------+----------+---------
 id         | integer           |           | not null |
 p_name     | character varying |           | not null |
 p_lastname | character varying |           | not null |
 p_age      | integer           |           |          |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)

The above result shows that we were able to unearth all the essential information about the table.

Display Postgres Version

The next utility we’ll discuss is essential for determining compatibility and stability issues– the command to obtain your Postgres version. To find out which version of Postgres is installed on your machine, use the command SELECT version();.

The output should look something like this:

1
2
3
4
5
personnel=# SELECT version();
                          version
------------------------------------------------------------
 PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit
(1 row)

Show all available commands

To view a list of all available commands, we use the command \?:

alt text

Notice that there are quite a few commands available for use.

Postgres Statements

If you want to know the syntax of a particular Postgres statement, use the \h command to learn more about that statement:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
personnel=# \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/12/sql-createdatabase.html

In the example above, we used the command \h CREATE DATABASE to view information about the CREATE DATABASE statement.

Query Execution Time

If you’re a database administrator responsible for optimizing database performance, knowing query execution time is essential. In Postgres, we can get this information using the \timing command.

To get the execution time for a query, we first need to execute the command \timing to enable the timing option. After that, we can run the query we want to time:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
personnel=# \timing
Timing is on.
personnel=# SELECT * FROM person;
 id | p_name | p_lastname | p_age
----+--------+------------+-------
  1 | angie  | locsin     |    23
  2 | tony   | lacson     |    24
  3 | duke   | samuel     |    24
  4 | gina   | gener      |    25
  5 | abi    | galisanao  |    21
  6 | john   | dune       |    23
  7 | yeshua | galisanao  |    28
  8 | nora   | burn       |    23
  9 | raizel | galisanao  |    35
(9 rows)


Time: 3.886 ms

We can see that this query took 3.886 ms to fetch and return the data.

Conclusion

If you’re a database administrator or developer working with PostgreSQL, you’ll want to take advantage of any tools that are available to make your work easier. PostgreSQL offers a wide range of utilities designed to help you interact with your database more efficiently. In this article, we provided an introduction to many Postgres common command-line utilities. With this tutorial to use as a reference, you’ll be able to make good use of these utilities in your own PostgreSQL environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.