PostgreSQL, often simply called Postgres, is a free and open-source relational database management system (RDBMS) known for its reliability, feature robustness, and performance. It has been actively developed for over 35 years and is considered one of the most advanced open-source RDBMSs available.
psql
is the official CLI client to command the PostgreSQL database. It supports interactive and noninteractive use.
Below list the 10 most commonly used psql
commands with examples.
1. Connect to a database - psql -U xxx -p -h xxx -d xxx
If you are using the default PostgreSQL username postgres
and have not set a password, you can connect to the database using the following command. You will be prompted to enter the password for the postgres
user.
If you are using a different username or have set a password for the postgres
user, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.
If the PostgreSQL server is running on a different port than the default (5432), you can connect to the database using the following command. You will be prompted to enter the password for the specified user.
If the PostgreSQL server is running on a remote host, you can connect to the database using the following command. You will be prompted to enter the password for the specified user.
In all of these examples, you can also specify the name of the database you want to connect to using the -d
option. For example, to connect to the database named my_database
, you would use the following command:
2. Run a single command - -c
To run a single command in PostgreSQL using the psql
command-line interface, you can use the -c
option followed by the SQL command you want to execute.
3. List available databases - \l
This command will all of the databases that are currently available on the server. This can be useful for getting an overview of the databases that are available to you.
4. Switch to a specified database - \c
The \c
command switches to the specified database. This is useful for working with a specific database after you have listed the available databases.
5. List tables - \dt
The \dt
command lists all of the tables that are currently available in the current database. This can be useful for getting an overview of the tables that are available to you in the current database.
6. Describe a specified table - \d
The \d
command describes the specified table. This provides information about the table's columns, data types, and constraints.
7. Listing users - \du
The \du
command lists all of the users that exist in the current database.
The \du username
command is used to list the roles (users) in the current database that have the specified username.
If the specified username does not match any roles in the current database, the output will be an empty table.
8. Show connections - pg_stat_activity
To list all active connections, execute the following SQL query.
You can also filter the results to specific connections based on criteria such as username, database name, or state. For example, to list only active connections to the database mydb, use the following query:
Similarly, to list connections currently executing queries, use the following query:
9. Kill connection - pg_terminate_backend()
, kill
Once you have identified the PID of the connection you want to kill, you can use the pg_terminate_backend()
function to terminate it. For example, the following command will kill the connection with PID 1234:
Using the kill command is a less common method for killing PostgreSQL connections, as it can be more dangerous and less reliable than using the pg_terminate_backend() function. However, it may be necessary in certain situations, such as when the connection is unresponsive or the pg_terminate_backend() function fails.
10. Quit - \q
,Ctrl+D
/Ctrl+Z
Using the \q
command or the keyboard shortcut Ctrl+D
(or Ctrl+Z
on windows). This will immediately exit the psql command-line interface.