Project

General

Profile

Wiki » History » Version 4

Pablo Alingery, 28/09/2017 15:20

1 1 Pablo Alingery
h1. Netdrms 9.1.1  
2 1 Pablo Alingery
3 1 Pablo Alingery
h2. Posgres 9.4.13 
4 1 Pablo Alingery
5 1 Pablo Alingery
h3. Install additional modules
6 1 Pablo Alingery
7 1 Pablo Alingery
We need dblink for triggers between db 
8 1 Pablo Alingery
9 1 Pablo Alingery
<pre>
10 1 Pablo Alingery
root@sdo1:~# cd /usr/share/postgresql/9.4/extension
11 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# ls -al  dblink--1.1.sql
12 1 Pablo Alingery
-rw-r--r-- 1 root root 5831 Aug 10 14:53 dblink--1.1.sql
13 1 Pablo Alingery
</pre>
14 1 Pablo Alingery
15 1 Pablo Alingery
h4. ias_sdo
16 1 Pablo Alingery
17 1 Pablo Alingery
<pre>
18 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo -p5432 -U postgres < dblink--1.1.sql
19 1 Pablo Alingery
Use "CREATE EXTENSION dblink" to load this file.
20 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# 
21 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# 
22 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# 
23 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo -U postgres 
24 1 Pablo Alingery
psql (9.4.13)
25 1 Pablo Alingery
Type "help" for help.
26 1 Pablo Alingery
27 4 Pablo Alingery
28 4 Pablo Alingery
29 4 Pablo Alingery
30 4 Pablo Alingery
31 4 Pablo Alingery
32 1 Pablo Alingery
ias_sdo=# CREATE EXTENSION dblink ;
33 1 Pablo Alingery
ERROR:  extension "dblink" already exists
34 1 Pablo Alingery
ias_sdo=# \q
35 1 Pablo Alingery
</pre>
36 1 Pablo Alingery
37 1 Pablo Alingery
Seems ok !
38 1 Pablo Alingery
39 1 Pablo Alingery
h4. ias_sdo_sums 
40 1 Pablo Alingery
41 1 Pablo Alingery
<pre>
42 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_sums -p5434 -U postgres < dblink--1.1.sql 
43 1 Pablo Alingery
Use "CREATE EXTENSION dblink" to load this file.
44 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_sums -p5434 -U postgres 
45 1 Pablo Alingery
psql (9.4.13)
46 1 Pablo Alingery
Type "help" for help.
47 1 Pablo Alingery
48 1 Pablo Alingery
ias_sdo_sums=#  CREATE EXTENSION dblink ;
49 1 Pablo Alingery
ERROR:  extension "dblink" already exists
50 1 Pablo Alingery
ias_sdo_sums=# \q
51 1 Pablo Alingery
</pre>
52 1 Pablo Alingery
53 1 Pablo Alingery
Seems ok too !
54 1 Pablo Alingery
55 1 Pablo Alingery
h4. ias_sdo_monitor 
56 1 Pablo Alingery
57 1 Pablo Alingery
<pre>
58 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_monitor -p5436 -U postgres < dblink--1.1.sql 
59 1 Pablo Alingery
Use "CREATE EXTENSION dblink" to load this file.
60 1 Pablo Alingery
root@sdo1:/usr/share/postgresql/9.4/extension# psql ias_sdo_monitor -p5436 -U postgres
61 1 Pablo Alingery
psql (9.4.13)
62 1 Pablo Alingery
Type "help" for help.
63 1 Pablo Alingery
64 1 Pablo Alingery
ias_sdo_monitor=# CREATE EXTENSION dblink ;
65 1 Pablo Alingery
ERROR:  extension "dblink" already exists
66 1 Pablo Alingery
</pre>
67 1 Pablo Alingery
68 1 Pablo Alingery
Ok too!
69 2 Pablo Alingery
70 2 Pablo Alingery
71 2 Pablo Alingery
Creation of missing tables & functions
72 2 Pablo Alingery
73 2 Pablo Alingery
TABLE drms.ingested_sunums
74 2 Pablo Alingery
75 2 Pablo Alingery
--When a client ingests a Slony log, each SUNUM of each data series record is copied
76 2 Pablo Alingery
-- into this table. The client can use this table of SUNUMs to prefetch SUs from the
77 2 Pablo Alingery
-- providing site. This SQL is ingested as the pg_user database user, the same
78 2 Pablo Alingery
-- user that will be ingesting Slony logs and prefetching SUs, so permissions on
79 2 Pablo Alingery
-- this table will be correct without having to execute a GRANT statement.
80 2 Pablo Alingery
-- 
81 2 Pablo Alingery
-- The namespace drms is required, and is created by NetDRMS.sql during the
82 2 Pablo Alingery
-- NetDRMS installation process.
83 2 Pablo Alingery
-- 
84 2 Pablo Alingery
85 2 Pablo Alingery
<pre>
86 2 Pablo Alingery
DROP TABLE IF EXISTS drms.ingested_sunums;
87 2 Pablo Alingery
CREATE TABLE drms.ingested_sunums (sunum bigint PRIMARY KEY, starttime timestamp with time zone NOT NULL);
88 2 Pablo Alingery
CREATE INDEX ingested_sunums_starttime ON drms.ingested_sunums(starttime);
89 2 Pablo Alingery
</pre>
90 2 Pablo Alingery
91 2 Pablo Alingery
92 2 Pablo Alingery
93 2 Pablo Alingery
FUNCTION drms.capturesunum
94 2 Pablo Alingery
-- 
95 2 Pablo Alingery
-- For each table under replication, a trigger is created that calls this function, which
96 2 Pablo Alingery
-- then copies SUNUMs into the underlying table, drms.ingested_sunums.
97 2 Pablo Alingery
-- 
98 2 Pablo Alingery
-- drms.ingested_sunums may not exist (older NetDRMSs did not receive the SQL to create
99 2 Pablo Alingery
-- this table). If this table does not exist, then this function is a no-op.
100 2 Pablo Alingery
-- 
101 2 Pablo Alingery
102 2 Pablo Alingery
<pre>
103 2 Pablo Alingery
CREATE OR REPLACE FUNCTION drms.capturesunum() RETURNS TRIGGER AS
104 2 Pablo Alingery
$capturesunumtrig$
105 2 Pablo Alingery
BEGIN
106 2 Pablo Alingery
    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
