RDB Archiver based on CS-Studio

PostgreSQL hosted by GSI central services

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

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
	"p.zumbruch"
	epics						pw: m7Z6a1B7g2
	hades_scs_css_archive_read	pw: k2E4g7D1c8
	hades_scs_css_archive
	hades_scs_css_archive_admin
	"m.traxler"
	"j.adamczewski-musch"

---

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

---

<div style="text-align:right;font-size:smaller">SCSArchiverCSStudioConfigurationScripts <a href="/edit/DaqSlowControl/SCSArchiverCSStudioConfigurationScripts">edit</a></div>

Hostname

pgsqltest.gsi.de

Port

6846

Database

hades_scs_css_archive

Schema

hades_scs_css_archive

Scripts

Create Database, Users, and Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresCreateDatabaseAndScheme</div>

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

%CODE{"bash"}% export PG_Owner=%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 - %ENDCODE%

Load Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadScheme</div>

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

%CODE{"bash"}% export PGUSER= export PGHOST=pgsqltest.gsi.de export PGPORT=6846 export PGDATABASE=hades_scs_css_archive schema=hades_scs_css_archive URL=$css-address/raw/$path/$file 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 - %ENDCODE%

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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)


%CODE{"bash"}% export PGUSER= 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 - %ENDCODE%

Partitioning

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




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

<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresPartitioning</div>

• 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

%CODE{"bash"}% 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="$css-address/raw/$path/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 - %ENDCODE%

Note

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

crontab :
%CODE{"bash"}% -- This maintenance function automatically creates partitions according to the -- specified interval (e.g. weekly or monthly). The first partition starts at -- 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). %ENDCODE% ⇒ 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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right" style="font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadSchemeGrants</div>

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

%CODE{"bash"}% export archive=<(list of) archiver_user(s)>)> export report=<(list of) report_user(s)>)> export PGUSER= export PGHOST=pgsqltest.gsi.de export PGPORT=6846 export PGDATABASE=hades_scs_css_archive schema=hades_scs_css_archive URL=$css-address/raw/$path/$file 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

%ENDCODE%

---

css_ee

---

<div style="text-align:right;font-size:smaller">SCSArchiverCSStudioConfigurationScripts <a href="/edit/DaqSlowControl/SCSArchiverCSStudioConfigurationScripts">edit</a></div>

Hostname

pgsqltest.gsi.de

Port

6846

Database

css_ee

Schema

css_ee

Scripts

Create Database, Users, and Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresCreateDatabaseAndScheme</div>

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

%CODE{"bash"}% export PG_Owner=%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 - %ENDCODE%

Load Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadScheme</div>

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

%CODE{"bash"}% export PGUSER= export PGHOST=pgsqltest.gsi.de export PGPORT=6846 export PGDATABASE=css_ee schema=css_ee URL=$css-address/raw/$path/$file 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 - %ENDCODE%

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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)


%CODE{"bash"}% export PGUSER= 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 - %ENDCODE%

Partitioning

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




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

<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresPartitioning</div>

• 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

%CODE{"bash"}% 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="$css-address/raw/$path/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 - %ENDCODE%

Note

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

crontab :
%CODE{"bash"}% -- This maintenance function automatically creates partitions according to the -- specified interval (e.g. weekly or monthly). The first partition starts at -- 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). %ENDCODE% ⇒ 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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right" style="font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadSchemeGrants</div>

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

%CODE{"bash"}% export archive=<(list of) archiver_user(s)>)> export report=<(list of) report_user(s)>)> export PGUSER= export PGHOST=pgsqltest.gsi.de export PGPORT=6846 export PGDATABASE=css_ee schema=css_ee URL=$css-address/raw/$path/$file 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

%ENDCODE%

---

%NEXT{"prefix"}% %NEXT{"path"}% %NEXT{"file"}%

---

PostgreSQL hosted "locally" w/ option for NOSSL

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

Installation

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

Install

• • Debian

%CODE{"bash"}% $ apt-get install postgresql postgresql-client %ENDCODE%

