How to connect to Postgres database from the command line on Linux

2023-07-26 09:19:44

PostgreSQL is a powerful open source relational database management system known for its robustness and rich feature set.

To interact with a database PostgreSQL you can use the command line interface, which provides a flexible and efficient way to run queries and perform administrative tasks.

In this article, we’ll walk you through the process of logging into a database. PostgreSQL from the command line in Linu

How to connect to Postgres from the command line

Before logging into a database PostgreSQL , make sure you have the necessary client tools installed on your system. These tools include the PostgreSQL command line client, psql which allows you to interact with the database from the terminal.

You can install the client tool psql using package managers like apt , yum or dnf, or downloading them directly from the website de PostgreSQL .

$ sudo apt install postgresql-client
O
$ sudo dnf install postgresql

List PostgreSQL server databases

We will access the user postgres, doing a “su – postgres”, then we execute psql, and type l to list the databases.

To exit “psql”, we will write q

to exit the user “postgres” we will write “exit”

Connect to the local Postgresql database

To get started, open up your terminal and use psql a terminal-based front-end for PostgreSQL as follows, where the “-d” indicates that it is used to specify the database that you are connecting to and “-U” specifies the username that you are connecting with. is connecting.

Note that the following command will allow you to log in to a Postgres database running on the local server:

$ psql -d ubuntupostt -U ravi

If the database connection parameters are correct, psql will provide a message with the name of the database to which psql is currently logged in, followed by the string => where you can execute the SQL commands.

Connect to the PostgreSQL database

Connect to remote Postgresql database

Before you can log in to a remote database, you need to know the name of your target database, the hostname or IP address of the machine it runs on, and the port number on which the Postgres database server is listening on, and what hostname user you want to connect as.

To access a database from Postgres running on a remote host, use the “-h” option to specify the host IP address or the hostname of the machine the server is running on, for example:

$ psql -U ravi -h 192.168.0.124 -p 5432 -d ubuntupostt

By default, the database server Postgres listens on the port 5432 , so we specify the port number. If your server is listening on a different port, you can use the “-p” option to specify the port number (replace 5432 with the actual port number it is listening on).

Press Enter after entering the command and provide the password to establish a connection to the specified database.

psql: error: failed to connect to server: Connection refused

If you receive an error message “could not connect to server: Connection refused” it indicates that there is a problem establishing a connection to the PostgreSQL server on the specified host and port.

Fix sql: error: failed to connect to server: Connection refused

To fix this error, open the configuration file that is usually located in the PostgreSQL data directory (eg /etc/postgresql//main/postgresql.conf).

$ sudo nano /etc/postgresql/15/main/postgresql.conf

Look for the following lines:

listen_addresses=”192.168.0.124″ # which IP addresses to listen on; port = 5432 # (change requires reboot)

If necessary, update the listen_addresses and port to match your server’s configuration. Remember to restart the PostgreSQL service after making changes to the configuration file.

$ sudo systemctl reiniciar postgresql

By following these troubleshooting steps, you should be able to diagnose and resolve the ” Connection refused ” when connecting to the PostgreSQL server.

$ psql -U ravi -h 192.168.0.124 -p 5432 -d ubuntupostt

FATAL: no pg_hba.conf entry for host error

If you still get an error like ” FATAL: no pg_hba.conf entry for host ‘192.168.0.162’ “, you should correct it as shown.

Fix FATAL: No pg_hba.conf entry for host ‘192.168.0.162’ Error

To fix this error, open the configuration file that is usually located in the PostgreSQL data directory (eg /etc/postgresql//main/pg_hba.conf).

$ sudo nano /etc/postgresql/15/main/pg_hba.conf

inside the file pg_hba.conf you must add an entry that allows the specified host, user, and database to connect.

host ubuntupost ravi 192.168.0.162/32 md5

Be sure to replace ubuntupost with the correct database name, ravi with the correct username and 192.168.0.162 with the actual IP address of the client machine.

After making the necessary changes, you need to restart the PostgreSQL service:

$ sudo systemctl restart postgresql

Now try connecting to the PostgreSQL server again using the specified host, user and database, it should allow the connection.

$ psql -U ravi -h 192.168.0.124 -p 5432 -d ubuntupostt

Connect to the remote PostgreSQL database

Once you’re logged in, you can start running queries and performing administrative tasks on the PostgreSQL database directly from the command line. The client psql provides a comprehensive set of commands and functions for managing and querying the database.

Conclusion

Log in to a PostgreSQL database from the command line using the client psql provides a convenient and efficient way to interact with the database.

By following the steps in this article, you can establish a connection to a PostgreSQL database, run queries, and perform administrative tasks right from your terminal.

Mastering the command line interface allows you to take full advantage of PostgreSQL’s capabilities and manage your data efficiently.