SCSArchiver CSStudio Load Scheme

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

export PGUSER=<database_owner> 
export PGHOST=%database_host% 
export PGPORT=%port% 
export PGDATABASE=%database% 
schema=%schema%
URL=%url% 
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=%database_host% 
    export PGPORT=%port% 
    export PGDATABASE=%database% 
    schema=%schema%
    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 -


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