Wiki » History » Version 7
« Previous -
Version 7/14
(diff) -
Next » -
Current version
Pablo Alingery, 29/09/2017 12:22
Netdrms 9.1.1¶
Posgres 9.4.13¶
Install additional modules¶
We need dblink for triggers between db
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
ias_sdo¶
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
Seems ok !
ias_sdo_sums¶
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
Seems ok too !
ias_sdo_monitor¶
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
Ok too!
Ingest last dumps from sdo1¶
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
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.
--
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);
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.
--
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;
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.
--
> 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);
-- 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.
--
> 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;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();
NOT for aia.lev1 series because we are taking 1/5 data we don't want everything
Create shadow & speed up the usage of aia.lev1 table¶
postgresql-plperl¶
In order to run createshadow we need postgresql-plperl
apt-get install postgresql-plperl
Install language plperl¶
Install plperl within postgres
createlang -p5432 -U postgres plperlu ias_sdo
Create function updateshadow()¶
Create function updateshadow()
psql ias_sdo -p5432 -f /usr/local/netdrms_current/base/drms/libs/pg/updateshadow.sql
So now we are able to launch createshadow()
createshadow series=aia.lev1
Get data from JSOC¶
Create readonly user¶
production@sdo1:/usr/local/netdrms_current$ psql ias_sdo_sums -p5434 -U postgres psql (9.4.13) Type "help" for help. ias_sdo_sums=# CREATE USER readonlyuser; CREATE ROLE ias_sdo_sums=# GRANT CONNECT ON DATABASE ias_sdo_sums To readonlyuser ; GRANT
Launch rsums-client.py¶
python3 base/drms/replication/subscribe_series/rsums-clientd.py --loglevel=debug &