Connection via terminal to PostgreSQL

Author
By Darío Rivera
Posted On in 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_name

To 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_name

The --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.sql

The --file parameter can also be replaced with -f.


Acerca de Darío Rivera

Author

Application Architect at Elentra Corp . Quality developer and passionate learner with 10+ years of experience in web technologies. Creator of EasyHttp , an standard way to consume HTTP Clients.

LinkedIn Twitter Instagram

Sólo aquellos que han alcanzado el éxito saben que siempre estuvo a un paso del momento en que pensaron renunciar.