RDB Archiver based on CS-Studio

Detailed Table of Content

Introduction


(from http://cs-studio.sourceforge.net/docbook/archive.png)
The CS-Studio RDB Archiver Engine archives EPICS Process Variables into an RDB (PostgreSQL, MySQL, Oracle). Stored data can be retrieved via CS-Studio RDB Archive Reader or any other SQL aware application.

Documents


Setup / Installation

Needs:
  • Database configuration / optional installation
  • Archiver Engine configuration

Database

PostgreSQL hosted by GSI central services


GSI PostgreSQL administrative contact: T.Badura@gsi.de
Role/User/Permissions
Hostname Port pgsqltest.gsi.de 6846
Database hades_scs_css_archive   css_ee
      permissions     permissions
  user/role   read write admin     read write admin
pzumbruch   choice-yes choice-yes choice-yes     choice-yes choice-yes choice-yes
"p.zumbruch"   choice-yes choice-yes choice-yes     choice-yes choice-yes choice-yes
epics           pw: m7Z6a1B7g2 choice-yes choice-no choice-no
hades_scs_css_archive_read pw: k2E4g7D1c8 choice-yes choice-no choice-no          
hades_scs_css_archive   choice-yes choice-yes choice-no          
hades_scs_css_archive_admin   choice-yes choice-yes choice-yes          
"m.traxler"   choice-yes choice-yes choice-yes          
"j.adamczewski-musch"   choice-yes choice-yes choice-yes          

Configuration
Hostname pgsqltest.gsi.de
Port 6846
Name hades_scs_css_archive css_ee
SSL  
based on CS-Studio provided postgres scheme (raw) (local) and CS-Studio provided partitioning (raw) (local)

Configuration - Load Schema
hades_scs_css_archive

Hostname
pgsqltest.gsi.de
Port
6846
Database
hades_scs_css_archive
Schema
hades_scs_css_archive
Scripts
    Create Database, Users, 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=%pgOwner%
    export PG_Archiver=%userArchiver%
    export PG_Reporter=%userReport%
    export PG_Archiver_pw='%pwArchiver%' 
    export PG_Reporter_pw='%pwReport%' 
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=%host% 
    export PGPORT=6846 
    export PGDATABASE=hades_scs_css_archive 
    schema=hades_scs_css_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 -

    Load Scheme

    1. Download postgres_schema.txt from cs-studio@github
    2. Since GSI already created the database, skip DROP DATABASE, CREATE DATABASE
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export PGUSER=<database_owner> 
    export PGHOST=pgsqltest.gsi.de 
    export PGPORT=6846 
    export PGDATABASE=hades_scs_css_archive 
    schema=hades_scs_css_archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    grep -v "DROP DATABASE" ${filename} | 
    grep -v "CREATE DATABASE" | 
    grep -v -w "\\connect" >> ${outputfile} && 
    psql  -a  -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

      optionally: to speed up in favour of neglecting consistency, you could drop the FOREIGN KEY constraints of the sample table:

      export PGUSER=<admin_user> 
      export PGHOST=pgsqltest.gsi.de 
      export PGPORT=6846 
      export PGDATABASE=hades_scs_css_archive 
      schema=hades_scs_css_archive
      outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
      cat > ${outputfile} << EOF
      --- 
      --- 
      SET client_encoding = "UTF-8"; 
      SET SEARCH_PATH TO "$schema";
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_status_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_severity_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_channel_id_fkey" CASCADE;
      EOF 
      cd ${outputdir} &&
      psql  -a  -f ${outputfile} &&
      rm -ir ${outputdir} && cd -

    Partitioning

    NOTE: alternatively, if applicable pg_partman Extension to manage partitioned tables by time or ID - not yet tested

    • database_owner_ship provided:
      1. Download postgres_partitioning.txt from cs-studio@github
      2. Since the table sample is already created, optionally including FOREIGN KEY constraints,
        • skip DROP TABLE IF EXISTS sample, though you will run into an error
      3. replace archive. prefixes by "$schema." prefix
      4. add client_encoding and SEARCH PATH in front
      5. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all

    export PG_Owner=<database_owner, e.g. "postgres">
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=pgsqltest.gsi.de 
    export PGPORT=6846 
    export PGDATABASE=hades_scs_css_archive 
    schema=hades_scs_css_archive
    URL="https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_partitioning.txt" 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    perl -p -e "s/\sarchive\./ $schema./g;" -e "s/OWNER TO postgres;/OWNER TO \"${PGUSER:?PGUSER undefined}\";/g;" -e "s/DROP TABLE IF EXISTS sample;.*?$//g;" ${filename} >> ${outputfile} &&
    echo -e "\nSELECT hades_scs_css_archive.sample_update_partitions('2015-01-01'::timestamp, 'hades_scs_css_archive', '$PGUSER', 'week');" >> ${outputfile} &&
    psql -U ${PG_Owner} -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

    Note
    if psql -U $(PG_Owner) does not work, unset PGHOST;

    crontab :
    -- This maintenance function automatically creates partitions according to the
    -- specified interval (e.g. weekly or monthly). The first partition starts at
    -- <begin_time> and ends a day/week/month/year later. This function has to be
    -- called regularly (e.g. daily by cron):
    --
    --   @daily psql  -a -h pgsqltest.gsi.de -p 6846 -U <database_owner, e.g. "postgres"> -d hades_scs_css_archive -c "SELECT hades_scs_css_archive.sample_update_partitions('2015-01-01'::timestamp, 'hades_scs_css_archive', '<database_owner, e.g. "postgres">', 'week');"
    --
    -- This function is based on a more generic version by Nicholas Whittier
    -- (http://imperialwicket.com/postgresql-automating-monthly-table-partitions).

    Install a cronjob on any relevant machine using .pgpass (q.v. Psql pgwiki.gsi.de #.pgpass)
    (german) note about the credentials
    ... da Sie vorhaben, "psql" für Ihren CronJob zu nutzen, können Sie alle relevanten PostgreSQL-Connection-Daten in Shell-Variablen hinterlegen. Im pgWiki finden Sie dazu eine Anleitung / Empfehlung von uns, wie so ein PostgreSQL-Environment aussehen könnte. Es gibt auch die Datei ".pgpass, wo Sie Passwörter für Ihre Verbindungen hinterlegen können. 1x hinterlegt, bezieht "psql" standardmäßig aus dieser Datei die passenden Passwörter. Wichtig ist nur, dass CronJob unter dem User ausgeführt wird, bei dem die Datei .pgpass hinterlegt ist.
    Sie können Ihr Environment sogar soweit "vorbereiten", dass Sie sogar den DBNamen, den Ufernamen, Port, Server, etc. bei Ihrem Cron-Aufruf nicht mehr mit angeben müssen. Die Environment-Variablen geben "psql" vor, wie und wohin er sich per Standard connecten soll.

    Grants

    1. Expect postgres_schema.txt
    2. Extract GRANT commands and for each user add those GRANTs
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export archive=<(list of) archiver_user(s)>)>
    export report=<(list of) report_user(s)>)>
    export PGUSER=<database_owner> 
    export PGHOST=pgsqltest.gsi.de 
    export PGPORT=6846 
    export PGDATABASE=hades_scs_css_archive 
    schema=hades_scs_css_archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    for item in $(echo $archive);
    do
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
    done
    for item in $(echo $report);
    do 
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
    done
    psql  -a -f $outputfile &&
    cd - &&
    rm -ir ${outputdir} &&
    unset outputdir outputfile archive report 


css_ee

Hostname
pgsqltest.gsi.de
Port
6846
Database
css_ee
Schema
css_ee
Scripts
    Create Database, Users, 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=%pgOwner%
    export PG_Archiver=%userArchiver%
    export PG_Reporter=%userReport%
    export PG_Archiver_pw='%pwArchiver%' 
    export PG_Reporter_pw='%pwReport%' 
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=%host% 
    export PGPORT=6846 
    export PGDATABASE=css_ee 
    schema=css_ee
    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 -

    Load Scheme

    1. Download postgres_schema.txt from cs-studio@github
    2. Since GSI already created the database, skip DROP DATABASE, CREATE DATABASE
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export PGUSER=<database_owner> 
    export PGHOST=pgsqltest.gsi.de 
    export PGPORT=6846 
    export PGDATABASE=css_ee 
    schema=css_ee
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    grep -v "DROP DATABASE" ${filename} | 
    grep -v "CREATE DATABASE" | 
    grep -v -w "\\connect" >> ${outputfile} && 
    psql  -a  -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

      optionally: to speed up in favour of neglecting consistency, you could drop the FOREIGN KEY constraints of the sample table:

      export PGUSER=<admin_user> 
      export PGHOST=pgsqltest.gsi.de 
      export PGPORT=6846 
      export PGDATABASE=css_ee 
      schema=css_ee
      outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
      cat > ${outputfile} << EOF
      --- 
      --- 
      SET client_encoding = "UTF-8"; 
      SET SEARCH_PATH TO "$schema";
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_status_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_severity_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_channel_id_fkey" CASCADE;
      EOF 
      cd ${outputdir} &&
      psql  -a  -f ${outputfile} &&
      rm -ir ${outputdir} && cd -

    Partitioning

    NOTE: alternatively, if applicable pg_partman Extension to manage partitioned tables by time or ID - not yet tested

    • database_owner_ship provided:
      1. Download postgres_partitioning.txt from cs-studio@github
      2. Since the table sample is already created, optionally including FOREIGN KEY constraints,
        • skip DROP TABLE IF EXISTS sample, though you will run into an error
      3. replace archive. prefixes by "$schema." prefix
      4. add client_encoding and SEARCH PATH in front
      5. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all

    export PG_Owner=<database_owner, e.g. "postgres">
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=pgsqltest.gsi.de 
    export PGPORT=6846 
    export PGDATABASE=css_ee 
    schema=css_ee
    URL="https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_partitioning.txt" 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    perl -p -e "s/\sarchive\./ $schema./g;" -e "s/OWNER TO postgres;/OWNER TO \"${PGUSER:?PGUSER undefined}\";/g;" -e "s/DROP TABLE IF EXISTS sample;.*?$//g;" ${filename} >> ${outputfile} &&
    echo -e "\nSELECT css_ee.sample_update_partitions('2015-01-01'::timestamp, 'css_ee', '$PGUSER', 'week');" >> ${outputfile} &&
    psql -U ${PG_Owner} -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

    Note
    if psql -U $(PG_Owner) does not work, unset PGHOST;

    crontab :
    -- This maintenance function automatically creates partitions according to the
    -- specified interval (e.g. weekly or monthly). The first partition starts at
    -- <begin_time> and ends a day/week/month/year later. This function has to be
    -- called regularly (e.g. daily by cron):
    --
    --   @daily psql  -a -h pgsqltest.gsi.de -p 6846 -U <database_owner, e.g. "postgres"> -d css_ee -c "SELECT css_ee.sample_update_partitions('2015-01-01'::timestamp, 'css_ee', '<database_owner, e.g. "postgres">', 'week');"
    --
    -- This function is based on a more generic version by Nicholas Whittier
    -- (http://imperialwicket.com/postgresql-automating-monthly-table-partitions).

    Install a cronjob on any relevant machine using .pgpass (q.v. Psql pgwiki.gsi.de #.pgpass)
    (german) note about the credentials
    ... da Sie vorhaben, "psql" für Ihren CronJob zu nutzen, können Sie alle relevanten PostgreSQL-Connection-Daten in Shell-Variablen hinterlegen. Im pgWiki finden Sie dazu eine Anleitung / Empfehlung von uns, wie so ein PostgreSQL-Environment aussehen könnte. Es gibt auch die Datei ".pgpass, wo Sie Passwörter für Ihre Verbindungen hinterlegen können. 1x hinterlegt, bezieht "psql" standardmäßig aus dieser Datei die passenden Passwörter. Wichtig ist nur, dass CronJob unter dem User ausgeführt wird, bei dem die Datei .pgpass hinterlegt ist.
    Sie können Ihr Environment sogar soweit "vorbereiten", dass Sie sogar den DBNamen, den Ufernamen, Port, Server, etc. bei Ihrem Cron-Aufruf nicht mehr mit angeben müssen. Die Environment-Variablen geben "psql" vor, wie und wohin er sich per Standard connecten soll.

    Grants

    1. Expect postgres_schema.txt
    2. Extract GRANT commands and for each user add those GRANTs
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export archive=<(list of) archiver_user(s)>)>
    export report=<(list of) report_user(s)>)>
    export PGUSER=<database_owner> 
    export PGHOST=pgsqltest.gsi.de 
    export PGPORT=6846 
    export PGDATABASE=css_ee 
    schema=css_ee
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    for item in $(echo $archive);
    do
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
    done
    for item in $(echo $report);
    do 
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
    done
    psql  -a -f $outputfile &&
    cd - &&
    rm -ir ${outputdir} &&
    unset outputdir outputfile archive report 



PostgreSQL hosted "locally" w/ option for NOSSL


Installation

Install
  • Debian
$ apt-get install postgresql postgresql-client 

fully prepared: Git Repository
branch=9.6 && 
sudo su -c "cp -r /etc/postgresql /tmp/postgresql && 
for item in $(ls -A /etc/postgresql/* | xargs); do rm -r /etc/postgresql/$item; done &&
git clone --branch ${branch:?} https://git.gsi.de/HADES/DCS/EPICS/Services/Archiver/Postgres-Server-Configuration /etc/postgresql &&
chown postgres:postgres -R /etc/postgresql"

by-hand: Server Connectivity

in: /etc/postgresql/9.6/main/postgresql.conf

change
listen_addresses = 'localhost'          

to
listen_addresses = '*'          
ls

and add in /etc/postgresql/9.6/main/pg_hba.conf
hostnossl        all               all          $ip     trust
hostnossl        all               all          140.181.75.165/18     trust
hostnossl        all               all          140.181.80.125/18     trust
hostnossl        all               all          140.181.93.18/18      trust
hostnossl        all               archiver     samehost              trust
hostnossl        all               report       samenet               trust

in: /etc/postgresql/9.6/main/postgresql.conf
the data directory is set to:
data_directory = '/var/lib/postgresql/9.6/main'         # use data in another directory

should be changed to a bigger directory:
data_directory = '/data.postgres/postgresql/9.6/main'         # use data in another directory

start/restart
# service postgresql restart; service postgresql status


Configuration lxhadeb05 (current)

Database Configuration - lxhadeb05 (current)
Hostname lxhadeb05
Port 5432
Name archive
schema public
SSL  
based on CS-Studio provided postgres scheme (raw) (local) and CS-Studio provided partitioning (raw) (local)

Role/User/Permissions - lxhadeb05 (current)
Hostname Port lxhadeb05:5432
Database archive
      permissions
  user/role   read write admin
archiver pw: $archive choice-yes choice-yes choice-no
report pw: $report choice-yes choice-no choice-no
postgres superuser choice-yes choice-yes choice-yes

Configuration Scripts - lxhadeb05 (current)
public.archive@lxhadeb05:5432
Hostname
lxhadeb05
Port
5432
Database
archive
Schema
public
Scripts
    Create Database, Users, 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=archiver
    export PG_Archiver=archiver
    export PG_Reporter=report
    export PG_Archiver_pw='$archive' 
    export PG_Reporter_pw='$report' 
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=public
    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 -

    Load Scheme

    1. Download postgres_schema.txt from cs-studio@github
    2. Since GSI already created the database, skip DROP DATABASE, CREATE DATABASE
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export PGUSER=<database_owner> 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=public
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    grep -v "DROP DATABASE" ${filename} | 
    grep -v "CREATE DATABASE" | 
    grep -v -w "\\connect" >> ${outputfile} && 
    psql  -a  -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

      optionally: to speed up in favour of neglecting consistency, you could drop the FOREIGN KEY constraints of the sample table:

      export PGUSER=<admin_user> 
      export PGHOST=lxhadeb05 
      export PGPORT=5432 
      export PGDATABASE=archive 
      schema=public
      outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
      cat > ${outputfile} << EOF
      --- 
      --- 
      SET client_encoding = "UTF-8"; 
      SET SEARCH_PATH TO "$schema";
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_status_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_severity_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_channel_id_fkey" CASCADE;
      EOF 
      cd ${outputdir} &&
      psql  -a  -f ${outputfile} &&
      rm -ir ${outputdir} && cd -

    Partitioning

    NOTE: alternatively, if applicable pg_partman Extension to manage partitioned tables by time or ID - not yet tested

    • database_owner_ship provided:
      1. Download postgres_partitioning.txt from cs-studio@github
      2. Since the table sample is already created, optionally including FOREIGN KEY constraints,
        • skip DROP TABLE IF EXISTS sample, though you will run into an error
      3. replace archive. prefixes by "$schema." prefix
      4. add client_encoding and SEARCH PATH in front
      5. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all

    export PG_Owner=archiver
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=public
    URL="https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_partitioning.txt" 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    perl -p -e "s/\sarchive\./ $schema./g;" -e "s/OWNER TO postgres;/OWNER TO \"${PGUSER:?PGUSER undefined}\";/g;" -e "s/DROP TABLE IF EXISTS sample;.*?$//g;" ${filename} >> ${outputfile} &&
    echo -e "\nSELECT public.sample_update_partitions('2015-01-01'::timestamp, 'public', '$PGUSER', 'week');" >> ${outputfile} &&
    psql -U ${PG_Owner} -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

    Note
    if psql -U $(PG_Owner) does not work, unset PGHOST;

    crontab :
    -- This maintenance function automatically creates partitions according to the
    -- specified interval (e.g. weekly or monthly). The first partition starts at
    -- <begin_time> and ends a day/week/month/year later. This function has to be
    -- called regularly (e.g. daily by cron):
    --
    --   @daily psql  -a -h lxhadeb05 -p 5432 -U archiver -d archive -c "SELECT public.sample_update_partitions('2015-01-01'::timestamp, 'public', 'archiver', 'week');"
    --
    -- This function is based on a more generic version by Nicholas Whittier
    -- (http://imperialwicket.com/postgresql-automating-monthly-table-partitions).

    Install a cronjob on any relevant machine using .pgpass (q.v. Psql pgwiki.gsi.de #.pgpass)
    (german) note about the credentials
    ... da Sie vorhaben, "psql" für Ihren CronJob zu nutzen, können Sie alle relevanten PostgreSQL-Connection-Daten in Shell-Variablen hinterlegen. Im pgWiki finden Sie dazu eine Anleitung / Empfehlung von uns, wie so ein PostgreSQL-Environment aussehen könnte. Es gibt auch die Datei ".pgpass, wo Sie Passwörter für Ihre Verbindungen hinterlegen können. 1x hinterlegt, bezieht "psql" standardmäßig aus dieser Datei die passenden Passwörter. Wichtig ist nur, dass CronJob unter dem User ausgeführt wird, bei dem die Datei .pgpass hinterlegt ist.
    Sie können Ihr Environment sogar soweit "vorbereiten", dass Sie sogar den DBNamen, den Ufernamen, Port, Server, etc. bei Ihrem Cron-Aufruf nicht mehr mit angeben müssen. Die Environment-Variablen geben "psql" vor, wie und wohin er sich per Standard connecten soll.

    Grants

    1. Expect postgres_schema.txt
    2. Extract GRANT commands and for each user add those GRANTs
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export archive=<(list of) archiver_user(s)>)>
    export report=<(list of) report_user(s)>)>
    export PGUSER=<database_owner> 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=public
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    for item in $(echo $archive);
    do
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
    done
    for item in $(echo $report);
    do 
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
    done
    psql  -a -f $outputfile &&
    cd - &&
    rm -ir ${outputdir} &&
    unset outputdir outputfile archive report 




Configuration lxhadeb05 (future)

Database Configuration - lxhadeb05 (future)
Hostname lxhadeb05
Port 5432
Name archive
schema archive
SSL  
based on CS-Studio provided postgres scheme (raw) (local) and CS-Studio provided partitioning (raw) (local)

Role/User/Permissions - lxhadeb05 (future)
Hostname Port lxhadeb05:5432
Database archive
      permissions
  user/role   read write admin
archiver pw: $archive choice-yes choice-yes choice-no
report pw: $report choice-yes choice-no choice-no
postgres superuser choice-yes choice-yes choice-yes

Configuration Scripts - lxhadeb05 (future)
archive.archive@lxhadeb05:5432
Hostname
lxhadeb05
Port
5432
Database
archive
Schema
archive
Scripts
    Create Database, Users, 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=postgres
    export PG_Archiver=archiver
    export PG_Reporter=report
    export PG_Archiver_pw='$archive' 
    export PG_Reporter_pw='$report' 
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=lxhadeb05 
    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 -

    Load Scheme

    1. Download postgres_schema.txt from cs-studio@github
    2. Since GSI already created the database, skip DROP DATABASE, CREATE DATABASE
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export PGUSER=<database_owner> 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    grep -v "DROP DATABASE" ${filename} | 
    grep -v "CREATE DATABASE" | 
    grep -v -w "\\connect" >> ${outputfile} && 
    psql  -a  -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

      optionally: to speed up in favour of neglecting consistency, you could drop the FOREIGN KEY constraints of the sample table:

      export PGUSER=<admin_user> 
      export PGHOST=lxhadeb05 
      export PGPORT=5432 
      export PGDATABASE=archive 
      schema=archive
      outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
      cat > ${outputfile} << EOF
      --- 
      --- 
      SET client_encoding = "UTF-8"; 
      SET SEARCH_PATH TO "$schema";
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_status_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_severity_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_channel_id_fkey" CASCADE;
      EOF 
      cd ${outputdir} &&
      psql  -a  -f ${outputfile} &&
      rm -ir ${outputdir} && cd -

    Partitioning

    NOTE: alternatively, if applicable pg_partman Extension to manage partitioned tables by time or ID - not yet tested

    • database_owner_ship provided:
      1. Download postgres_partitioning.txt from cs-studio@github
      2. Since the table sample is already created, optionally including FOREIGN KEY constraints,
        • skip DROP TABLE IF EXISTS sample, though you will run into an error
      3. replace archive. prefixes by "$schema." prefix
      4. add client_encoding and SEARCH PATH in front
      5. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all

    export PG_Owner=postgres
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL="https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_partitioning.txt" 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    perl -p -e "s/\sarchive\./ $schema./g;" -e "s/OWNER TO postgres;/OWNER TO \"${PGUSER:?PGUSER undefined}\";/g;" -e "s/DROP TABLE IF EXISTS sample;.*?$//g;" ${filename} >> ${outputfile} &&
    echo -e "\nSELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', '$PGUSER', 'week');" >> ${outputfile} &&
    psql -U ${PG_Owner} -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

    Note
    if psql -U $(PG_Owner) does not work, unset PGHOST;

    crontab :
    -- This maintenance function automatically creates partitions according to the
    -- specified interval (e.g. weekly or monthly). The first partition starts at
    -- <begin_time> and ends a day/week/month/year later. This function has to be
    -- called regularly (e.g. daily by cron):
    --
    --   @daily psql  -a -h lxhadeb05 -p 5432 -U postgres -d archive -c "SELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', 'postgres', 'week');"
    --
    -- This function is based on a more generic version by Nicholas Whittier
    -- (http://imperialwicket.com/postgresql-automating-monthly-table-partitions).

    Install a cronjob on any relevant machine using .pgpass (q.v. Psql pgwiki.gsi.de #.pgpass)
    (german) note about the credentials
    ... da Sie vorhaben, "psql" für Ihren CronJob zu nutzen, können Sie alle relevanten PostgreSQL-Connection-Daten in Shell-Variablen hinterlegen. Im pgWiki finden Sie dazu eine Anleitung / Empfehlung von uns, wie so ein PostgreSQL-Environment aussehen könnte. Es gibt auch die Datei ".pgpass, wo Sie Passwörter für Ihre Verbindungen hinterlegen können. 1x hinterlegt, bezieht "psql" standardmäßig aus dieser Datei die passenden Passwörter. Wichtig ist nur, dass CronJob unter dem User ausgeführt wird, bei dem die Datei .pgpass hinterlegt ist.
    Sie können Ihr Environment sogar soweit "vorbereiten", dass Sie sogar den DBNamen, den Ufernamen, Port, Server, etc. bei Ihrem Cron-Aufruf nicht mehr mit angeben müssen. Die Environment-Variablen geben "psql" vor, wie und wohin er sich per Standard connecten soll.

    Grants

    1. Expect postgres_schema.txt
    2. Extract GRANT commands and for each user add those GRANTs
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export archive=<(list of) archiver_user(s)>)>
    export report=<(list of) report_user(s)>)>
    export PGUSER=<database_owner> 
    export PGHOST=lxhadeb05 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    for item in $(echo $archive);
    do
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
    done
    for item in $(echo $report);
    do 
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
    done
    psql  -a -f $outputfile &&
    cd - &&
    rm -ir ${outputdir} &&
    unset outputdir outputfile archive report 




Configuration depc429

Database Configuration - depc429
Hostname depc429
Port 5432
Name archive
schema archive
SSL  
based on CS-Studio provided postgres scheme (raw) (local) and CS-Studio provided partitioning (raw) (local)

Role/User/Permissions - depc429
Hostname Port depc429:5432
Database archive
      permissions
  user/role   read write admin
archiver pw: $archive choice-yes choice-yes choice-no
report pw: $report choice-yes choice-no choice-no
postgres superuser choice-yes choice-yes choice-yes

Configuration Scripts - depc429
archive.archive@depc429:5432
Hostname
depc429
Port
5432
Database
archive
Schema
archive
Scripts
    Create Database, Users, 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=postgres
    export PG_Archiver=archiver
    export PG_Reporter=report
    export PG_Archiver_pw='$archive' 
    export PG_Reporter_pw='$report' 
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=depc429 
    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 -

    Load Scheme

    1. Download postgres_schema.txt from cs-studio@github
    2. Since GSI already created the database, skip DROP DATABASE, CREATE DATABASE
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export PGUSER=<database_owner> 
    export PGHOST=depc429 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    grep -v "DROP DATABASE" ${filename} | 
    grep -v "CREATE DATABASE" | 
    grep -v -w "\\connect" >> ${outputfile} && 
    psql  -a  -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

      optionally: to speed up in favour of neglecting consistency, you could drop the FOREIGN KEY constraints of the sample table:

      export PGUSER=<admin_user> 
      export PGHOST=depc429 
      export PGPORT=5432 
      export PGDATABASE=archive 
      schema=archive
      outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
      cat > ${outputfile} << EOF
      --- 
      --- 
      SET client_encoding = "UTF-8"; 
      SET SEARCH_PATH TO "$schema";
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_status_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_severity_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_channel_id_fkey" CASCADE;
      EOF 
      cd ${outputdir} &&
      psql  -a  -f ${outputfile} &&
      rm -ir ${outputdir} && cd -

    Partitioning

    NOTE: alternatively, if applicable pg_partman Extension to manage partitioned tables by time or ID - not yet tested

    • database_owner_ship provided:
      1. Download postgres_partitioning.txt from cs-studio@github
      2. Since the table sample is already created, optionally including FOREIGN KEY constraints,
        • skip DROP TABLE IF EXISTS sample, though you will run into an error
      3. replace archive. prefixes by "$schema." prefix
      4. add client_encoding and SEARCH PATH in front
      5. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all

    export PG_Owner=postgres
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=depc429 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL="https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_partitioning.txt" 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    perl -p -e "s/\sarchive\./ $schema./g;" -e "s/OWNER TO postgres;/OWNER TO \"${PGUSER:?PGUSER undefined}\";/g;" -e "s/DROP TABLE IF EXISTS sample;.*?$//g;" ${filename} >> ${outputfile} &&
    echo -e "\nSELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', '$PGUSER', 'week');" >> ${outputfile} &&
    psql -U ${PG_Owner} -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

    Note
    if psql -U $(PG_Owner) does not work, unset PGHOST;

    crontab :
    -- This maintenance function automatically creates partitions according to the
    -- specified interval (e.g. weekly or monthly). The first partition starts at
    -- <begin_time> and ends a day/week/month/year later. This function has to be
    -- called regularly (e.g. daily by cron):
    --
    --   @daily psql  -a -h depc429 -p 5432 -U postgres -d archive -c "SELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', 'postgres', 'week');"
    --
    -- This function is based on a more generic version by Nicholas Whittier
    -- (http://imperialwicket.com/postgresql-automating-monthly-table-partitions).

    Install a cronjob on any relevant machine using .pgpass (q.v. Psql pgwiki.gsi.de #.pgpass)
    (german) note about the credentials
    ... da Sie vorhaben, "psql" für Ihren CronJob zu nutzen, können Sie alle relevanten PostgreSQL-Connection-Daten in Shell-Variablen hinterlegen. Im pgWiki finden Sie dazu eine Anleitung / Empfehlung von uns, wie so ein PostgreSQL-Environment aussehen könnte. Es gibt auch die Datei ".pgpass, wo Sie Passwörter für Ihre Verbindungen hinterlegen können. 1x hinterlegt, bezieht "psql" standardmäßig aus dieser Datei die passenden Passwörter. Wichtig ist nur, dass CronJob unter dem User ausgeführt wird, bei dem die Datei .pgpass hinterlegt ist.
    Sie können Ihr Environment sogar soweit "vorbereiten", dass Sie sogar den DBNamen, den Ufernamen, Port, Server, etc. bei Ihrem Cron-Aufruf nicht mehr mit angeben müssen. Die Environment-Variablen geben "psql" vor, wie und wohin er sich per Standard connecten soll.

    Grants

    1. Expect postgres_schema.txt
    2. Extract GRANT commands and for each user add those GRANTs
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export archive=<(list of) archiver_user(s)>)>
    export report=<(list of) report_user(s)>)>
    export PGUSER=<database_owner> 
    export PGHOST=depc429 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    for item in $(echo $archive);
    do
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
    done
    for item in $(echo $report);
    do 
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
    done
    psql  -a -f $outputfile &&
    cd - &&
    rm -ir ${outputdir} &&
    unset outputdir outputfile archive report 




Configuration cbmdcs01

Database Configuration - cbmdcs01
Hostname cbmdcs01
Port 5432
Name archive
schema archive
SSL  
based on CS-Studio provided postgres scheme (raw) (local) and CS-Studio provided partitioning (raw) (local)

Role/User/Permissions - cbmdcs01
Hostname Port cbmdcs01:5432
Database archive
      permissions
  user/role   read write admin
archiver pw: $archive choice-yes choice-yes choice-no
report pw: $report choice-yes choice-no choice-no
postgres superuser choice-yes choice-yes choice-yes

Configuration Scripts - cbmdcs01
archive.archive@cbmdcs01:5432
Hostname
cbmdcs01
Port
5432
Database
archive
Schema
archive
Scripts
    Create Database, Users, 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=postgres
    export PG_Archiver=archiver
    export PG_Reporter=report
    export PG_Archiver_pw='$archive' 
    export PG_Reporter_pw='$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 -

    Load Scheme

    1. Download postgres_schema.txt from cs-studio@github
    2. Since GSI already created the database, skip DROP DATABASE, CREATE DATABASE
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export PGUSER=<database_owner> 
    export PGHOST=cbmdcs01 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    grep -v "DROP DATABASE" ${filename} | 
    grep -v "CREATE DATABASE" | 
    grep -v -w "\\connect" >> ${outputfile} && 
    psql  -a  -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

      optionally: to speed up in favour of neglecting consistency, you could drop the FOREIGN KEY constraints of the sample table:

      export PGUSER=<admin_user> 
      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"; 
      SET SEARCH_PATH TO "$schema";
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_status_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_severity_id_fkey" CASCADE;
      ALTER TABLE sample DROP CONSTRAINT IF EXISTS "sample_channel_id_fkey" CASCADE;
      EOF 
      cd ${outputdir} &&
      psql  -a  -f ${outputfile} &&
      rm -ir ${outputdir} && cd -

    Partitioning

    NOTE: alternatively, if applicable pg_partman Extension to manage partitioned tables by time or ID - not yet tested

    • database_owner_ship provided:
      1. Download postgres_partitioning.txt from cs-studio@github
      2. Since the table sample is already created, optionally including FOREIGN KEY constraints,
        • skip DROP TABLE IF EXISTS sample, though you will run into an error
      3. replace archive. prefixes by "$schema." prefix
      4. add client_encoding and SEARCH PATH in front
      5. call pqsl with
        • option "-f" loading modified partitioning
        • option "-a" to show all

    export PG_Owner=postgres
    export PGUSER=<schema_owner, e.g. "archiver"> 
    export PGHOST=cbmdcs01 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL="https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_partitioning.txt" 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH_PATH TO "$schema";
    EOF
    
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    perl -p -e "s/\sarchive\./ $schema./g;" -e "s/OWNER TO postgres;/OWNER TO \"${PGUSER:?PGUSER undefined}\";/g;" -e "s/DROP TABLE IF EXISTS sample;.*?$//g;" ${filename} >> ${outputfile} &&
    echo -e "\nSELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', '$PGUSER', 'week');" >> ${outputfile} &&
    psql -U ${PG_Owner} -a -f ${outputfile} &&
    rm -ir ${outputdir} && cd -

    Note
    if psql -U $(PG_Owner) does not work, unset PGHOST;

    crontab :
    -- This maintenance function automatically creates partitions according to the
    -- specified interval (e.g. weekly or monthly). The first partition starts at
    -- <begin_time> and ends a day/week/month/year later. This function has to be
    -- called regularly (e.g. daily by cron):
    --
    --   @daily psql  -a -h cbmdcs01 -p 5432 -U postgres -d archive -c "SELECT archive.sample_update_partitions('2015-01-01'::timestamp, 'archive', 'postgres', 'week');"
    --
    -- This function is based on a more generic version by Nicholas Whittier
    -- (http://imperialwicket.com/postgresql-automating-monthly-table-partitions).

    Install a cronjob on any relevant machine using .pgpass (q.v. Psql pgwiki.gsi.de #.pgpass)
    (german) note about the credentials
    ... da Sie vorhaben, "psql" für Ihren CronJob zu nutzen, können Sie alle relevanten PostgreSQL-Connection-Daten in Shell-Variablen hinterlegen. Im pgWiki finden Sie dazu eine Anleitung / Empfehlung von uns, wie so ein PostgreSQL-Environment aussehen könnte. Es gibt auch die Datei ".pgpass, wo Sie Passwörter für Ihre Verbindungen hinterlegen können. 1x hinterlegt, bezieht "psql" standardmäßig aus dieser Datei die passenden Passwörter. Wichtig ist nur, dass CronJob unter dem User ausgeführt wird, bei dem die Datei .pgpass hinterlegt ist.
    Sie können Ihr Environment sogar soweit "vorbereiten", dass Sie sogar den DBNamen, den Ufernamen, Port, Server, etc. bei Ihrem Cron-Aufruf nicht mehr mit angeben müssen. Die Environment-Variablen geben "psql" vor, wie und wohin er sich per Standard connecten soll.

    Grants

    1. Expect postgres_schema.txt
    2. Extract GRANT commands and for each user add those GRANTs
    3. add client_encoding and "SEARCH PATH" in front
    4. call pqsl with
      • option "-f" loading modified schema
      • option "-a" to show all

    export archive=<(list of) archiver_user(s)>)>
    export report=<(list of) report_user(s)>)>
    export PGUSER=<database_owner> 
    export PGHOST=cbmdcs01 
    export PGPORT=5432 
    export PGDATABASE=archive 
    schema=archive
    URL=https://github.com/ControlSystemStudio/cs-studio/raw/master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd/postgres_schema.txt 
    filename=$(basename $URL ) 
    outputdir=$(mktemp -d); 
    outputfile=$(mktemp -p ${outputdir}) ; 
    cat > ${outputfile} << EOF
    --- 
    --- 
    SET client_encoding = "UTF-8"; 
    SET SEARCH PATH TO "$schema";
    EOF
    cd ${outputdir} &&
    wget -N --no-check-certificate ${URL} &&
    for item in $(echo $archive);
    do
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
    done
    for item in $(echo $report);
    do 
    perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
    done
    psql  -a -f $outputfile &&
    cd - &&
    rm -ir ${outputdir} &&
    unset outputdir outputfile archive report 





CS-Studio - RDB Archiver

Currently located at lxhadeb06

Requirements

Sources download

Current versions can be build on its own based on the GIT hub repository of CS-Studio: Prebuilt binaries can be found at https://ics-web.sns.ornl.gov/css/updates/apps/ Look for:
  • archive_config...
  • archvie_engine...
Currently used at HADES are those:
  • archive-config-4.1.0-linux.gtk.x86_64.zip : local copy
  • archive-engine-4.1.0-linux.gtk.x86_64.zip : local copy


File locations

Install Area
  • /home/epics/EPICS/programs/css/...
    • directory read-only to group and others
    • modifications on <Application>.ini file to allow multiuser operation "shared configuration"/"shared install"
  • archive-config-4.1.0
-Dosgi.configuration.area.default=@user.home/.css/ArchiveConfigTool_3.2.15/configuration
-Dosgi.instance.area.default=@user.home/CSS-Workspaces/ArchiveConfigTool_3.2.15/workspace

  • archive-engine-4.1.0
-Dosgi.configuration.area.default=@user.home/.css/ArchiveEngine_4.0.0/configuration
-Dosgi.instance.area.default=@user.home/CSS-Workspaces/ArchiveEngine_4.0.0/workspace

  • /home/epics/EPICS/bin/
    • Caller Scripts to provide correct java version of Oracle (java version "1.8.0_66", Sun/Oracle):
#!/bin/bash
export JAVA_HOME=/home/epics/EPICS/programs/java/jdk1.8.0_66;
declare -F addToPath && addToPath $JAVA_HOME/bin || PATH=$(echo $JAVA_HOME/bin${PATH:+:$PATH}  | sed -e 's/^://' -e 's/:$//')
export PATH

/home/epics/EPICS/programs/css/archive-config-4.1.0/ArchiveConfigTool $*

#!/bin/bash
export JAVA_HOME=/home/epics/EPICS/programs/java/jdk1.8.0_66;
declare -F addToPath && addToPath $JAVA_HOME/bin || PATH=$(echo $JAVA_HOME/bin${PATH:+:$PATH}  | sed -e 's/^://' -e 's/:$//')
export PATH

/home/epics/EPICS/programs/css/archive-engine-4.1.0/ArchiveEngine $*


Configuration Area
can be changed by runtime option
-configuration <location>
default
~/.css/ArchiveEngine_4.0.0/configuration
~/.css/ArchiveConfigTool_3.2.15/configuration

Instance Area
can be changed by runtime option
-data <location>
default
~/CSS-Workspaces/ArchiveEngine_4.0.0/workspace
~/CSS-Workspaces/ArchiveConfigTool_3.2.15/workspace



CS-Studio - Database credentials

There are several ways to provide the database passwords (archiving (write), retrieving (read):
  • every time via command line option :
    -rdb_password <plain text password>
  • every time via pluginCustomization :
    • command line option :
      -pluginCustomization <file>
    • where <file> contains the line:
      org.csstudio.archive.rdb/password=<plain text password>
  • one time command line option :
    -set_password org.csstudio.archive.rdb/password
    setting secure preferences interactively and storing it in secure_store.dat depending on the settings of org.csstudio.security/secure_preference_location in the (pluginCustomization) settings file (c.f Section "Secure Preferences", and Configuring Authentication, Authorization and Secure Preferences of "Chapter 13. Authentication and Authorization Part I. CS-Studio Guide" ):
    • "Default": Use the default location, which is typically the user's home directory.
      • Advantage: It's the default
      • Disadvantage: You won't always know where the preferences are.
    • "Instance": Use the Eclipse 'instance', i.e. the workspace.
      • Advantage: You know where it is, and each workspace will have its own settings.
      • Disadvantage: Each workspace has different settings.
    • "Install": Use the Eclipse 'install' location, i.e. where the product is installed.
      • Advantage: You know where it is, and every workspace for that product will have the same settings. Good for a "global" setup.
      • Disadvantage: Ordinary users cannot (should not) have write permissions.

Status

At the moment the encrypted credentials for hades_scs_css_archive@pgsqltest.gsi.de:6846/hades_scs_css_archive are stored at the install location of the config tool or the engine respectively. Logged with installation credentials (e.g. user epics), either of these will set it:
$> su - epics 
$> . epics &&
ArchiveConfigTool -rdb_user hades_scs_css_archive  -set_password org.csstudio.archive.rdb/password &&
ArchiveEngineCSS  -set_password org.csstudio.archive.rdb/password



GIT repository



Engine Configuration

  1. definitions
  2. convert old archiver "cfg" into new "XML"
  3. import configuration into configuration database
  4. list/check configurations
  5. goto operations

Code Repositories

CVS

  • GSI Linux Cluster
    CVSROOT
    /misc/hadesprojects/slowcontrol/cvsroot
    ==Module
    Archiver.css

git



Directory

APPS_TOP=~scs/playground/EPICS/apps
#APPS_TOP=~scs/apps
ARCHIVER_CSS_TOP=$APPS_TOP/Archiver.css
cd $ARCHIVER_CSS_TOP/configuration


Converting old archiver 'cfg' into new 'xml' configuration files

The configuration directory of the css archiver at $APPS_TOP/Archiver/css/configuration/old contains as a starting point the copy of the archiver configuration of the old archiver. By using the old/ConvertEngineConfig.pl script (which is a copy of the latest old archiver tool set) you could generate an xml configuration file:

ConvertEngineConfig.pl -v -d old/engineconfig.dtd -o hadesArchive.xml hadesArchive.cfg

or alternatively the wrapper script convert.sh which cares about existing files
old/convert.sh [[input:default=hadesArchive.cfg] output]


Import / Modify Engine Configuration Database

The XML output file of the conversion is used by ArchiveConfigTool to generate/modify the configuration of the archive engine, which is also stored in the database.
TIP pluginCustomization
to ease common rdb a plugin customization settings file is used, see plugin customization

hades_scs_css_archive

pluginCustomization=$ARCHIVER_CSS_TOP/pluginCustomization/settings_hades_scs_css_archive.ini

engine_configuration_file=hadesArchive_css.xml
engine_name=HadesArchiveEngine
engine_port=4912
engine_description=HADES_Archiver
engine_replace=yes
engine_steal_channels=yes

[ $engine_replace == "yes" ] && engine_replace=-replace_engine || engine_replace=""
[ $engine_steal_channels == "yes" ] && engine_steal_channels=-steal_channels || engine_steal_channels=""

#import
cd $ARCHIVER_CSS_TOP/configuration && [ -f $pluginCustomization ] && [ -f "$engine_configuration_file" ] && 
ArchiveConfigTool -pluginCustomization $pluginCustomization -import -engine $engine_name -port $engine_port -config $engine_configuration_file -description $engine_description $engine_replace 

lxhadeb05_archive

pluginCustomization=$ARCHIVER_CSS_TOP/pluginCustomization/settings_lxhadeb05_archive.ini

engine_configuration_file=hadesArchive_css.xml
engine_name=HadesArchiveEngine
engine_port=4912
engine_description=HADES_Archiver
engine_replace=yes
engine_steal_channels=yes

[ $engine_replace == "yes" ] && engine_replace=-replace_engine || engine_replace=""
[ $engine_steal_channels == "yes" ] && engine_steal_channels=-steal_channels || engine_steal_channels=""

#import
cd $ARCHIVER_CSS_TOP/configuration && [ -f $pluginCustomization ] && [ -f "$engine_configuration_file" ] && 
ArchiveConfigTool -pluginCustomization $pluginCustomization -import -engine $engine_name -port $engine_port -config $engine_configuration_file -description $engine_description $engine_replace 


List existing engine configurations

pluginCustomization=$ARCHIVER_CSS_TOP/pluginCustomization/settings_hades_scs_css_archive.ini
ArchiveConfigTool -pluginCustomization $pluginCustomization -list




Operation

Start Engine

by Hand

engine_name=HadesArchiveEngine
engine_port=4912
pluginCustomization=$ARCHIVER_CSS_TOP/pluginCustomization/settings_hades_scs_css_archive.ini

ArchiveEngineCSS -port $engine_port -engine $engine_name -data workspace/mdc  -pluginCustomization $pluginCustomization

A running Engine will have an HTTP server at e.g. http://localhost:4912/main an will look like this:
ArchiveEngineWebInterface.png

ALERT! Make sure the engine port is the same as defined in the configuration, otherwise you might get something like this - (mismatch of defined 4812 (default) and requested 4912):
2016-06-02 15:56:51.010 INFO [Thread 1] org.csstudio.archive.engine.Application (start) - Archive Engine 4.0.0.201506301920
2016-06-02 15:56:51.233 INFO [Thread 1] org.csstudio.archive.engine.server.EngineServer (<init>) - Engine HTTP Server on http://localhost:4912/main
2016-06-02 15:56:51.233 INFO [Thread 1] org.csstudio.archive.engine.Application (start) - Reading configuration 'HadesArchiveEngine'
2016-06-02 15:56:51.858 SEVERE [Thread 1] org.csstudio.archive.engine.Application (start) - Cannot read configuration
java.lang.Exception: Engine running on port 4912 while configuration requires http://localhost:4812/main

using crontab / procServ

On the basis of the "by Hand" a suitable cronjob is set up: At ~scs/procServ the file archiverCSS.sh handles the configuration of the start routine. Its function userDefines holds the important informations, change it if necessary, and update it to the code revision.
#specific user predefines
#the userDefines function is called within the $startBackgroundEpicsIocScriptName script
userDefines()
{
userProcServTelnetPort=4853
userProcServTelnetLogPort=-1
userIOC=archiverCSS-BEAUTY-$(hostname)
#	userWorkingDir=~/apps/Archiver/css/startup
userWorkingDir=~/playground/EPICS/apps/Archiver.css/startup
userCommandExecutable=startEngineCSS.sh
userCommandArgument=

The command crontab -l should contain the following line of code:
@reboot cd ~scs/procServ && ./archiverCSS.sh > ~/log/archiverCSS.crontab.start 2>&1

With those scripts a procServ session with port 4853 is created, which can be locally reached and manipulated using telnet (see procServ man pages):
telnet localhost 4813

Restarting Engine

via web browser

The RDB Channel Archiver offers the opportunity to restart the engine by simple calling its restart web page: http://localhost:4912/restart

script based using curl

Download this small bash script https://git.gsi.de/EPICS/Tools/scripts/Archiver/restartChannelArchiverArchiveEngine

via procServ - "brute force"

Login to the procServ session and kill/restart the child process with ^X.

Attach CSS Browser

lxhadeb05

Settings needed in CSS preferences (restart needed) Just look at the line starting with jdbc:
  • Archiver preference settings 1:
    Archiver_preferences_settings_1.png
  • URL: jdbc:postgresql://lxhadeb05:5432/archive

You must not enter a password:
  • Archiver preference settings 2:
    Archiver_preferences_settings_2.png
  • User: report
Example:
  • Archiver CSS Browser:
    Archiver_Example.png


Appendix:

pluginCustomization

with e.g. ~scs/playground/EPICS/apps/Archiver/css/pluginCustomization/settings_hades_scs_css_archive.ini
org.csstudio.archive.rdb/url=jdbc:postgresql://pgsqltest.gsi.de:6846/hades_scs_css_archive
org.csstudio.archive.rdb/user=hades_scs_css_archive
org.csstudio.archive.rdb/schema=
org.csstudio.security/secure_preference_location=Instance


-- PeterZumbruch - 16 Jan 2018
-- PeterZumbruch - 17 Sep 2019

Topic attachments
I Attachment Action Size Date Who Comment
ArchiveConfigToolEXT ArchiveConfigTool manage 0.3 K 17 Jun 2016 - 11:41 PeterZumbruch caller script for ArchiveConfig
ArchiveEngineCSSEXT ArchiveEngineCSS manage 0.3 K 17 Jun 2016 - 11:42 PeterZumbruch caller script for ArchiveEngine
ArchiveEngineWebInterface.pngpng ArchiveEngineWebInterface.png manage 125.2 K 09 Jun 2016 - 14:09 PeterZumbruch Archive Engine Web Interface
Archiver_Example.pngpng Archiver_Example.png manage 415.8 K 06 Jun 2016 - 13:54 PeterZumbruch Archiver CSS Browser
Archiver_preferences_settings_1.pngpng Archiver_preferences_settings_1.png manage 67.2 K 06 Jun 2016 - 13:40 PeterZumbruch Archiver preference settings 1
Archiver_preferences_settings_2.pngpng Archiver_preferences_settings_2.png manage 29.6 K 06 Jun 2016 - 13:41 PeterZumbruch Archiver preference settings 2
archive-config-4.1.0-linux.gtk.x86_64.zipzip archive-config-4.1.0-linux.gtk.x86_64.zip manage 24560.6 K 14 Jun 2016 - 08:08 PeterZumbruch archive-config-4.1.0-linux.gtk.x86_64.zip
archive-engine-4.1.0-linux.gtk.x86_64.zipzip archive-engine-4.1.0-linux.gtk.x86_64.zip manage 27347.2 K 14 Jun 2016 - 08:09 PeterZumbruch archive-engine-4.1.0-linux.gtk.x86_64.zip
cs-studio-sns-css-4.1.11 cs-studio-sns-css-4.1.1 manage 0.3 K 17 Jun 2016 - 11:43 PeterZumbruch caller script for css
postgres_partitioning.txttxt postgres_partitioning.txt manage 6.4 K 09 May 2016 - 12:16 PeterZumbruch postgres_partitioning
postgres_schema.txttxt postgres_schema.txt manage 9.2 K 18 Apr 2016 - 14:39 PeterZumbruch cs-studio postgres archive schema
Topic revision: r65 - 17 Sep 2019, 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)