SCSArchiver CSStudio Postgres Create Database And Scheme
- database_owner_ship provided:
- add
client_encoding and SEARCH PATH
- if database does not exist, create it
- call
pqsl with
- option
"-f" loading modified partitioning
- option
"-a" to show all
%CODE{"bash"}%
export PG_Owner=<database_owner, e.g. "postgres">
export PG_Archiver=
export PG_Reporter=
export PG_Archiver_pw=''
export PG_Reporter_pw=''
export PGUSER=<schema_owner, e.g. "archiver">
export PGHOST=cbmdcs01
export PGPORT=5432
export PGDATABASE=archive
schema=archive;
outputdir=$(mktemp -d);
outputfile=$(mktemp -p ${outputdir}) ;
cat > ${outputfile} << EOF
---
---
SET client_encoding = "UTF-8";
create database
DO $$
BEGIN
IF NOT EXISTS( SELECT datname from pg_catalog.pg_database where datname = \'$PGDATABASE\' )
THEN
EXECUTE \'CREATE DATABASE $PGDATABASE;\'
END IF;
END
$$;
\connect $PGDATABASE;
create roles / users
DO $$
BEGIN
IF NOT EXISTS( SELECT usename from pg_user where usename = \'$PG_Archiver\' )
THEN
EXECUTE \'CREATE USER $PG_Archiver with PASSWORD \'$PG_Archiver_pw\'\';
EXECUTE \'ALTER USER $PG_Archiver with PASSWORD \'$PG_Archiver_pw\'\';
END IF;
IF NOT EXISTS( SELECT usename from pg_user where usename = \'$PG_Reporter\' )
THEN
EXECUTE \'CREATE USER $PG_Reporter with PASSWORD \'$PG_Reporter_pw\'\';
EXECUTE \'ALTER USER $PG_Reporter with PASSWORD \'$PG_Reporter_pw\'\';
END IF;
END
$$;
-- create scheme
DO $$
BEGIN
IF NOT EXISTS( SELECT schema_name FROM information_schema.schemata WHERE schema_name = \'$schema\' )
THEN
EXECUTE \'CREATE SCHEMA $schema\';
EXECUTE \'GRANT USAGE on SCHEMA $schema to $PGUSER\';
END IF;
END
$$;
--
SET SEARCH_PATH TO "$schema";
EOF
cd ${outputdir} && unset PGDATABASE &&
psql -U PG_Owner -a -f ${outputfile} &&
rm -ir ${outputdir} && cd -
%ENDCODE%
-- PeterZumbruch - 25 Oct 2016