Overview
psql is a powerful tool for interacting with PostgreSQL databases. It not only allows SQL commands but also provides various meta-commands (backslash commands) that streamline database management tasks.
Key Features
- Execute SQL queries
- Manage databases and tables
- Configure users and permissions
- Execute script files
- Provide an interactive shell environment
Key Options
These are the main options you can use with the psql command line.
Connection Options
Command Execution Options
Generated command:
Try combining the commands.
Description:
`psql` Executes the command.
Combine the above options to virtually execute commands with AI.
Usage Examples
Various examples of using the psql command.
Basic Database Connection
psqlConnects to the default database using the default user (current system user).
Connect to a Specific Database and User
psql -U myuser -d mydb -WConnects to the 'mydb' database as user 'myuser'. A password prompt will appear.
Connect to a Remote Server
psql -h db.example.com -p 5432 -U postgresConnects to port 5432 on the remote host 'db.example.com' as user 'postgres'.
Execute a Single SQL Command
psql -U postgres -d postgres -c "SELECT version();"Executes an SQL command to query the database version and then exits psql.
Execute an SQL Script File
psql -U postgres -d mydb -f script.sqlExecutes the SQL commands contained in the 'script.sql' file.
List Databases
psql -lDisplays a list of all databases on the current PostgreSQL server.
Installation
psql is provided as part of the PostgreSQL client package. Most Linux distributions allow you to install only the client tools without installing the PostgreSQL server.
Debian/Ubuntu
sudo apt update
sudo apt install postgresql-clientInstalls the PostgreSQL client tools using the apt package manager.
RHEL/CentOS/Fedora
sudo dnf install postgresqlInstalls the PostgreSQL client tools using the yum or dnf package manager.
Tips & Notes
Tips and notes for using psql more efficiently.
Key psql Meta-Commands
You can perform various tasks within the psql shell using commands starting with a backslash (\).
- \l: List all databases
- \dt: List tables in the current database
- \du: List all users (roles)
- \d <table>: Display schema information for a specific table
- \q: Quit psql
- \?: Display help for all meta-commands
Utilizing Environment Variables
You can simplify psql commands by setting frequently used connection information as environment variables.
- PGHOST: Hostname or IP address
- PGPORT: Port number
- PGUSER: Username
- PGDATABASE: Database name
- PGPASSWORD: Password (not recommended for security reasons; using .pgpass file is advised)
.pgpass File
To securely store passwords without exposing them in the command line or environment variables, it is recommended to use the ~/.pgpass file. This file stores passwords for specific hosts, ports, databases, and usernames, allowing psql to authenticate automatically.