How to Install and Use pgcli in Linux for Smart PostgreSQL Autocompletion and Syntax Highlighting

The Ergonomic Shortcomings of the Standard psql Terminal

PostgreSQL has earned an undisputed reputation as the world’s most advanced open-source relational database management system. When managing database servers, developers and database administrators (DBAs) traditionally interact with the engine via the default interactive terminal client, psql. While psql is reliable and universally available, its user experience remains rooted in traditional Unix conventions: auto-completion requires constant Tab pressing, query syntax is completely unhighlighted monochrome text, and multi-line queries lack visual structural guidance.

To modernise database terminal interactions, developer Amjith Ramanujam created pgcli, an interactive command-line interface for PostgreSQL built on Python and prompt_toolkit. pgcli introduces contextual auto-completion (as-you-type suggestions for SQL keywords, database names, table schemas, and column identifiers), dynamic syntax colourisation, smart multi-line editing, and human-friendly tabular output formatting.

Why pgcli Supercharges Database Administration

Integrating pgcli into your administration workflow delivers tangible productivity advantages over standard database clients:

• Context-Aware Auto-Completion: As you type SQL statements (such as SELECT * FROM us...), pgcli queries the active PostgreSQL catalog in the background, surfacing relevant table names and column names in an interactive pop-up menu without demanding repeated manual tab hits.
• SQL Syntax Highlighting: Utilises the Pygments engine to tint keywords, operators, strings, numeric constants, and schema identifiers with configurable colour themes.
• Smart Tabular Display: Employs auto-paging and visual borders. If a query result contains numerous columns that exceed the physical width of your terminal window, pgcli automatically pivots the output into an expanded vertical view (equivalent to psql‘s \x auto mode).
• Secure Named Configurations: Supports configuration profiles inside ~/.config/pgcli/config, allowing you to store database connection strings, favourite queries, and custom key bindings safely.

How to Install pgcli on Linux

Because pgcli is built in Python, it can be deployed via native Linux package managers, pipx, or standard container environments.

Method 1: Installing via pipx (Recommended for Modern Linux)

Modern Linux distributions (such as Ubuntu 24.04+, Debian 12+, and Fedora 40+) enforce PEP 668 to prevent global package pollution. The cleanest, most isolated method to deploy pgcli is using pipx:

# Install pipx on Ubuntu/Debian
sudo apt update && sudo apt install -y pipx
pipx ensurepath

# Install pgcli inside an isolated virtual environment
pipx install pgcli

Verify that the binary operates properly:

pgcli --version

Method 2: Installing via Native Package Managers

You can also install pgcli directly from standard distribution repositories:

# Ubuntu / Debian
sudo apt install -y pgcli

# Fedora
sudo dnf install -y pgcli

# Arch Linux (official community repository)
sudo pacman -S pgcli

Connecting to PostgreSQL Databases

The connection syntax for pgcli mirrors standard psql conventions, accepting connection flags or full URI connection strings:

# Standard localhost connection
pgcli -u postgres -d production_db

# Connecting to a remote database host with specific port
pgcli -h db.internal.lan -p 5432 -u dbadmin -d analytics

# Connecting via standard PostgreSQL URI
pgcli postgresql://dbadmin:[email protected]:5432/analytics

When connecting, pgcli prompts for your database password securely without displaying characters on screen, establishes a TLS/SSL handshake, caches schema metadata, and opens the interactive prompt.

Essential Interactive Features and Keyboard Shortcuts

Once inside the interactive pgcli console, leverage these shortcuts for rapid query construction:

Shortcut / Command	Action Description
Tab / Shift + Tab	Cycles forwards and backwards through the contextual auto-completion suggestions popup.
F2	Toggles between Emacs and Vi navigation keybindings on the fly.
F3	Toggles multi-line mode on and off. When enabled, pressing Enter creates newlines instead of executing.
F4	Toggles auto-completion on or off.
Ctrl + R	Initiates reverse incremental search across previously executed SQL query history.
\dt	Standard psql meta-command to list all tables in the active schema.
\d <table>	Displays detailed schema definition, column data types, indices, and foreign keys.

Customising pgcli Appearance and Behaviour

Upon initial launch, pgcli generates a comprehensive configuration file located at ~/.config/pgcli/config. You can tailor this file with any standard text editor:

nano ~/.config/pgcli/config

Key configuration directives worth customising include:

• syntax_style = monokai: Adjusts the colour theme for SQL code (options include vim, solarized-dark, fruity, native).
• smart_completion = True: Ensures the completion engine suggests only column names that physically belong to the tables referenced in your FROM clause.
• destructive_warning = True: Automatically intercepts accidental executions of dangerous queries (such as DROP TABLE or unconstrained DELETE FROM) and prompts for interactive confirmation before sending the command to the engine.
• table_format = psql: Sets the ASCII border rendering style (e.g., fancy_grid, pipe, psql, simple).

Saving and Recalling Named Queries

If your routine administration involves executing identical diagnostic queries repeatedly (such as checking slow locks or active client connections), save them as named queries directly inside pgcli:

# Save a named query
\sn check_locks SELECT pid, locktype, mode, granted FROM pg_locks WHERE NOT granted;

# Execute the saved query at any time
\n check_locks

# List all stored named queries
\dn

By transforming raw database terminals into high-velocity, auto-completing consoles, pgcli empowers developers and system administrators to audit and query PostgreSQL clusters with unmatched speed and precision.