Automating install and creation of postgresql databases in shell

Question

I am trying to create two databases called spider and geo under postgresql via an automated shell script. This is the code so far.

apt-get install -y postgresql
echo "CREATE ROLE deploy LOGIN ENCRYPTED PASSWORD '$APP_DB_PASS';" | sudo -u postgres psql
su postgres -c "createdb spider --owner deploy"
su postgres -c "createdb geo --owner deploy"
/etc/init.d/postgresql reload

Can anyone please have a look and see if I am going about this the right way. Moreover, when I try to see if it works by running the following command I get an error:

root:~# psql -l                                                                               
psql: FATAL:  role "root" does not exist

Where have I gone wrong, and is there any way to improve this script?

Shouldn't the "--owner deploy" be "--owner=deploy"?

bma
– bma

2013-08-20 21:19:20 +00:00
Commented Aug 20, 2013 at 21:19 — bma
– bma, Commented Aug 20, 2013 at 21:19

Kirk Roybal · Accepted Answer · 2013-08-20 22:14:37Z

4

Judging by the apt-get, your deployment platform is Ubuntu-(ish).

apt-get install -y postgresql
echo "CREATE ROLE deploy LOGIN ENCRYPTED PASSWORD '$APP_DB_PASS';" | sudo -u postgres psql
su postgres -c "createdb spider --owner deploy"
su postgres -c "createdb geo --owner deploy"
service postgresql reload

Then you should be able to log in by specifying a user on the command line:

psql -U root spider

or

psql -U deploy spider

Generally speaking, you're on the right track.

answered Aug 20, 2013 at 22:14

Kirk Roybal

18k2 gold badges34 silver badges39 bronze badges

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

1 Comment

Fredrick Over a year ago

I had better luck with this for the the command line: sudo -u postgres psql -c "create database spider --owner deploy"

Collectives™ on Stack Overflow

Automating install and creation of postgresql databases in shell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related