Execute sql queries from shell script

Question

I need to execute the following sql queries from bash/expect script what is the preferred approach to run these queries from bash script

# psql ambari -U ambari
Password for user ambari:
psql (9.2.24)
Type "help" for help.

ambari=>
ambari=>
ambari=>
ambari=> select
ambari-> sum(case when ulo = 1  then 1 else 0 end) as ulo_1,
ambari-> sum(case when ulo = 2  then 1 else 0 end) as ulo_2,

.
.
.

for access PostgreSQL we do

psql ambari -U ambari
Password for user ambari:bigdata

and when we run this ( /tmp/file include the bach of the query )

 psql -U ambari -f /tmp/file  ambari

we get

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "ambari", database "ambari", SSL off

Ivan · Accepted Answer · 2020-03-12 06:49:26Z

2

I'm using this

dbhost=localhost
dbport=5432
dbuser=user
dbpass=pass
dbname=test
export PGPASSWORD="$dbpass"
dbopts="-h $dbhost -p $dbport -U $dbuser -d $dbname"

Then run sql script from file

psql $dbopts < "$path_to_sql_script"

Or from query var

query="
SELECT 1;
...
"
psql $dbopts <<< "$query"

Also pgpass can be set in special file ~/.pgpass like this

echo "$dbhost:$dbport:$dbname:$dbname:$dbpass" > ~/.pgpass
chmod 600 ~/.pgpass

edited Mar 12, 2020 at 6:49

answered Mar 12, 2020 at 6:37

Ivan

7,5011 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jessica Over a year ago

how to know the - dbport?

Ivan Over a year ago

Use default 5432.

jessica Over a year ago

also where I can found the - dbname?

James Brown · Accepted Answer · 2020-03-12 07:07:26Z

Use switches -c command or -f filename, ie.:

$ psql -U ambari -c "SELECT ... ;" ambari    # > result.file

or:

$ cat file.sql
SELECT
... ;
$ psql -U ambari -f file.sql ambari          # > result.file

Probably -f as your query seems lengthy. Use > result.file to store the query result to a file.

As for the password, store following kind of entry to .pgpass file in user's home dir:

$ cat >> ~/.pgpass
#hostname:port:database:username:password
localhost:5432:ambari:ambari:t00M4NY53CR3t5

and set its rights to user's eyes only:

$ chmod 600 ~/.pgpass

Also, consider psql -h hostname if the database is not running in localhost (this needs to reflect in .pgpass entry as well).

see please the update , we run as you suggest but I think we need also to consider the password

Collectives™ on Stack Overflow

Execute sql queries from shell script

2 Answers 2

3 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Related