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
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.
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
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.