How to Use psql: A Comprehensive Guide to PostgreSQL’s Command-Line Client

psql is the official command-line interface (CLI) for interacting with PostgreSQL databases.

It’s lightweight, powerful, and essential for database administrators, developers, and DevOps engineers working with PostgreSQL.

This guide covers everything from basic connection setup to advanced customization and troubleshooting—written in native, industry-standard English for technical practitioners.

Prerequisites

Before getting started, ensure:

1. PostgreSQL is installed on your system (psql comes bundled with PostgreSQL by default).
2. You have a valid PostgreSQL user account (the default superuser is postgres).
3. The PostgreSQL service is running (verify with sudo systemctl status postgresql on Linux/macOS).

Verify psql Installation

Check if psql is available and confirm its version:

psql --version
# Example output: psql (PostgreSQL) 16.2 (Ubuntu 16.2-1.pgdg22.04+1)

1. Connecting to PostgreSQL with psql

psql supports multiple connection methods, from default local connections to remote database access.

1.1 Default Local Connection (No Parameters)

When you run psql without arguments, it uses system defaults to connect:

psql

How the Default Connection Works

psql auto-populates connection parameters in this order (highest to lowest priority):

1. Environment variables (e.g., PGUSER, PGDATABASE).
2. PostgreSQL’s default settings:
   • User: Matches your current OS username (e.g., ubuntu → postgres user if the OS user doesn’t exist in PostgreSQL).
   • Database: Matches the username (falls back to postgres if the database doesn’t exist).
   • Host: Local Unix socket (default path: /var/run/postgresql on Linux, /tmp on macOS).
   • Port: 5432 (PostgreSQL’s default port).
3. Authentication: Uses peer authentication (trusts OS users matching PostgreSQL users) defined in pg_hba.conf.

1.2 Specify Connection Parameters (Recommended)

For explicit control, use flags to define connection details:

# Basic syntax: psql -U <user> -d <database> -h <host> -p <port>
psql -U postgres -d mydb -h localhost -p 5432

Flag	Purpose	Example
-U	PostgreSQL username (required for non-default users)	-U test_user
-d	Target database name	-d ecommerce_db
-h	Hostname/IP address (use localhost for local connections)	-h 192.168.1.100
-p	Port number (default: 5432)	-p 5433
-W	Force password prompt (even if authentication allows no password)	-W

1.3 Connect via Connection URI

For scriptable, concise connections, use a PostgreSQL URI:

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

psql postgresql://postgres:mypassword@localhost:5432/mydb

URI format: postgresql://<user>:<password>@<host>:<port>/<database>

1.4 Exit psql

To quit the psql shell at any time:

\\\\q  -- Shortcut (most common)
# Or:
EXIT;  -- SQL-style command

2. Essential psql Commands

psql has two types of commands:

• Meta-commands: Start with \\\\ (backslash) – for CLI navigation/management.
• SQL commands: Standard PostgreSQL SQL (must end with ;).

2.1 Core Meta-Commands (psql Shortcuts)

These are the most frequently used shortcuts for database exploration:

Command	Purpose	Example
\\\\l	List all databases	\\\\l+ (add + for detailed info: owner, size, permissions)
\\\\c <db>	Connect to a different database	\\\\c mydb
\\\\dt	List tables in the current schema	\\\\dt my_schema.* (list tables in a specific schema)
\\\\dn	List all schemas	\\\\dn+ (detailed schema info)
\\\\d <table>	Show table structure (columns, types, constraints)	\\\\d users
\\\\du	List PostgreSQL users/roles	\\\\du+ (detailed role permissions)
\\\\conninfo	Show current connection details	\\\\conninfo
\\\\e	Open default editor (e.g., vim) to write/edit SQL	\\\\e (edit query, save to run)
\\\\x	Toggle expanded output (useful for wide tables)	\\\\x on (enable expanded mode)
\\\\?	List all psql meta-commands	\\\\?
\\\\h	Show help for SQL commands	\\\\h CREATE TABLE (help for table creation)

2.2 Executing SQL in psql

Run single-line SQL commands (end with ;):

SELECT * FROM users LIMIT 10;
INSERT INTO products (name, price) VALUES ('Laptop', 999.99);

Run multi-line SQL (psql will prompt for continuation until ;):

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.3 Run SQL from a File

Execute a saved SQL script (e.g., schema.sql) in psql:

# From terminal (before connecting to psql)
psql -U postgres -d mydb -f schema.sql

# From within psql shell
\\\\i schema.sql

3. Common psql Operations

3.1 Manage Databases

-- Create a database
CREATE DATABASE ecommerce_db OWNER test_user;

-- Rename a database (must disconnect all sessions first)
ALTER DATABASE ecommerce_db RENAME TO shop_db;

-- Delete a database (irreversible!)
DROP DATABASE IF EXISTS shop_db;

3.2 Manage Schemas & Tables

-- Create a schema
CREATE SCHEMA IF NOT EXISTS sales;

-- Create a table in a schema
CREATE TABLE sales.transactions (
    transaction_id INT PRIMARY KEY,
    amount NUMERIC(10,2),
    transaction_time TIMESTAMP
);

