Connection via terminal to PostgreSQL
PostgreSQL is one of the most popular database engines among Unix users. Today we will see how to make a connection to PostgreSQL from the terminal with different client options.
Default connection
The fastest way to make a connection is to run the psql command with the name of the database you want to enter.
psql db_nameTo connect in this way, it is necessary to have a trust authentication method, which depends on how the database engine was installed and on which operating system. To find out if there is indeed a trust authentication method, you can check the contents of the pg_hba.conf configuration file. Below is an example of the last lines of this file.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
Of all these lines, the following is the one that does not allow any user with an account on the server to enter the cluster with any user and to any database.
local all all trust
Connection with user, password and database
The most common way to connect is usually by explicitly indicating the user and the database to which you want to enter.
psql --username=user_name --password db_nameThe --username parameter can be replaced with -U and the --password parameter with -W. This last one indicates that the user's password must be entered once the command is executed (the console will request it).
Connection and execution of a command
In some cases, it is useful to make a connection in order to execute a specific command and immediately terminate the session. It may be, for example, a scheduled task or execution from a programming language.
psql -U user_name -W db_name --command='select now()'Connection and execution of a file
Another common way of connection is the execution of a script, which can be done as follows:
psql db_name --file=file.sqlThe --file parameter can also be replaced with -f.