I need to execute the following sql queries from bash/expect script what is the preferred approach to run these queries from bash script
x
# 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
Advertisement
Answer
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