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:

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



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 -