107 2 Pablo Alingery
      IF (TG_OP='INSERT' AND new.sunum > 0) THEN
108 2 Pablo Alingery
        IF EXISTS (SELECT 1 FROM drms.ingested_sunums WHERE sunum = new.sunum) THEN
109 2 Pablo Alingery
          RETURN NULL;
110 2 Pablo Alingery
        END IF;
111 2 Pablo Alingery
        INSERT INTO drms.ingested_sunums (sunum, starttime) VALUES (new.sunum, clock_timestamp());
112 2 Pablo Alingery
      END IF;
113 2 Pablo Alingery
    END IF;
114 2 Pablo Alingery
115 2 Pablo Alingery
  RETURN NEW;
116 2 Pablo Alingery
END
117 2 Pablo Alingery
$capturesunumtrig$ LANGUAGE plpgsql;
118 2 Pablo Alingery
</pre>
119 3 Pablo Alingery
120 3 Pablo Alingery
121 3 Pablo Alingery
122 3 Pablo Alingery
Add a trigger on every hmi table like this to use the function:
123 3 Pablo Alingery
124 3 Pablo Alingery
CREATE TRIGGER capturesunumtrig AFTER INSERT ON hmi.bharp_720s FOR EACH ROW EXECUTE PROCEDURE drms.capturesunum();
125 3 Pablo Alingery
126 3 Pablo Alingery
127 3 Pablo Alingery
> -- TABLE drms.ingested_sunums
128 3 Pablo Alingery
> -- 
129 3 Pablo Alingery
> -- When a client ingests a Slony log, each SUNUM of each data series record is copied
130 3 Pablo Alingery
> -- into this table. The client can use this table of SUNUMs to prefetch SUs from the
131 3 Pablo Alingery
> -- providing site. This SQL is ingested as the pg_user database user, the same
132 3 Pablo Alingery
> -- user that will be ingesting Slony logs and prefetching SUs, so permissions on
133 3 Pablo Alingery
> -- this table will be correct without having to execute a GRANT statement.
134 3 Pablo Alingery
> -- 
135 3 Pablo Alingery
> -- The namespace drms is required, and is created by NetDRMS.sql during the
136 3 Pablo Alingery
> -- NetDRMS installation process.
137 3 Pablo Alingery
> -- 
138 3 Pablo Alingery
139 3 Pablo Alingery
<pre>
140 3 Pablo Alingery
> DROP TABLE IF EXISTS drms.ingested_sunums;
141 3 Pablo Alingery
> CREATE TABLE drms.ingested_sunums (sunum bigint PRIMARY KEY, starttime timestamp with time zone NOT NULL);
142 3 Pablo Alingery
> CREATE INDEX ingested_sunums_starttime ON drms.ingested_sunums(starttime);
143 3 Pablo Alingery
</pre>
144 3 Pablo Alingery
145 3 Pablo Alingery
> -- FUNCTION drms.capturesunum
146 3 Pablo Alingery
> -- 
147 3 Pablo Alingery
> -- For each table under replication, a trigger is created that calls this function, which
148 3 Pablo Alingery
> -- then copies SUNUMs into the underlying table, drms.ingested_sunums.
149 3 Pablo Alingery
> -- 
150 3 Pablo Alingery
> -- drms.ingested_sunums may not exist (older NetDRMSs did not receive the SQL to create
151 3 Pablo Alingery
> -- this table). If this table does not exist, then this function is a no-op.
152 3 Pablo Alingery
> -- 
153 3 Pablo Alingery
154 3 Pablo Alingery
<pre>
155 3 Pablo Alingery
> CREATE OR REPLACE FUNCTION drms.capturesunum() RETURNS TRIGGER AS
156 3 Pablo Alingery
> $capturesunumtrig$
157 3 Pablo Alingery
> BEGIN
158 3 Pablo Alingery
>     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
159 3 Pablo Alingery
>       IF (TG_OP='INSERT' AND new.sunum > 0) THEN
160 3 Pablo Alingery
>         IF EXISTS (SELECT 1 FROM drms.ingested_sunums WHERE sunum = new.sunum) THEN
161 3 Pablo Alingery
>           RETURN NULL;
162 3 Pablo Alingery
>         END IF;
163 3 Pablo Alingery
>         INSERT INTO drms.ingested_sunums (sunum, starttime) VALUES (new.sunum, clock_timestamp());
164 3 Pablo Alingery
>       END IF;
165 3 Pablo Alingery
>     END IF;
166 3 Pablo Alingery
>
167 3 Pablo Alingery
>   RETURN NEW;
168 3 Pablo Alingery
> END
169 3 Pablo Alingery
> $capturesunumtrig$ LANGUAGE plpgsql;
170 4 Pablo Alingery
</pre>
171 4 Pablo Alingery
172 4 Pablo Alingery
173 4 Pablo Alingery
 Add a trigger on every hmi table like this to use the function:
174 4 Pablo Alingery
175 4 Pablo Alingery
<pre>
176 4 Pablo Alingery
CREATE TRIGGER capturesunumtrig AFTER INSERT ON hmi.bharp_720s FOR EACH ROW EXECUTE PROCEDURE drms.capturesunum();
177 3 Pablo Alingery
</pre>