MEDOC - Multi Experiment Data & Operation Center » Netdrms_9.1.1

h1. Netdrms 9.1.1  

h2. Posgres 9.4.13 

h3. Install additional modules

We need dblink for triggers between db 

<pre>

root@sdo1:~# cd /usr/share/postgresql/9.4/extension

root@sdo1:/usr/share/postgresql/9.4/extension# ls -al  dblink--1.1.sql

-rw-r--r-- 1 root root 5831 Aug 10 14:53 dblink--1.1.sql

</pre>

h4. ias_sdo

<pre>

root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo -p5432 -U postgres < dblink--1.1.sql

Use "CREATE EXTENSION dblink" to load this file.

root@sdo1:/usr/share/postgresql/9.4/extension# 

root@sdo1:/usr/share/postgresql/9.4/extension# 

root@sdo1:/usr/share/postgresql/9.4/extension# 

root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo -U postgres 

psql (9.4.13)

Type "help" for help.

ias_sdo=# CREATE EXTENSION dblink ;

ERROR:  extension "dblink" already exists

ias_sdo=# \q

</pre>

Seems ok !

h4. ias_sdo_sums 

<pre>

root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_sums -p5434 -U postgres < dblink--1.1.sql 

Use "CREATE EXTENSION dblink" to load this file.

root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_sums -p5434 -U postgres 

psql (9.4.13)

Type "help" for help.

ias_sdo_sums=#  CREATE EXTENSION dblink ;

ERROR:  extension "dblink" already exists

ias_sdo_sums=# \q

</pre>

Seems ok too !

h4. ias_sdo_monitor 

<pre>

root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_monitor -p5436 -U postgres < dblink--1.1.sql 

Use "CREATE EXTENSION dblink" to load this file.

root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_monitor -p5436 -U postgres

psql (9.4.13)

Type "help" for help.

ias_sdo_monitor=# CREATE EXTENSION dblink ;

ERROR:  extension "dblink" already exists

</pre>

Ok too!

h3. Ingest last dumps from sdo1

<pre>

postgres@sdo1:~/dumps$ gunzip < ias_sdo_sums_2017-09-28.gz | psql ias_sdo_sums  -p5434 -U postgres

postgres@sdo1:~/dumps$ gunzip < ias_sdo_2017-09-28.gz | psql ias_sdo -p5432 -U postgres

postgres@sdo1:~/dumps$ gunzip < ias_sdo_monitor_2017-09-28.gz | psql ias_sdo_monitor -p5436 -U postgres

</pre>

h3. Creation of missing tables & functions

TABLE drms.ingested_sunums

--When a client ingests a Slony log, each SUNUM of each data series record is copied

-- into this table. The client can use this table of SUNUMs to prefetch SUs from the

-- providing site. This SQL is ingested as the pg_user database user, the same

-- user that will be ingesting Slony logs and prefetching SUs, so permissions on

-- this table will be correct without having to execute a GRANT statement.

-- 

-- The namespace drms is required, and is created by NetDRMS.sql during the

-- NetDRMS installation process.

-- 

<pre>

DROP TABLE IF EXISTS drms.ingested_sunums;

CREATE TABLE drms.ingested_sunums (sunum bigint PRIMARY KEY, starttime timestamp with time zone NOT NULL);

CREATE INDEX ingested_sunums_starttime ON drms.ingested_sunums(starttime);

</pre>

FUNCTION drms.capturesunum

-- 

-- For each table under replication, a trigger is created that calls this function, which

-- then copies SUNUMs into the underlying table, drms.ingested_sunums.

-- 

-- drms.ingested_sunums may not exist (older NetDRMSs did not receive the SQL to create

-- this table). If this table does not exist, then this function is a no-op.

-- 

<pre>

CREATE OR REPLACE FUNCTION drms.capturesunum() RETURNS TRIGGER AS

$capturesunumtrig$

BEGIN

    IF EXISTS (SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'drms' AND c.relname = 'ingested_sunums') THEN

      IF (TG_OP='INSERT' AND new.sunum > 0) THEN

        IF EXISTS (SELECT 1 FROM drms.ingested_sunums WHERE sunum = new.sunum) THEN

          RETURN NULL;

        END IF;

        INSERT INTO drms.ingested_sunums (sunum, starttime) VALUES (new.sunum, clock_timestamp());

      END IF;

    END IF;

  RETURN NEW;

END

$capturesunumtrig$ LANGUAGE plpgsql;

</pre>

Add a trigger on every hmi table like this to use the function:

CREATE TRIGGER capturesunumtrig AFTER INSERT ON hmi.bharp_720s FOR EACH ROW EXECUTE PROCEDURE drms.capturesunum();

> -- TABLE drms.ingested_sunums

> -- 

> -- When a client ingests a Slony log, each SUNUM of each data series record is copied

> -- into this table. The client can use this table of SUNUMs to prefetch SUs from the

> -- providing site. This SQL is ingested as the pg_user database user, the same

> -- user that will be ingesting Slony logs and prefetching SUs, so permissions on

> -- this table will be correct without having to execute a GRANT statement.

> -- 

> -- The namespace drms is required, and is created by NetDRMS.sql during the

> -- NetDRMS installation process.

> -- 

<pre>

> DROP TABLE IF EXISTS drms.ingested_sunums;

> CREATE TABLE drms.ingested_sunums (sunum bigint PRIMARY KEY, starttime timestamp with time zone NOT NULL);

> CREATE INDEX ingested_sunums_starttime ON drms.ingested_sunums(starttime);

</pre>

> -- FUNCTION drms.capturesunum

> -- 

> -- For each table under replication, a trigger is created that calls this function, which

> -- then copies SUNUMs into the underlying table, drms.ingested_sunums.

> -- 

> -- drms.ingested_sunums may not exist (older NetDRMSs did not receive the SQL to create

> -- this table). If this table does not exist, then this function is a no-op.

> -- 

<pre>

> CREATE OR REPLACE FUNCTION drms.capturesunum() RETURNS TRIGGER AS

> $capturesunumtrig$

> BEGIN

>     IF EXISTS (SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'drms' AND c.relname = 'ingested_sunums') THEN

>       IF (TG_OP='INSERT' AND new.sunum > 0) THEN

>         IF EXISTS (SELECT 1 FROM drms.ingested_sunums WHERE sunum = new.sunum) THEN

>           RETURN NULL;

>         END IF;

>         INSERT INTO drms.ingested_sunums (sunum, starttime) VALUES (new.sunum, clock_timestamp());

>       END IF;

>     END IF;

>

>   RETURN NEW;

> END

> $capturesunumtrig$ LANGUAGE plpgsql;

</pre>

 Add a trigger on every hmi table like this to use the function:

<pre>

CREATE TRIGGER capturesunumtrig AFTER INSERT ON hmi.bharp_720s FOR EACH ROW EXECUTE PROCEDURE drms.capturesunum();

</pre>

NOT for aia.lev1 series because we are taking 1/5 data we don't want everything

h3. Create shadow & speed up the usage of aia.lev1 table 

h4. postgresql-plperl

In order to run createshadow we need postgresql-plperl

<pre>

apt-get install postgresql-plperl 

</pre>

h4. Install language plperl

Install plperl within postgres

<pre>

createlang -p5432 -U postgres plperlu ias_sdo

</pre>

h4. Create function updateshadow()

Create function updateshadow()

<pre>

psql ias_sdo -p5432 -f /usr/local/netdrms_current/base/drms/libs/pg/updateshadow.sql

</pre>

So now we are able to launch createshadow()

<pre>

createshadow series=aia.lev1

</pre>