I am new to the Postgres database management system. How can I access or log into a Postgres database from the command line? This short guide explains how to access or connect to a Postgres database from the terminal.
Before you can log into a database, you need to know the name of your target database, the hostname or IP address of the machine it is running on, and the port number the Postgres database server is listening on), and what user name you want to connect as.
Connect to Postgresql Database
You can log into a Postgres database using the psql, a terminal-based front-end to PostgreSQL as follows, where the -d flag is used to specify the database you are connecting to and the -U specifies the username you are connecting as. Note that this command will allow you to log into a Postgres database running on the localhost:
[ravi@linuxshelltips:~]$ psql -d tecmintdb -U tecmintdbadmin
If the database connection parameters are correct, psql will provide a prompt with the name of the database to which psql is currently connected, followed by the string => where you can run SQL commands.
Connect to Remote Postgresql Database
To access a Postgres database running on a remote host, use the -h option to specify the host IP address or hostname of the machine on which the server is running, for example:
[ravi@linuxshelltips:~]$ psql -d tecmintdb -U tecmintdbadmin -h 10.10.20.5 -p 5432
By default, the Postgres database server listens on port 5432 which is why we did not 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):
[ravi@linuxshelltips:~]$ psql -d tecmintdb -U tecmintdbadmin -p 5432
To force psql to prompt for a password before connecting (which should happen automatically), add the -W flag:
[ravi@linuxshelltips:~]$ psql -d tecmintdb -U tecmintdbadmin -h dbserver.tecmint.com -p 5432 -W
That’s all for now! For more information, see the psql man page (man psql) or help page (psql –help).