Mercurial > dive4elements > river

annotate backend/doc/schema/postgresql-setup.sh @ 9802:a9641367812c 3.2.x

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve locale support in artifacts container Makes it easier to run scripts e.g. for importing data.
author Tom Gottfried <tom@intevation.de>
date Wed, 29 May 2024 10:51:00 +0200
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