You are here: Hades Wiki>DaqSlowControl Web>HadesDaqDocumentation>DetectorControlSystem>SCSArchiverCSStudio (26 Apr 2021, PeterZumbruch)EditAttach

RDB Archiver based on CS-Studio

Table of Content (short)
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

    SCSArchiverCSStudioPostgresCreateDatabaseAndScheme
    • 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

    SCSArchiverCSStudioLoadScheme
    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

    SCSArchiverCSStudioPostgresPartitioning
    • 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

    SCSArchiverCSStudioLoadSchemeGrants

    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

    SCSArchiverCSStudioPostgresCreateDatabaseAndScheme
    • 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

    SCSArchiverCSStudioLoadScheme
    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

    SCSArchiverCSStudioPostgresPartitioning
    • 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

    SCSArchiverCSStudioLoadSchemeGrants

    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 lxhaddcs10 (current)

Database Configuration - lxhaddcs10 (current)
Hostname lxhaddcs10
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 - lxhaddcs10 (current)
Hostname Port lxhaddcs10: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 - lxhaddcs10 (current)
public.archive@lxhaddcs10:5432
Hostname
lxhaddcs10
Port
5432
Database
archive
Schema
public
Scripts
    Create Database, Users, and Scheme

    SCSArchiverCSStudioPostgresCreateDatabaseAndScheme
    • 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=lxhaddcs10 
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

    SCSArchiverCSStudioLoadScheme
    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=lxhaddcs10 
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=lxhaddcs10 
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

    SCSArchiverCSStudioPostgresPartitioning
    • 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=lxhaddcs10 
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 lxhaddcs10 -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

    SCSArchiverCSStudioLoadSchemeGrants

    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=lxhaddcs10 
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 lxhaddcs10 (future)

Database Configuration - lxhaddcs10 (future)
Hostname lxhaddcs10
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 - lxhaddcs10 (future)
Hostname Port lxhaddcs10: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 - lxhaddcs10 (future)
archive.archive@lxhaddcs10:5432
Hostname
lxhaddcs10
Port
5432
Database
archive
Schema
archive
Scripts
    Create Database, Users, and Scheme

    SCSArchiverCSStudioPostgresCreateDatabaseAndScheme
    • 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=lxhaddcs10 
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

    SCSArchiverCSStudioLoadScheme
    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=lxhaddcs10 
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=lxhaddcs10 
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

    SCSArchiverCSStudioPostgresPartitioning
    • 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=lxhaddcs10 
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 lxhaddcs10 -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

    SCSArchiverCSStudioLoadSchemeGrants

    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=lxhaddcs10 
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

lxhaddcs10_archive 

pluginCustomization=$ARCHIVER_CSS_TOP/pluginCustomization/settings_lxhaddcs10_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

lxhaddcs10

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://lxhaddcs10: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 - 26 Apr 2021
Topic attachments
I Attachment ActionSorted ascending 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 86.4 K 26 Apr 2021 - 08:42 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
Edit | Attach | Print version | History: r67 < r66 < r65 < r64 | Backlinks | View wiki text | Edit wiki text | More topic actions
Topic revision: r67 - 26 Apr 2021, 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)