Create PostgreSQL database (with optional PostGIS support) on Ubuntu (assuming you have PostgreSQL 9.1+ installed), typically for use with Django.

create_psql_db.sh

#!/bin/bash

DATABASE_NAME=#####
DATABASE_OWNER=#####
USE_POSTGIS=N

if [ ! `which psql` ]; then
    echo 'You must have PostgreSQL installed before running this script.'
    exit 1
fi

PSQL_VERSION=`psql -V | grep -i postgresql | awk '{print $3}' | sed -r 's/([0-9]\.[0-9]).*$/\1/'`

if [ `id -u` == 0 ]; then
    echo -n "Where is your pg_hba.conf file? [/etc/postgresql/${PSQL_VERSION}/main/pg_hba.conf] "
    read HBAFILE

    [ -z $HBAFILE ] && HBAFILE=/etc/postgresql/${PSQL_VERSION}/main/pg_hba.conf

    if [ `ls $HBAFILE` ]; then
        EXISTS=`sed -n '/local.*all.*all.*md5/p' $HBAFILE`
        if [[ "$EXISTS" != "" ]]; then
            sed -i '/^local.*postgres.*$/alocal\tall\t\tall\t\t\t\tmd5' $HBAFILE
            service postgresql restart || /etc/init.d/postgresql restart
        fi
    else
        echo 'File not found.'
        exit 3
    fi

else
    echo 'You must run this script as root.'
    exit 2
fi

echo "Creating new user account '${DATABASE_OWNER}'..."
createuser -DeEPR -U postgres -h localhost -p 5432 $DATABASE_OWNER
echo "Creating new database '${DATABASE_NAME}'..."
createdb $DATABASE_NAME --owner $DATABASE_OWNER --encoding UNICODE --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -T template_postgis -U postgres -h localhost -p 5432

# PostGIS
if [ $? != 0 ]; then
    if [ $USE_POSTGIS == 'Y' ]; then
        echo -n "Where is your postgis.sql script? [/usr/share/postgresql/${PSQL_VERSION}/contrib/postgis-1.5/postgis.sql] "
        read POSTGIS
    
        [ -z $POSTGIS ] && POSTGIS=/usr/share/postgresql/${PSQL_VERSION}/contrib/postgis-1.5/postgis.sql
        SPATIAL=`echo $POSTGIS | sed 's/postgis\.sql/spatial_ref_sys.sql/'`
    
        if [ `ls $POSTGIS` ]; then
            echo "Creating new PostGIS database template..."
            createdb -U postgres -h localhost -p 5432 template_postgis
            createlang -U postgres -h localhost -p 5432 plpgsql template_postgis
            echo "Enabling PostGIS in database..."
            psql -U postgres -h localhost -p 5432 -d template_postgis -f $POSTGIS
            psql -U postgres -h localhost -p 5432 -d template_postgis -f $SPATIAL
    
            # Try again
            echo "Creating new PostGIS-enabled database '${DATABASE_NAME}'..."
            createdb $DATABASE_NAME --owner $DATABASE_OWNER --encoding UNICODE --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -T template_postgis -U postgres -h localhost -p 5432
    
            if [ $? != 0 ]; then
                echo 'There was a problem creating your database. Please check the output and try again. It is safe to run this script again.'
                exit 5
            fi
        else
            echo 'File not found.'
            exit 4
        fi
    else
        echo "First attempt to create database failed. Trying again..."
        createdb $DATABASE_NAME --owner $DATABASE_OWNER --encoding UNICODE --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 -U postgres -h localhost -p 5432
        if [ $? != 0 ]; then
            echo 'There was a problem creating your database. Please check the output and try again. It is safe to run this script again.'
            exit 5
        fi
    fi
fi