• • Suse Leap 42.1
    q.v. https://www.howtoforge.com/tutorial/how-to-install-postgresql-and-phppgadmin-on-opensuse-leap-42.1

%FOREACH{"version" in="9.6"}%

fully prepared: Git Repository

%FOREACH{"url" in="https://git.gsi.de/HADES/DCS/EPICS/Services/Archiver/Postgres-Server-Configuration"}%

• $url

%CODE{"bash"}% branch=$version && 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:?} $url /etc/postgresql && chown postgres:postgres -R /etc/postgresql" %ENDCODE% %NEXT{"url"}%

by-hand: Server Connectivity

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

in: /etc/postgresql/$version/main/postgresql.conf

change %CODE% listen_addresses = 'localhost' %ENDCODE% to %CODE% listen_addresses = '*' %ENDCODE%ls

and add in /etc/postgresql/$version/main/pg_hba.conf %CODE% %FOREACH{"ip" in="140.181.75.165/18 140.181.80.125/18 140.181.93.18/18"}% hostnossl all all $ip trust %NEXT{"ip"}% 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 %ENDCODE%

in: /etc/postgresql/$version/main/postgresql.conf
the data directory is set to: %CODE% data_directory = '/var/lib/postgresql/$version/main' # use data in another directory %ENDCODE% should be changed to a bigger directory: %CODE% data_directory = '/data.postgres/postgresql/$version/main' # use data in another directory %ENDCODE% %NEXT{"version"}%

start/restart

%CODE{"bash"}% # service postgresql restart; service postgresql status %ENDCODE%

---

<div style="text-align:right;font-size:smaller">SCSArchiverCSStudioPostgresDatabaseConfigurationLocal <a href="/edit/DaqSlowControl/SCSArchiverCSStudioPostgresDatabaseConfigurationLocal">edit</a></div>

%FOREACH{"css-address" in="https://github.com/ControlSystemStudio/cs-studio"}% %FOREACH{"file" in="postgres_schema.txt"}% %FOREACH{"path" in="master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd"}% %FOREACH{"prefix" in="SCSArchiverCSStudioPostgreSql"}%

Configuration lxhaddcs10 (current)

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

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
	report	pw: $report
	postgres	superuser

---

Configuration Scripts - lxhaddcs10 (current)

public.archive@lxhaddcs10:5432

<div style="text-align:right;font-size:smaller">SCSArchiverCSStudioConfigurationScripts <a href="/edit/DaqSlowControl/SCSArchiverCSStudioConfigurationScripts">edit</a></div>

Hostname

lxhaddcs10

Port

5432

Database

archive

Schema

public

Scripts

Create Database, Users, and Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresCreateDatabaseAndScheme</div>

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

%CODE{"bash"}% export PG_Owner=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 - %ENDCODE%

Load Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadScheme</div>

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

%CODE{"bash"}% export PGUSER= export PGHOST=lxhaddcs10 export PGPORT=5432 export PGDATABASE=archive schema=public URL=$css-address/raw/$path/$file 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 - %ENDCODE%

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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)


%CODE{"bash"}% export PGUSER= 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 - %ENDCODE%

Partitioning

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




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

<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresPartitioning</div>

• 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

%CODE{"bash"}% export PG_Owner=archiver export PGUSER=<schema_owner, e.g. "archiver"> export PGHOST=lxhaddcs10 export PGPORT=5432 export PGDATABASE=archive schema=public URL="$css-address/raw/$path/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 - %ENDCODE%

Note

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

crontab :
%CODE{"bash"}% -- This maintenance function automatically creates partitions according to the -- specified interval (e.g. weekly or monthly). The first partition starts at -- 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). %ENDCODE% ⇒ 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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right" style="font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadSchemeGrants</div>

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

%CODE{"bash"}% export archive=<(list of) archiver_user(s)>)> export report=<(list of) report_user(s)>)> export PGUSER= export PGHOST=lxhaddcs10 export PGPORT=5432 export PGDATABASE=archive schema=public URL=$css-address/raw/$path/$file 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

