{"id":6027,"library":"pgcli","title":"pgcli: PostgreSQL Command-Line Interface","description":"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.","status":"active","version":"4.4.0","language":"python","source_language":"en","source_url":"https://github.com/dbcli/pgcli","tags":["database","cli","postgresql","terminal"],"install":[{"cmd":"pip install pgcli","lang":"bash","label":"PyPI (recommended)"},{"cmd":"brew install pgcli","lang":"bash","label":"macOS (Homebrew)"}],"dependencies":[{"reason":"Required for connecting to a PostgreSQL database. The CLI client interacts with a running PostgreSQL instance.","package":"PostgreSQL","optional":false},{"reason":"System-level development headers for Python and PostgreSQL's C library (libpq) are required to build the `psycopg` or `psycopg-binary` adapter, especially on Linux.","package":"python-dev / libpq-dev","optional":false},{"reason":"The core PostgreSQL adapter. `psycopg` for Linux/macOS, `psycopg-binary` for Windows.","package":"psycopg / psycopg-binary","optional":false},{"reason":"Core library providing interactive terminal features like auto-completion and syntax highlighting.","package":"prompt_toolkit","optional":false},{"reason":"Optional, for integrating with system keyring services to securely store database credentials.","package":"keyring","optional":true},{"reason":"Optional, for establishing SSH tunnels to connect to remote PostgreSQL instances.","package":"sshtunnel","optional":true}],"imports":[],"quickstart":{"code":"pgcli -h localhost -p 5432 -U $(whoami) -d my_database","lang":"bash","description":"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`."},"warnings":[{"fix":"Upgrade your Python environment to Python 3.9 or newer. Ensure your virtual environment uses a supported Python version.","message":"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.","severity":"breaking","affected_versions":">=4.0.0"},{"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`.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"search_vec":"'4.4.0':66 'activ':57 'auto':36 'auto-complet':35 'back':51 'back-slash':50 'cadenc':63 'cli':73 'command':4,18,53 'command-lin':3,17 'complet':37,42 'consist':61 'current':69 'databas':24,72 'enhanc':27 'experi':31 'extens':44 'featur':33 'highlight':39 'interact':16 'interfac':6,20 'like':34,49 'line':5,19 'maintain':58 'packag':11 'pgcli':1,7 'postgresql':2,23,30,74 'project':55 'psql':48 'psql-like':47 'python':10 'releas':62,71 'serv':13 'server':25 'slash':52 'smart':41 'smart-complet':40 'stabl':70 'standard':29 'support':45 'syntax':38 'termin':75 'version':65","created_at":"2026-04-14T18:38:47.183370+00:00","updated_at":"2026-04-16T17:59:19.403998+00:00","problems":[{"fix":"Ensure the PostgreSQL server is running, verify the host and port are correct (e.g., `pgcli -h <host> -p <port> -U <user> <dbname>`), and check any local or network firewalls that might be blocking port 5432 (or your configured port).","cause":"pgcli is unable to establish a connection to the PostgreSQL server, often due to the server not running, incorrect host/port, or firewall restrictions preventing the connection.","error":"could not connect to server: Connection refused Is the server running on host \"localhost\" (...) and accepting TCP/IP connections on port 5432?"},{"fix":"Install the PostgreSQL development libraries for your operating system (e.g., `sudo apt-get install libpq-dev python3-dev` on Debian/Ubuntu, `sudo yum install postgresql-devel python3-devel` on RHEL/CentOS, or `brew install postgresql` and `xcode-select --install` on macOS), then reinstall pgcli with `pip install --no-cache-dir pgcli`.","cause":"This error occurs because the underlying PostgreSQL adapter (psycopg2 or psycopg) that pgcli uses cannot find the necessary client libraries (libpq) or its C/binary components during installation or runtime.","error":"ImportError: no pq wrapper available. Attempts made: - couldn't import psycopg 'c' implementation: No module named 'psycopg_c' - couldn't import psycopg 'binary' implementation: No module named 'psycopg_binary' - couldn't import psycopg 'python' implementation: libpq library not found"},{"fix":"Verify the username and password are correct. You can explicitly provide them using `pgcli -U <correct_username> -W <dbname>` (which will prompt for a password) or ensure your `~/.pgpass` file is correctly configured for passwordless access.","cause":"pgcli successfully connected to the PostgreSQL server, but the username or password provided is incorrect, or the user lacks permission to connect to the specified database.","error":"FATAL: password authentication failed for user \"your_username\""},{"fix":"First, ensure pgcli is installed by running `pip install pgcli`. If it is, locate the `pgcli` executable (e.g., `which pgcli` or `find / -name pgcli` for a global search) and add its directory to your system's PATH environment variable. Alternatively, use `python3 -m pgcli` to run it directly via the Python module.","cause":"The `pgcli` executable is not found in your system's PATH, typically meaning it wasn't installed correctly, or the directory where pip installs executables is not included in your PATH environment variable.","error":"command not found: pgcli"},{"fix":"Install the server's root SSL certificate into your system's trust store. If using a proxy, import the proxy's root certificate. Alternatively, for testing, you might be able to disable SSL verification with an option in pgcli's configuration or connection string (though not recommended for production).","cause":"The client (pgcli) is unable to verify the SSL certificate presented by the PostgreSQL server, often due to an untrusted certificate authority, an expired certificate, or an intermediary like a proxy (e.g., Zscaler) intercepting the connection with its own certificate.","error":"SSL routines:ssl3_get_server_certificate:certificate verify failed"}],"ecosystem":"pypi","meta_description":null,"install_score":null,"quickstart_score":null,"quickstart_tag":null,"pypi_latest":null,"cli_name":"pgcli","cli_version":"Traceback (most recent call last):","type":"library","homepage":"https://pgcli.com","github":null,"docs":null,"changelog":null,"pypi":"https://pypi.org/project/pgcli/","npm":null,"openapi_spec":null,"status_page":null,"smithery":null,"categories":["database"],"base_url":null,"auth_type":null,"provenance":{"verified_status":null,"verified_at":null,"last_verified":"2026-04-14","next_check":"2026-07-13","install_tag":null}}