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

Question

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;"

Kaushik Nayak · Accepted Answer · 2019-07-24 07:35:00Z

1

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

answered Jul 24, 2019 at 7:35

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

4 Comments

Rahul Over a year ago

I can not save sql file so I am trying first approach. I have prepared the .sh file at pastebin. If possible can you please check if I understood your answer correctly?

Rahul Over a year ago

The first and last cd commands are just to understand that this is part of large .sh file. Is this ok?

Kaushik Nayak Over a year ago

@Rahul : The cd command's not needed. Rest seems Ok. Please verify it in your test environment before running.

Rahul Over a year ago

cd commands are there to confirm that this is part of larger .sh file and can work that way.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related