%ENDCODE%

---

%NEXT{"prefix"}% %NEXT{"path"}% %NEXT{"file"}% %NEXT{"css-address"}%

---

---

<div style="text-align:right;font-size:smaller">SCSArchiverCSStudioPostgresDatabaseConfigurationLocal <a href="/edit/DaqSlowControl/SCSArchiverCSStudioPostgresDatabaseConfigurationLocal">edit</a></div>

%FOREACH{"css-address" in="https://github.com/ControlSystemStudio/cs-studio"}% %FOREACH{"file" in="postgres_schema.txt"}% %FOREACH{"path" in="master/applications/archive/archive-plugins/org.csstudio.archive.rdb/dbd"}% %FOREACH{"prefix" in="SCSArchiverCSStudioPostgreSql"}%

Configuration lxhaddcs10 (future)

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

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
	report	pw: $report
	postgres	superuser

---

Configuration Scripts - lxhaddcs10 (future)

archive.archive@lxhaddcs10:5432

<div style="text-align:right;font-size:smaller">SCSArchiverCSStudioConfigurationScripts <a href="/edit/DaqSlowControl/SCSArchiverCSStudioConfigurationScripts">edit</a></div>

Hostname

lxhaddcs10

Port

5432

Database

archive

Schema

archive

Scripts

Create Database, Users, and Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresCreateDatabaseAndScheme</div>

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

%CODE{"bash"}% export PG_Owner=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 - %ENDCODE%

Load Scheme

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadScheme</div>

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

%CODE{"bash"}% export PGUSER= export PGHOST=lxhaddcs10 export PGPORT=5432 export PGDATABASE=archive schema=archive URL=$css-address/raw/$path/$file 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 - %ENDCODE%

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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)


%CODE{"bash"}% export PGUSER= 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 - %ENDCODE%

Partitioning

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




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

<div style="text-align:right;font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioPostgresPartitioning</div>

• 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

%CODE{"bash"}% export PG_Owner=postgres export PGUSER=<schema_owner, e.g. "archiver"> export PGHOST=lxhaddcs10 export PGPORT=5432 export PGDATABASE=archive schema=archive URL="$css-address/raw/$path/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 - %ENDCODE%

Note

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

crontab :
%CODE{"bash"}% -- This maintenance function automatically creates partitions according to the -- specified interval (e.g. weekly or monthly). The first partition starts at -- 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). %ENDCODE% ⇒ 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

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)




<div style="text-align:right" style="font-size:smaller" class='TMLhtml' > SCSArchiverCSStudioLoadSchemeGrants</div>

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

%CODE{"bash"}% export archive=<(list of) archiver_user(s)>)> export report=<(list of) report_user(s)>)> export PGUSER= export PGHOST=lxhaddcs10 export PGPORT=5432 export PGDATABASE=archive schema=archive URL=$css-address/raw/$path/$file 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

%ENDCODE%

---

%NEXT{"prefix"}% %NEXT{"path"}% %NEXT{"file"}% %NEXT{"css-address"}%

---

---

---

Requirements

• CS-Studio 4 requires at least Java 8 by Oracle. Therefore make sure you have it installed:
  • q.v. https://docs.oracle.com/javase/8/docs/technotes/guides/install/install_overview.html#CJAGAACB

Sources download

• archive_config...
• archvie_engine...

• 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

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

CVS

• GSI Linux Cluster

  CVSROOT

  /misc/hadesprojects/slowcontrol/cvsroot

  ==Module

  Archiver.css

git

hades_scs_css_archive

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

lxhadeb05_archive

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

lxhaddcs10_archive

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

by Hand

using crontab / procServ

Show ... (Expand all... Collapse all)Hide ... (Expand all... Collapse all)

via web browser

script based using curl

via procServ - "brute force"

lxhaddcs10

• Archiver preference settings 1:
  

• URL: jdbc:postgresql://lxhaddcs10:5432/archive

• Archiver preference settings 2:
  
• User: report

• Archiver CSS Browser: