Project

General

Profile

Wiki » History » Version 3

Pablo Alingery, 28/09/2017 15:17

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