Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-setup.sh @ 9801:1d7a72a50183 3.2.x tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Assume Compose V2, consistently
author Tom Gottfried <tom@intevation.de>
date Thu, 23 Nov 2023 10:14:13 +0100
parents 358a0fd48a00
children f89fb9e9abad
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
http://dive4elements.wald.intevation.org