Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to connect to PostgreSQL through CLI?

I am trying to access postgresql through the command line. However, whenever it is time for me to enter my password, I get the following error: Fatal: password authentication failed for user RMehta. I am pretty sure the reason that password authentication fails is that the user for my database is postgres, and not RMehta.

The only solution I found was using runas in the command line, but I couldn't figure how to get runas to work. Thanks a lot for any advice. I am using windows 7, and postgresql 9.3

like image 788
Ravi Mehta Avatar asked Oct 17 '14 14:10

Ravi Mehta


People also ask

How do I connect to postgres from terminal?

Username [postgres]: The default username for postgres is postgres. (If you are using Advanced Server it is enterprisedb.) On a Mac or Windows, you are able to connect to the default instance by simply hitting enter at the shell or command prompt when trying to run psql and keying in the password.

How do I connect to a PostgreSQL database using SQL shell?

On Windows, press Windows keys -> All apps -> PostgreSQL 14 -> click on SQL Shell (psql), as shown below. This will launch SQL Shell (psql) command-line interface, as shown below. First, you need to enter the PostgreSQL server name. if it is on your localhost then press Enter.


3 Answers

For Unix environnement the command line is

psql -U USERNAME -h localhost dbname

For a Windows environment, you may consider replacing "-" with "/"

-U option able you to choose a user to connect with

-h option able you to connect with the TCPIP protocol, you may consider it useless for Windows

like image 166
Charkan Avatar answered Oct 17 '22 23:10

Charkan


First make sure your user have a sudo access if not you can use the below command to add your user as sudo user :-

sudo adduser <username> sudo

The change will take effect the next time the user logs in.

Now try running this command :-

sudo -u postgres psql

if that gives you error follow the below steps it should work.

i) Now go to sudo vim /etc/postgresql/<your_postgres_version>/main/pg_hba.conf file and look for line that says :

local   all             postgres                                md5 #peer

and comment that. Just below that line there must be a commented line that says:

local   all             postgres                                peer

or for older versions it'll be :-

local   all         postgres                          ident

Uncomment that line.

ii) Now restart the postgres by using any of these commands :-

sudo /etc/init.d/postgresql restart

OR

sudo service postgresql restart

iii) Now you can simply log into postgres using the following command :

sudo -u postgres psql

iv) once you're in you can create any operation you want to in my case i wanted to create a new database you can do the same using below command :

CREATE DATABASE airflow_replica;
like image 28
officialrahulmandal Avatar answered Oct 18 '22 00:10

officialrahulmandal


psql -h <hostname> -U <username> -d <dbname>

If all params are correct then it will ask you for db password for username entered earlier.

like image 45
Jasmeet Singh Avatar answered Oct 17 '22 23:10

Jasmeet Singh