Isok -- A query centered monitoring tool for PostgreSQLIsok -- A query centered monitoring tool for PostgreSQL

Introduction to IsokIntroduction to Isok

InstallationInstallation

RequirementsRequirements

Quick-StartQuick-Start

Preparing, While Logged-In to Un*xPreparing, While Logged-In to Un*x

Re-Building to Disable FeaturesRe-Building to Disable Features

Building for and Installing in The Cloud (Installing With SQL)Building for and Installing in The Cloud (Installing With SQL)

Installing in the PostgreSQL Server's OSInstalling in the PostgreSQL Server's OS

Running Regression TestsRunning Regression Tests

Loading Into PostgreSQLLoading Into PostgreSQL

UninstallingUninstalling

Uninstalling From the OSUninstalling From the OS

Uninstalling From PostgreSQLUninstalling From PostgreSQL

An Overview of the Isok TablesAn Overview of the Isok TablesThe Isok TablesTable 1The Isok Support TablesTable 2Key To Entity Relationship DiagramsFigure 1Isok Entity Relationship DiagramFigure 2

The Isok Main TablesThe Isok Main Tables???TITLE???The Isok Main Tables

ISOK_QUERIESISOK_QUERIESInserting a query into ISOK_QUERIES using dollar quotingExample 1

IQName (Isok Query Name)IQName

ErrorError

TypeType

First_RunFirst_Run

Last_RunLast_Run

Keep (Keep old results)Keep

RoleRole

Search_PathSearch_Path

QueryQuery

The first returned column, the ID columnID

The second returned column, the Msg columnMsg

The third returned column, the Extra JSON columnExtra

CommentComment

ISOK_RESULTS (Isok query Results)ISOK_RESULTS???TITLE???ISOK_RESULTS (Isok query Results)???TITLE???ISOK_RESULTS (Isok query Results)

IRID (Integrity Results Identifier)IRID

IQName (Integrity Query Name)IQName

First_SeenFirst_Seen

Last_SeenLast_Seen

Last_RoleLast_Role

Last_SchemasLast_Schemas

Resolved (Date and Time Resolved)Resolved

Deferred_ToDeferred_To

CategoryCategory

Keep_UntilKeep_Until

QR_ID (Query Result IDentifier)QR_ID

QR_Message (Query Result Message)QR_Message

QR_Extra (Query Result Extra JSON data)QR_Extra

NotesNotes

Isok Support TablesIsok Support Tables

IQ_TYPES (Integrity Query Types)IQ_TYPES

Key: IQTypeIQTypeKey: IQTypeIQType

IR_TYPES (Isok Result Types)IR_TYPES

Key: IRTypeIRTypeKey: IRTypeIRType

The Isok Functions (Activating Isok)The Isok Functions

run_isok_queriesrun_isok_queries()

InputInput

iqname_queryiqname_query

DescriptionDescription

The Record of Query ExecutionThe Record of Query Execution

Deletion of Old ResultsDeletion of Old Results

ExamplesExamplesExecuting all ISOK_QUERIESExample 2Executing a single ISOK_QUERIES.QueryExample 3Executing many specific ISOK_QUERIES.Query-sExample 4Executing ISOK_QUERIES of the “bdate” typeExample 5

Security ConsiderationsAppendix A

Limiting AccessLimiting Access

What Queries Access MattersWhat Queries Access Matters

The Search PathThe Search Path

RolesRoles

Mitigation StrategiesMitigation Strategies

Creating an Audit TrailCreating an Audit Trail

Local Copies of the DocumentationAppendix BFinding the Documentation of Locally Installed ExtensionsExample B.1

Periodic ExecutionAppendix C

Example Periodic Reporting via Email Using systemdExample Periodic Reporting via Email Using systemdSample /usr/local/bin/pg_isok_report FileExample C.1Sample /etc/systemd/system/isok_report.timer FileExample C.2Sample /etc/systemd/system/isok_report.service FileExample C.3

Techniques For Making Local Extensions to IsokAppendix D

Wrap run_isok_queries()Wrap run_isok_queries()

Extend Issue ClassificationExtend Issue Classification

Fully Utilize ISOK_RESULTS.QR_ExtraFully Utilize ISOK_RESULTS.QR_Extra

Modify Isok's Generated SQLModify Isok's Generated SQL

Developing IsokAppendix E

Tool RequirementsTool Requirements

Building and DistributingBuilding and Distributing

Acknowledgments and HistoryAppendix F

The Gombe Mother-Infant Project AcknowledgmentsThe Gombe Mother-Infant Project Acknowledgments

The Babase AcknowledgmentsThe Babase Acknowledgments

The SokweDB AcknowledgementsThe SokweDB Acknowledgements

Isok Licensing Terms -- Licensed Under The AGPL v3.0+ (Examples Excepted)Appendix G

GNU Affero General Public License version 3 Appendix H Preamble the section called “ Preamble ” 0. Definitions. the section called “ 0. Definitions. ” 1. Source Code. the section called “ 1. Source Code. ” 2. Basic Permissions. the section called “ 2. Basic Permissions. ” 3. Protecting Users’ Legal Rights From Anti-Circumvention Law. the section called “ 3. Protecting Users’ Legal Rights From Anti-Circumvention Law. ” 4. Conveying Verbatim Copies. the section called “ 4. Conveying Verbatim Copies. ” 5. Conveying Modified Source Versions. the section called “ 5. Conveying Modified Source Versions. ” 6. Conveying Non-Source Forms. the section called “ 6. Conveying Non-Source Forms. ” 7. Additional Terms. the section called “ 7. Additional Terms. ” 8. Termination. the section called “ 8. Termination. ” 9. Acceptance Not Required for Having Copies. the section called “ 9. Acceptance Not Required for Having Copies. ” 10. Automatic Licensing of Downstream Recipients. the section called “ 10. Automatic Licensing of Downstream Recipients. ” 11. Patents. the section called “ 11. Patents. ” 12. No Surrender of Others’ Freedom. the section called “ 12. No Surrender of Others’ Freedom. ” 13. Remote Network Interaction; Use with the GNU General Public License. the section called “ 13. Remote Network Interaction; Use with the GNU General Public License. ” 14. Revised Versions of this License. the section called “ 14. Revised Versions of this License. ” 15. Disclaimer of Warranty. the section called “ 15. Disclaimer of Warranty. ” 16. Limitation of Liability. the section called “ 16. Limitation of Liability. ” 17. Interpretation of Sections 15 and 16. the section called “ 17. Interpretation of Sections 15 and 16. ” How to Apply These Terms to Your New Programs the section called “ How to Apply These Terms to Your New Programs ”

CC0 1.0 Universal DeedAppendix I

No CopyrightNo Copyright

Other InformationOther Information

NoticeNotice

CC0 1.0 Universal Appendix J