-- Grant permissions on a schema/table
GRANT USAGE, CREATE ON SCHEMA sales TO test_user;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA sales TO test_user;

3.3 Export/Import Data

Export Query Results to a File

-- Export to CSV (from psql shell)
\\\\copy (SELECT * FROM users) TO '/tmp/users.csv' WITH (FORMAT csv, HEADER true);

-- Export entire table
\\\\copy sales.transactions TO '/tmp/transactions.csv' WITH CSV HEADER;

Import Data from CSV

\\\\copy sales.transactions FROM '/tmp/new_transactions.csv' WITH (FORMAT csv, HEADER true);

4. Customizing psql Behavior

psql is highly customizable via configuration files and environment variables.

4.1 The .psqlrc Configuration File

The .psqlrc file (user-specific) loads every time psql starts, letting you set default behavior. It lives in your home directory (~/.psqlrc).

Example .psqlrc File

# ~/.psqlrc - Custom psql settings
\\\\set PROMPT1 '%n@%/%R%# '  # Custom prompt: [user]@[database]/[schema] >
\\\\x auto  # Auto-expand output for wide tables
\\\\set HISTFILE ~/.psql_history  # Define history file path
\\\\set HISTCONTROL ignoredups  # Ignore duplicate commands in history
\\\\c postgres  # Auto-connect to postgres database on startup

Apply Changes: Restart psql, or reload .psqlrc with \\\\ir ~/.psqlrc.

4.2 Environment Variables

Override default connection parameters with environment variables (add these to ~/.bashrc/~/.zshrc for persistence):

# Set default psql user/database
export PGUSER=postgres
export PGDATABASE=mydb
export PGPORT=5432
export PGHOST=localhost

# Avoid hardcoding passwords (use .pgpass instead)
# export PGPASSWORD=mypassword  # NOT RECOMMENDED (insecure)

4.3 Secure Password Storage with .pgpass

Store passwords securely (instead of PGPASSWORD) in ~/.pgpass:

# Create .pgpass (format: host:port:database:user:password)
echo "localhost:5432:mydb:test_user:MySecurePass123!" >> ~/.pgpass

# Set strict permissions (required by PostgreSQL)
chmod 600 ~/.pgpass

psql will auto-use this file for password authentication.

5. Troubleshooting Common psql Issues

5.1 “psql: error: connection to server on socket…” (Connection Failed)

Error Message:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?

Solutions:

1. Check if PostgreSQL service is running: sudo systemctl start postgresql # Start service sudo systemctl enable postgresql # Enable on boot
2. Verify the socket path (match unix_socket_directories in postgresql.conf): psql -U postgres -c "SHOW unix_socket_directories;"

5.2 “permission denied for database” (Authentication Failure)

Error Message:

psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "test_user"

Solutions:

1. Check pg_hba.conf (path: /etc/postgresql/<version>/main/pg_hba.conf) for authentication rules: # Update to allow password auth for local connections local all all md5 host all all 127.0.0.1/32 md5
2. Reload PostgreSQL to apply changes: sudo systemctl reload postgresql
3. Reset the user’s password (if forgotten): ALTER USER test_user WITH PASSWORD 'NewSecurePass!';

5.3 “relation does not exist” (Table/Schema Not Found)

Error Message:

ERROR:  relation "transactions" does not exist

Solutions:

1. Verify the table is in the correct schema (use \\\\dt *.* to list all tables).
2. Set the search path to include the target schema: SET search_path TO sales, public; # Persist for user: ALTER USER test_user SET search_path TO sales, public;
3. Explicitly specify the schema when querying: SELECT * FROM sales.transactions;

5.4 psql Ignores .psqlrc

Solutions:

1. Check file path (must be ~/.psqlrc; case-sensitive on Linux/macOS).
2. Verify file permissions (run chmod 644 ~/.psqlrc).
3. Load the file manually to test: \\\\ir ~/.psqlrc.

6. psql Best Practices

1. Use Least Privilege: Avoid connecting as postgres (superuser) for daily tasks—use a dedicated user with limited permissions.
2. Secure Passwords: Never hardcode passwords in scripts or use PGPASSWORD; use .pgpass or password prompts.
3. Log Queries: For debugging, log psql sessions to a file: psql -U test_user -d mydb -L /tmp/psql_session.log # Log all output to file
4. Format Output for Readability: Use \\\\x auto (expanded output) for wide tables and \\\\pset format aligned for clean text output.
5. Backup Before Changes: Always back up data (e.g., pg_dump) before running destructive commands (e.g., DROP TABLE).

Summary

psql is the backbone of PostgreSQL CLI management—mastering its core commands and customization options will streamline your workflow. Key takeaways:

• Connect with psql (default) or explicit flags (U, d, h) for control.
• Use meta-commands (e.g., \\\\l, \\\\dt, \\\\c) for quick navigation.
• Customize behavior with ~/.psqlrc and environment variables (avoid insecure PGPASSWORD).
• Troubleshoot connection/permissions issues with pg_hba.conf and postgresql.conf.
• Follow security best practices (least privilege, secure password storage) for production environments.

With this guide, you can confidently perform daily PostgreSQL tasks—from basic queries to advanced database administration—using psql.