SCSArchiver CSStudio Postgres Create Database And Scheme

export PG_Owner=<database_owner, e.g. "postgres">
export PG_Archiver=<archiver user, e.g. "archiver" >
export PG_Reporter=<reporter user, e.g. "report">
export PG_Archiver_pw='<archiver pw, e.g. "$archive">' 
export PG_Reporter_pw='<reporter pw, e.g. "$report">' 
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 -