Establishing a connection to a PostgreSQL database via the command line is a fundamental skill for developers, database administrators, and data engineers. The PostgreSQL command-line interface, primarily accessed through the psql utility, provides a powerful and efficient method to interact with your databases directly. This approach eliminates the overhead of graphical tools and allows for precise execution of SQL commands and administrative tasks.
Prerequisites for Connection
Before initiating a connection, ensure that the PostgreSQL server is actively running and configured to accept connections. You need to know the specific hostname or IP address, the port number (default is 5432), the database name, and the username you intend to authenticate with. If the server is on a remote machine, verify that firewall rules permit traffic on the designated port.
Basic Connection Syntax
The core command to launch the interactive terminal is psql . Its flexibility allows for credentials to be supplied directly within the command string, though using environment variables or a password prompt is often more secure. Below are common methods to establish a session.
Connecting with Explicit Parameters
You can specify the connection details directly in the command line. This method is useful for quick access but may expose sensitive information in your shell history.
psql -h hostname -p port -U username -d database
Using Environment Variables
For a cleaner and more secure approach, set environment variables like PGHOST , PGPORT , PGUSER , PGPASSWORD , and PGDATABASE . Once these are configured, you can connect with a minimal command.
export PGDATABASE=mydb export PGUSER=myuser psql
Initiating the Session
Upon executing the connection command, the system will prompt you for a password if password authentication is required. Successful authentication loads the client and server versions, and you are presented with a prompt indicating the connected database. At this stage, you are ready to input SQL statements directly.
Essential Internal Commands
While connected, you can utilize specific backslash commands to manage the session and inspect the database structure. These meta-commands are processed by psql itself rather than being sent to the server.
\dt — Lists all tables in the current database.
\d — Describes the structure of a specific table.
\conninfo — Displays information about the current connection.
\q — Exits the psql session.
Executing Queries and Scripting
You can execute standard SQL queries immediately after connecting. Furthermore, psql allows you to run SQL scripts stored in files, which is ideal for migrations or complex operations. Use the \i command followed by the file path to execute the script within the current session.
Troubleshooting Common Issues
Connection errors often stem from incorrect credentials, network issues, or misconfigured pg_hba.conf settings. A "connection refused" error typically indicates that the server is not running or is blocking the port. Always verify the server status and authentication configuration if you encounter access problems.
