Postgresql – Running the .sql script in Linux to get the postgresql database table and functions

linuxpostgresql

I am using Red hat Centos 7. When I use this command to run the .sql script to get postgres database table and fucntion:

$sudo psql -U postgres -d tsdb -W -f /.sqlscript/location
It asks for postgres password and when i put password and hit enters it says 
psql: FATAL: Peer authentication failed for user "postgres"

What could be my error? How do I fix the error so that I can successfully run the script to restore table and function and all the data in postgresql.

This is my pg_hba.conf. Is there anything that need to be changed in this file? 

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                               peer
#host    replication     postgres        127.0.0.1/32           ident
#host    replication     postgres        ::1/128                ident

Best Answer

You are trying to run the psql as the OS user 'root'. Your pg_hba.conf file doesn't let OS user 'root' log in as database user 'postgres'.

You could instead use sudo to run as OS user 'postgres', not 'root':

sudo -u postgres psql -U postgres -d tsdb -f /.sqlscript/location

(Although perhaps OS user 'postgres' won't have access to the sqlscript)

You could also make use of a mapping file pg_ident.conf to say that root is allowed to log in as postgres, although I don't see much point in that.

Or you could change the "local" line of your pg_hba.conf so that it uses "md5" rather than "peer". At that point, there is no reason to use "sudo" at all as it doesn't care who you are, just that you know the password.

Or you could make use of the existing 'host ... md5' line of your pg_hba.conf, by specifying a host rather than defaulting to using a unix socket:

psql -h 127.0.0.1 -U postgres -d tsdb -W -f /.sqlscript/location
Related Question