Mercurial > lada > lada-server

annotate db_schema/setup-db.sh @ 928:6ab17d8b5558

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
erase mpr_id values in lada_data.sql
author Michael Stanko <mstanko@bfs.de>
date Wed, 04 May 2016 08:07:31 +0200
parents 2b746f4e97d6
children d3d9cd5e18f8
rev   line source
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 #!/bin/sh
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 DIR=`dirname $0`
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
4 ROLE_NAME=${1:-lada}
928
6ab17d8b5558 erase mpr_id values in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 925
diff changeset
5 echo "DROLE_NAME = $ROLE_NAME"
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
6 ROLE_PW=${2:-$ROLE_NAME}
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
7 echo "ROLE_PW = $ROLE_PW"
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
8 DB_NAME=${3:-$ROLE_NAME}
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
9 echo "DB_NAME = $DB_NAME"
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
11 # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
12 if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
13 if [ -n "$DB_SRV" -a -n "$DB_PORT" ] ; then
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
14 DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
15 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
16 DB_CONNECT_STRING="$DB_CONNECT_STRING -U postgres"
928
6ab17d8b5558 erase mpr_id values in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 925
diff changeset
17 echo "DB_CONNECT_STRING = $DB_CONNECT_STRING"
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
18
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
19 if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
20 echo create user $ROLE_NAME
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
21 psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
22 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
23
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
24 if psql -h test-pgsql1-fr.lab.bfs.de -U postgres -l | grep -q "^ $DB_NAME " ; then
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
25 echo drop db $DB_NAME
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
26 psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
27 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
28
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
29 echo create db $DB_NAME
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
30 psql $DB_CONNECT_STRING --command \
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
31 "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
32
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
33 echo create postgis extention
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
34 psql $DB_CONNECT_STRING -d $DB_NAME --command \
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
35 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public"
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
36
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
37 echo create stammdaten schema
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
38 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
39
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
40 echo create lada schema
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
41 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
42 echo set grants
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
43 psql $DB_CONNECT_STRING -d $DB_NAME --command \
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 "GRANT USAGE ON SCHEMA stammdaten, bund, land TO $ROLE_NAME;
764
9d2070b84238 Hibernate needs nextval to insert new datasets.
Tom Gottfried <tom@intevation.de>
parents: 752
diff changeset
45 GRANT USAGE
752
0cf9387c8da2 Added grant to all sequences.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 742
diff changeset
46 ON ALL SEQUENCES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 ON ALL TABLES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;"
908
51051e85348f Added data dumps.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 866
diff changeset
49
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
50 echo import stammdaten
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
51 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
52
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
53 echo import lada test data
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
54 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)