You are here: Foswiki>DaqSlowControl Web>HadesDaqDocumentation>DetectorControlSystem>SCSArchiverCSStudio>SCSArchiverCSStudioConfigurationScripts>SCSArchiverCSStudioPostgresCreateDatabaseAndScheme (2016-11-04, PeterZumbruch)Edit Attach

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

%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
Edit  | Attach | Print version | History: r3 < r2 < r1 | Backlinks | View wiki text | Edit wiki text | More topic actions
Topic revision: r3 - 2016-11-04, PeterZumbruch
Copyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki Send feedback | Imprint | Privacy Policy (in German)