0

I am having troubles getting started with psql. I can login using the script below

myusername@ubuntu:~/Desktop/dbscripts$ sudo su - postgres
postgres@ubuntu:~$

But, once here, I cannot figure out how to find my .sql file. I tried the options supplied by Bolo here: How to import existing *.sql files in PostgreSQL 8.4? But they only give

myusername@ubuntu:~/Desktop/dbscripts$ psql -U root -d first -f myscript.sql
psql: FATAL:  Peer authentication failed for user "root"

and 

myusername@ubuntu:~/Desktop/dbscripts$ psql -f myscript.sqlp
sql: FATAL:  role "myusername" does not exist

and 

myusername@ubuntu:~/Desktop/dbscripts$ sudo su - postgres
postgres@ubuntu:~$ \i myscript.sql
The program 'i' is currently not installed. To run 'i' please ask your administrator to install the package 'iprint'
Community
  • 1
  • 1
Rorschach
  • 3,684
  • 7
  • 33
  • 77
  • 1
    This can be handled from any of the above options you tried. In which path do you have myscript.sql? After you do su - postgres, give the full path . So it will be psql -d first -f /myscript.sql. psql -U root will not work unless you have a user root in the database. Try psql -U postgres. You can do "\i sqlscript" at psql prompt, not at linux command prompt as you have done. The error you are getting "role "myusername" does not exist" can be avoided either by using -U postgres (or any other db user) or by setting the PGUSER environment variable. – Jayadevan Jul 24 '15 at 17:06
  • Thank you @jayadevan. The myscript.sql is in the ~/Desktop/dbscripts folder, but I can't find where my desktop folder is when I get into the postges@ubuntu prompt. – Rorschach Jul 24 '15 at 17:11
  • Nevermind. I just went down all the way to root and worked my way back up. Give me a minute to try your suggestions. – Rorschach Jul 24 '15 at 17:12
  • That got me going on the right track and works. Thank you for the very complete answer. If you make it into a Answer I will accept it. – Rorschach Jul 24 '15 at 17:39
  • Done. Posted it as answer. – Jayadevan Jul 25 '15 at 06:02
  • 1
    `-U` specifies the **database** user to be used by `psql` so you should use `psql -U postgres` –  Jul 25 '15 at 07:10

1 Answers1

1

This can be handled from any of the above options you tried. In which path do you have myscript.sql? After you do su - postgres, give the full path . So it will be psql -d first -f <pathtosqlfile>/myscript.sql. psql -U root will not work unless you have a user root in the database. Try psql -U postgres. You can do \i sqlscript at psql prompt, not at linux command prompt as you have done. The error you are getting "role "myusername" does not exist" can be avoided either by using -U postgres (or any other db user) or by setting the PGUSER environment variable.

Jayadevan
  • 1,306
  • 2
  • 12
  • 33