Installing pg isok

pg_isok is "The Warning System".

When to Install

Pg_isok is installed in its own schema, isolated from all of SokweDB's schemas. It only need be installed once, upon database creation. Removing or re-installing SokweDB's schemas using the build tools do not interfere with the content of Pg_isok's schema.

Of course, if an entire database is dropped, re-created, and restored from backup, pg_isok will also need to be restored. Depending on the situation, this may involve reinstalling pg_isok and then restoring the content of its tables.

Installing The Warning System (pg_isok)

The pg_isok PostgreSQL extension is used as SokweDB's "Warning System". Because SokweDB is in the cloud, pg_isok must be installed from a SQL script.

To generate the SQL needed to install pg_isok, follow the cloud instructions found in the pg_isok documentation. SokweDB expects installation in a schema named isok.

Install pg_isok in each database.

After installation, permissions must be granted.

The following example shows the steps involved to install pg_isok version 0.1.4, using psql, and to grant the expected permissions. (The name of the SQL file, included with \i, will vary with the pg_isok version and the relative location of the sql file.)

SET ROLE TO admin;
CREATE SCHEMA isok;
GRANT USAGE ON SCHEMA isok TO reader;
GRANT USAGE ON SCHEMA isok TO writer;

\i ../pg_isok_cloud--0.1.4.sql

-- IQ_TYPES
GRANT SELECT ON isok.iq_types TO reader;
GRANT SELECT ON isok.iq_types TO writer;
GRANT INSERT ON isok.iq_types TO writer;
GRANT UPDATE ON isok.iq_types TO writer;
GRANT DELETE ON isok.iq_types TO writer;

-- IR_TYPES
GRANT SELECT ON isok.ir_types TO reader;
GRANT SELECT ON isok.ir_types TO writer;
GRANT INSERT ON isok.ir_types TO writer;
GRANT UPDATE ON isok.ir_types TO writer;
GRANT DELETE ON isok.ir_types TO writer;

-- ISOK_QUERIES
GRANT SELECT ON isok.isok_queries TO reader;
GRANT SELECT ON isok.isok_queries TO writer;
GRANT INSERT ON isok.isok_queries TO writer;
GRANT UPDATE ON isok.isok_queries TO writer;
GRANT DELETE ON isok.isok_queries TO writer;

-- ISOK_RESULTS
GRANT SELECT ON isok.isok_results TO reader;
GRANT SELECT ON isok.isok_results TO writer;
GRANT INSERT ON isok.isok_results TO writer;
GRANT UPDATE ON isok.isok_results TO writer;
GRANT DELETE ON isok.isok_results TO writer;

GRANT SELECT ON isok.isok_results_irid_seq TO reader;
GRANT SELECT ON isok.isok_results_irid_seq TO writer;
GRANT UPDATE ON isok.isok_results_irid_seq TO writer;

-- run_isok_queries()
GRANT EXECUTE ON FUNCTION isok.run_isok_queries() TO writer;
GRANT EXECUTE ON FUNCTION isok.run_isok_queries(TEXT) TO writer;

updating pg_isok

Installing or updating pg_isok requires a clean isok schema. As such, a backup of warn queries and results that can be reloaded after updating pg_isok is needed if that information should be maintained. The procedure for updating pg_isok is similar to those for installation with the additional steps of first creating a data-only dump of isok schema contents prior to dropping then creating the schema, and restoring those contents once the update is complete. As with installation, these steps need to be addressed separately for each database.

pg_dump -h HOST -d DATABASE -n isok -Fc --data-only > isok_schema_contents

within psql:

DROP SCHEMA isok CASCADE;

Follow the installation steps above to (re)install pg_isok then restore the dumped contents to repopulate the schema:

pg_restore -h HOST -d DATABASE -n isok --data-only isok_schema_contents