pgcli: PostgreSQL Command-Line Interface

4.4.0 · active · verified Tue Apr 14

pgcli is a Python package that serves as an interactive command-line interface for the PostgreSQL database server. It enhances the standard PostgreSQL experience with features like auto-completion, syntax highlighting, smart-completion, and extensive support for psql-like back-slash commands. The project is actively maintained with a consistent release cadence, with version 4.4.0 being the current stable release.

Warnings

breaking Python 3.8 and older are no longer supported. Version 4.0.0 dropped support for Python <3.8, and version 4.2.0 dropped support for Python <3.9.
Fix: Upgrade your Python environment to Python 3.9 or newer. Ensure your virtual environment uses a supported Python version.
gotcha Installation on Linux often requires system-level development packages for `libpq` and Python (`python-dev`, `libpq-dev`, or equivalents like `postgresql-devel`, `python-devel`). Without these, the `psycopg` adapter build may fail.
Fix: Install the necessary system packages, e.g., `sudo apt-get install python3-dev libpq-dev` (Debian/Ubuntu) or `sudo yum install python3-devel postgresql-devel` (RHEL/CentOS/Fedora) before running `pip install pgcli`.
gotcha Connection issues can arise due to network proxies (e.g., Zscaler) interfering with direct TCP connections or SSL certificate validation. Errors like 'SSL routines:ssl3_get_server_certificate:certificate verify failed' are common.
Fix: Ensure the proxy's root certificate is installed in your system's trust chain. Verify outbound firewall rules allow PostgreSQL traffic (port 5432 by default). Consider using an SSH tunnel if direct access is restricted.
gotcha Storing passwords in shell history or inadequately permissioned `.pgpass` files (e.g., `chmod 644`) poses a security risk. Environment variables (like `PGPASSWORD`) can also be inspected.
Fix: Always set `.pgpass` file permissions strictly (e.g., `chmod 600`). Avoid storing production passwords directly in shell history. Use secure credential management systems or rely on interactive password prompts where appropriate.
gotcha The interactive nature and ease of use can inadvertently lead to executing destructive SQL commands. This is a common footgun in any direct database CLI.
Fix: Utilize `pgcli`'s configuration options like `destructive_statements_require_transaction` and `destructive_warning` (introduced in 4.4.0) to prompt for confirmation before executing potentially damaging queries.

Install

pip install pgcli PyPI (recommended)
brew install pgcli macOS (Homebrew)

Quickstart

Connect to a local PostgreSQL database named 'my_database' using the current system username. You will be prompted for a password if required. Alternatively, use a connection URL: `pgcli postgresql://user:password@host:port/dbname`.

pgcli -h localhost -p 5432 -U $(whoami) -d my_database

view raw JSON →