Overview
This document is targeted towards covering basic PostgreSQL monitoring and health check related system objects like tables, views, etc. This allows simple monitoring of PostgreSQL database via some custom services, which I'll attach at the end of this document, from the ThingWorx Composer itself. I'll also try to cover short detail on some of the services that are included with the Thing: PostgreSQLHealthCheck which implements Database ThingTemplate
Pre-requisite
The document assumes that the user already has ThingWorx running with PostgreSQL as a Persistence Provider.
How to install
Usage for this is fairly straight forward, import the Entities.twx and it will create required Thing which implements Database ThingTemplate and some DataShapes. Each Service under the Thing: PostgreSQLHealthCheck has its own DataShape. Feel free edit these services / DataShapes if you are looking to use output of these services as part of your mashup(s).
Make sure to edit the PostgeSQL's JDBC Connection String, Username & password under the configuration section in order to connect to your PostgreSQL instance under the Thing PostgreSQLHealthCheck which will be created when Entities.twx is imported (attached with this blog)
Note : Users can use these services to query non-ThingWorx related database created with PostgreSQL as part of the external JDBC connection.
Reviewing Services from Thing: PostgreSQLHealthCheck
1. DescribeTableStructure
- Takes two inputs **Table Name** and the **Schema Name** in which the ThingWorx database tables exists both inputs have default values that can be modified to match your PostgreSQL schema setup and required table name
- It provides information on a Table's structure, see below
2. GetAllPSQLConfig
- Provides runtime details on all the configurations done in the postgresql.conf which are in-effect
- For detail on pg_settings see Postgresql 9.4 Doc
3. GetAllPSQLConfigLimited
Similar to GetAllPSQLConfig, however with limited information
4. GetAllPSQLRoles
- Lists all the database roles/users
- Also lists their access rights permissions together with OID
- Helpful in identifying if the role is active/inactive or carries any limitation on the DB connections
5. GetPG_Stat_Activity
- Part of the Statistics Collector subsystem for the PostgreSQL DB
- Shows current state of the schema e.g. connections, queries, etc.
- For more detail on the output refer to the PostgreSQL 9.4 doc
6. GetPSQLDBLocksInformation
- Shows the kind of locks in effect on which database and on which relation (table)
- Particularly useful in identifying the relations and what lock mode is enabled on them
7. GetPSQLDBStat
- Shows database wide statistics
- Like Commits, reads, block reads, tupples (rows) fetched, inserted, deadlocks, etc
- For more detail refer to PostgreSQL 9.4 doc
8. GetPSQLLogDesitnation
- Checks where the PostgreSQL server logs are directed to
- I.e. stderr, csvlog or syslog
- Default is stderr
9. GetPSQLLogFileName
- Fetches the log PostgreSQL log file name and the filename format
- E.g. postgresql-%Y-%m-%d_%H%M%S.log
10. GetPSQLLoggingLocation
- Fetches the location where the logs are stored for PostgreSQL
- e.g. pg_log, which is also the default location
- Desired location for the logs can be done in the postgresql.conf file
11. GetPSQLRelationIndexes
- Gets information on the Indexes
- Information like index size, number of rows, table names on which the index is created
12. GetPSQLReplicationStat
- Shows information related to the Replication on PostgreSQL DB
- Applicable to the PostgreSQL DBs where replication is enabled
13. GetPSQLTablespaceInfo
- Takes tablespace name as input (String DataType), service defaults to 'thingworx' - modify if needed
- Fetches information like owner oid, tablespace ACL
14. GetPSQLUserIndexIO
- Fetches index that are created only on the User created DB objects
- Shows relations (table) vs index relations ids (index on table), together with their names
- Also shows additional info like number of disk blocks read from this index & number of buffer hits in this index
15. GetPSQLUserSequencesIOStats
- Fetches informtion on Sequence objects used on user defined relations (tables)
- Number of disk blocks read from this sequence & buffer hits in this sequence
16. GetPSQLUserTableIOStat
- Fetches disk I/O information on the user created tables
17. GetPSQLUserTables
- Fetches all the user created tables, together with their
- name, OID
- Disk I/O
- Last auto vacuum , vacuum
- Also lists the amount of rows each relation (table) has
Finally
The attached entity has some additional service not yet covered in this blog, as they are minor services. Therefore for brevity of this blog I've left them out for now, feel free to explore or enhance this. I will continue to look for any additional services and will enhance this document and the entities belong to this.
If you are looking to enhance this feel free to fork from twxPostgreSqlHealthCheck over Github.
