Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-setup.sh @ 8794:c1cea6c95967

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve Postgres-DB setup. We depend on 9.1 (having CREATE EXTENSION) since a while.
author Tom Gottfried <tom@intevation.de>
date Thu, 10 Dec 2015 17:40:02 +0100
parents 4ad2b530dbba
children 358a0fd48a00
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 createlang plpgsql $DB_NAME
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
17
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
18 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
19
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
20 psql -d $DB_NAME -c "CREATE EXTENSION postgis;"
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
21 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
22 GRANT ALL ON geography_columns TO $DB_NAME;
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
23 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
24
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 # add credentials to .pgpass (or create .pgpass)
8794
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
26 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
27 chmod 0600 ~/.pgpass
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28
4e79267c3af8 added script to setup postgres-DB for testing
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 # apply schema-scripts
8794
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.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-spatial.sql
c1cea6c95967 Improve Postgres-DB setup.
Tom Gottfried <tom@intevation.de>
parents: 6818
diff changeset
32 psql -d $DB_NAME -U $DB_NAME -h $PG_HOST -f $SCRIPT_DIR/postgresql-minfo.sql
http://dive4elements.wald.intevation.org