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?

Answer 1

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.