Project

General

Profile

1 11496 psarando
#!/bin/bash
2
3
# Bash script to create a new postgis database and prep it with GADM
4
# data for geovalidation purposes.
5
#
6
# Basic workflow:
7
#  1. Create postgis-enabled template db
8
#  2. Create postgis-enabled db from the template
9
#  3. Create required tables in postgis-enabled db
10
#  4. Load gadm2 and geonames.org data into database
11
#
12
# For postgis installation see:
13
#   http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204
14
#
15
# Jim Regetz
16
# NCEAS
17
# Created November 2012
18
#
19
# refactored and reorganized by
20
# Paul Sarando
21
# iPlant Collaborative
22
# Updated Oct 2013
23
24
DB_NAME="geoscrub"
25
DB_USER="bien"
26 11558 psarando
DB_HOST_OPT=""
27 11496 psarando
SCRIPT_DIR="$(dirname $0)"
28
29 11559 psarando
function usage {
30
    echo "Usage: $0 [OPTIONS]" >&2
31
    echo "Valid Options:" >&2
32
    echo "-d, --dbname=DBNAME      database name psql commands will connect to" >&2
33
    echo "-h, --host=HOSTNAME      database server host or socket directory" >&2
34
    echo "-U, --username=USERNAME  database user name" >&2
35
    exit 1;
36
}
37
38
while [[ $# -gt 0  ]]; do
39
    case "$1" in
40
        -\? | --help)
41
            usage
42
            ;;
43
        -h)
44
            if [[ -z $2  ]];  then
45
                echo "Option $1 requires an argument." >&2
46
                usage
47
            fi
48
            DB_HOST_OPT="-h $2"
49
            shift 2
50
            ;;
51
        --host=*)
52
            DB_HOST_OPT="-h ${1#*=}"
53
            shift
54
            ;;
55
        -U)
56
            if [[ -z $2  ]];  then
57
                echo "Option $1 requires an argument." >&2
58
                usage
59
            fi
60
            DB_USER="$2"
61
            shift 2
62
            ;;
63
        --username=*)
64
            DB_USER="${1#*=}"
65
            shift
66
            ;;
67
        -d)
68
            if [[ -z $2  ]];  then
69
                echo "Option $1 requires an argument." >&2
70
                usage
71
            fi
72
            DB_NAME="$2"
73
            shift 2
74
            ;;
75
        --dbname=*)
76
            DB_NAME="${1#*=}"
77
            shift
78
            ;;
79
        *)
80
            echo "Invalid option: $1" >&2
81
            usage
82
            ;;
83
    esac
84
done
85
86 11496 psarando
function run_sudo_sql_cmd {
87
    local SQL_CMD="$1"
88
89 11558 psarando
    sudo -u postgres psql -e $DB_HOST_OPT -d "$DB_NAME" -c "$SQL_CMD"
90 11496 psarando
    if [[ $? != 0 ]]; then
91
        echo "Error while executing SQL command."
92
        echo -n "You may drop the ${DB_NAME} database"
93
        echo " and attempt to run this $0 script again."
94
        exit 1
95
    fi
96
}
97
98
function run_sql_script {
99
    local SCRIPT="$1"
100
101 11558 psarando
    psql -e --set ON_ERROR_STOP=1 -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" < "$SCRIPT"
102 11496 psarando
    if [[ $? != 0 ]]; then
103
        echo "Error while executing SQL script ${SCRIPT}"
104
        exit 1
105
    fi
106
}
107
108
# create postgis template if one doesn't already exist
109
echo "Setting up template_postgis database..."
110
sudo -u postgres createdb template_postgis
111
if [[ $? == 0 ]]; then
112
    # successfully created template_postgis, which means it didn't already exist
113
    sudo -u postgres psql -e -d template_postgis < "${SCRIPT_DIR}"/update.template_postgis.sql
114
    if [[ $? != 0 ]]; then
115
        echo "Could not create postgis extesion on template_postgis database."
116
        exit 1
117
    fi
118
fi
119
120
# create postgis db from template
121
echo "Creating the ${DB_NAME} database..."
122 11558 psarando
createdb --no-password -U "$DB_USER" $DB_HOST_OPT "$DB_NAME" -T template_postgis
123 11496 psarando
if [[ $? != 0 ]]; then
124
    echo "Could not create ${DB_NAME} database."
125
    exit 1
126
fi
127
128 11558 psarando
echo "Updating ${DB_NAME} table permissions..."
129 11496 psarando
run_sudo_sql_cmd "ALTER TABLE spatial_ref_sys OWNER TO ${DB_USER}"
130
run_sudo_sql_cmd "ALTER VIEW geography_columns OWNER TO ${DB_USER}"
131
run_sudo_sql_cmd "ALTER VIEW geometry_columns OWNER TO ${DB_USER}"
132
133
echo "Setting up ${DB_NAME} database tables..."
134 11555 psarando
run_sql_script "${SCRIPT_DIR}"/setup.geonames.sql
135
run_sql_script "${SCRIPT_DIR}"/setup.geonames-to-gadm.sql
136
run_sql_script "${SCRIPT_DIR}"/setup.geoscrub.sql
137 11496 psarando
138
echo "Install successfully completed."