Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-setup.sh @ 9415:9744ce3c3853

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rework of fixanalysis computation and dWt and WQ facets. Got rid of strange remapping and bitshifting code by explicitely saving the column information and using it in the facets. The facets also put the valid station range into their xml-metadata
author gernotbelger
date Thu, 16 Aug 2018 16:27:53 +0200
parents f89fb9e9abad
children
rev   line source
5284
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 #!/bin/bash
5287
a7e8e9bbe6f5 postgresql-setup.sh: Doc improved.
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents: 5284
diff changeset
2
8794
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
3 # $1: name, user and password for new DB (optional. Default: d4e)
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
4 # $2: host (optional. Default: localhost)
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
5
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
6 SCRIPT_DIR=`dirname $0`
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
7 DB_NAME=${1:-d4e}
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
8 PG_HOST=${2:-localhost}
5284
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 # run as user postgres (postgresql super-user)
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 # it is assumed that the owner of the DB has the same name as the DB!
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 # create PostGIS-DB
8794
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
14 createuser -S -D -R $DB_NAME
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
15 createdb $DB_NAME
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
16
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
17 psql -d $DB_NAME -c "ALTER USER $DB_NAME WITH PASSWORD '$DB_NAME';"
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
18
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
19 psql -d $DB_NAME -c "CREATE EXTENSION postgis;"
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
20 psql -d $DB_NAME -c "GRANT ALL ON geometry_columns TO $DB_NAME;
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
21 GRANT ALL ON geography_columns TO $DB_NAME;
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
22 GRANT ALL ON spatial_ref_sys TO $DB_NAME;"
5284
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 # add credentials to .pgpass (or create .pgpass)
8794
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
25 echo "*:*:$DB_NAME:$DB_NAME:$DB_NAME" >> ~/.pgpass
5284
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 chmod 0600 ~/.pgpass
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 # apply schema-scripts
8794
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
29 psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql.sql
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
30 psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql-spatial.sql
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
31 psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql-minfo.sql
8965
f89fb9e9abad Datatype changes from rev 8942 reverted
mschaefer
parents: 8796
diff changeset
32 psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql-sinfo-uinfo.sql
http://dive4elements.wald.intevation.org