CODEWITHSUNDEEP
bashpostgresqlubuntu
Rahul
Rahul

Reputation: 11550

(Learning Bash) How to run multiple postgresql command from bash file

This is what I am doing manually.

user@host: sudo -u postgres psql
postgres=# create database myproj;
postgres=# create user myuser with password 'mypass';
postgres=# alter role myuser set client_encoding to 'utf8';
postgres=# alter role myuser set default_transaction_isolation to 'read committed';
postgres=# alter role myuser set timezone to 'UTC';
postgres=# grant all privileges on database myproj to myuser;
postgres=# \q

The question is if I want to save it to .sh file and run, what should be the content. something like this?

sudo -u postgres psql -c "create database myproj;"
sudo -u postgres psql -c "create user myuser with password 'mypass';"
sudo -u postgres psql -c "alter role myuser set client_encoding to 'utf8';"
sudo -u postgres psql -c "alter role myuser set default_transaction_isolation to 'read committed';"
sudo -u postgres psql -c "alter role myuser set timezone to 'UTC';"
sudo -u postgres psql -c "grant all privileges on database myproj to myuser;"

Upvotes: 0

Views: 423

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

You may use a Here document

$cat yourscript.sh
sudo -u postgres psql <<'INP'
create database myproj;
create user myuser with password 'mypass';
..
..
INP

If you wish to isolate the SQL commands from shell, put it in a separate .sql file and call using -f option or psql \i option.

$cat dbscript.sql
     create database myproj;
     create user myuser with password 'mypass';
..

Run it as

sudo -u postgres psql -U <yourusername> -d <yourdbname> -f dbscript.sql

Or

sudo -u postgres psql <<INP
\i /path/dbscript.sql

Upvotes: 1

Related Questions