Simplifying PostgreSQL Management: A Guide to Install and Use pgcli

Managing databases is a crucial aspect for a DBA, and PostgreSQL is a powerful open-source relational database management system that is widely used. To interact with a PostgreSQL database, DBA and developers often rely on command-line tools. The default tool that is used is psql. What if I told you that you can simplify the interaction with your database with a simple tool ? One such tool is pgcli. In this blog, I’ll guide you through the process of installing and using pgcli to make your PostgreSQL database management tasks more efficient and enjoyable.

What is pgcli?

pgcli is a command-line interface for PostgreSQL databases that provides a user-friendly alternative to the default psql command-line tool. It offers features such as syntax highlighting and auto-completion.

Installation

Before starting the installation process, please note that I’m running PostgreSQL 16beta1 installed from sources, running on opensuse 15.4.

Installing pgcli requires Python, so make sure you have Python installed on your system.
There are various way of installing Python so I’ll not explain it now, but you can check if it’s installed with this command:

14:27:21 postgres@localhost:/home/postgres/ [] which pip
/usr/bin/pip

Here’s how to install pgcli using the Python package manager, pip:

16:27:28 postgres@localhost:/home/postgres/ [] pip install pgcli

This command will fetch the necessary packages and install pgcli on your system.

Connecting to a PostgreSQL Database

Once you have pgcli installed, you can easily connect to a PostgreSQL database using the following command:

15:31:06 postgres@localhost:/home/postgres/ [PG16] pgcli -h localhost -p 5432 -U postgres -d postgres
Server: PostgreSQL 16beta1 dbi services build
Version: 3.4.1
Home: http://pgcli.com
postgres@localhost:postgres>

You can get information about the possible parameters to use with the command pgcli –help

Once you’re connected, you’ll see the pgcli prompt, which indicates that you are now working within the pgcli environment.

Using pgcli

pgcli offers several advantages over the default PostgreSQL command-line tool:

1. Syntax Highlighting: SQL commands you enter are color-coded for easier readability.
2. Auto-Completion: pgcli provides suggestions for table and column names

3. Multi-Line Editing: Unlike psql, you can write and edit multi-line queries with ease. (press F3 to enable or disable multi-line editing)

4. Command Help: Typing `\?` gives you access to a list of available commands and shortcuts within pgcli.

5. Configurability: You can customize the behavior and appearance of pgcli using its configuration file. The file is located in ~/.config/pgcli/config.

Basic Commands and Usage

Here are some basic commands to get you started with pgcli:

– To list all available databases:
\l

Also, note the differences with the default psql cli:

– To switch to a different database:
/c <database_name>

Autocompletion proposing you the list of available databases.

Remember, you can also use regular SQL commands to interact with your databases as well.

Conclusion

pgcli is a great tool that makes your PostgreSQL database management experience easier by providing a more interactive and intuitive command-line interface. For people like me, who are keen to forget the name of their PostgreSQL tables, autocompletion is a wonderful tool. By following the installation and usage guide outlined in this blog post, I hope to help you make your life easier.

You can find more information on pgcli on the website and the GitHub of the tool:
https://github.com/dbcli/pgcli
https://www.pgcli.com/install