Wiki » History » Version 2
Version 1 (Pablo Alingery, 28/09/2017 10:44) → Version 2/14 (Pablo Alingery, 28/09/2017 15:14)
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!
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>
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!
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>