SCSArchiver CSStudio Postgres Create Database And Scheme

  • database_owner_ship provided:
    1. add client_encoding and SEARCH PATH
    2. if database does not exist, create it
    3. call pqsl with
      • option "-f" loading modified partitioning
      • option "-a" to show all

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 -


-- PeterZumbruch - 25 Oct 2016
Topic revision: r3 - 04 Nov 2016, PeterZumbruch
 
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Hades Wiki? Send feedback
Imprint (in German)
Privacy Policy (in German)