PostgreSQL RDBMS - Database Detail Pattern - Documentation for BMC Discovery content reference
Jessica Cortez Starting from TKU September 2020 Database Schema node type and Database Table node type were updated to contain DB Server type. (DRDC1-14748)
e.g. SQL Database Schema type was changed to PostgreSQL Database Schema and SQL Database Table type was replaced with PostgreSQL Database Table.
The current pattern allows to discover and model all databases and tables for PostgreSQL Database Server or EnterpriseDB Postgres Advanced Server instances as Detail nodes.
Configuration Options
There are several configuration options available for this product:
| Option | Default value | Commentary |
|---|---|---|
| ignore_databases | ["postgres", "template0", "template1"] | List of databases to ignore. |
| ignore_schemas | ["pg_catalog", "information_schema"] | List of schemas to ignore. |
| model_schemas_and_tables | true | The pattern only creates schema and table nodes if this is true. Setting this option to true lengthens the runtime but provides a deeper model. |
| adminuser | Empty string | If the pattern cannot find the admin user from the pg_hba.conf file, it uses the value of this string. |
| active_method | true | Only runs commands such as pg_dumpall and psql if this is true. |
Queries Run
The pattern runs the following database queries. When setting up the PostgreSQL credential please ensure it has SELECT access to the relevant databases
| Query | Reason | Permission |
|---|---|---|
| select datname from pg_database | Get database list | select permission on pg_database |
| select schemaname, tablename from pg_tables | Get list of tables | select permission on pg_tables |
| select pg_database_size(db name) | Get database sizes | connect permission on relevant database (see publisher's docs) |
The pattern identifies databases and tables of PostgreSQL Database Server or EnterpriseDB Postgres Advanced Server running on Windows and UNIX platforms.
Software Instance Triggers
| Pattern | Trigger Node | Attribute | Condition | Argument |
|---|---|---|---|---|
| PostgreDatabasesAndTables | SoftwareInstance created, confirmed | type | in | "PostgreSQL Database Server", "EnterpriseDB Postgres Advanced Server" |
The ProstgreSQL database, schema, and table information are stored within the Discovery Model as DatabaseDetail Nodes.
Note
Current pattern module contains Configuration section which allows to disable discovery for specified databases and schemas.
By default the pattern skips:
- databases: "postgres", "template0", "template1"
- schemas: "pg_catalog", "information_schema"
Software Pattern Model
NOTE: Active methods are enabled by default. They can be disabled in configuration options.
Active method #1
The pattern tries to run a command which generates the dump of all PostgreSQL databases structures (without data) by means of command:
or if <base_si.bin_dir> doew not exist, then default value for UNIX host is used:
where <base_si.bin_dir> is 'bin_dir' attribute of trigger "PostgreSQL Database Server" or "EnterpriseDB Postgres Advanced Server" SI.
NOTE: If <adminuser> value is not specified in pattern Configuration section then default 'postgres' user is used. <adminuser> should have 'trust' access to all databases specified in pg_hba.conf file. Here is an example of "/var/lib/pgsql/data/pg_hba.conf" file' entry:
| DATABASE | USER | ADDRESS | METHOD |
| host | all | postgres | 127.0.0.1/32 | trust |
Command output is split by string "\\connect", where each such chunk represents dump for one specific database ("PostgreSQL Database") which is used for obtaining database name (db_name):
regex
'^\s+(\w+)'
then, each database dump is split by string "SET search_path" in order to get chunks for each available "PostgreSQL Database Schema" which is used for obtaining schema name (schema_name)
The tables list("PostgreSQL Database Table") is extracted from each schema chunk by regex:
Note
Table and Schema modeling can be disabled (leaving only database modeling) by switching to 'false' correspondent Configuration option in the pattern.
Active method #2
If the first active method failed ( pg_dumpall command does not have admin access) pattern tries to run the following command:
Windows:
"<bin_dir>\psql" -p <port%>-t -l
Unix:
"<bin_dir>/psql" -p <port> -t -lFrom the command output pattern extracts a list of DB names by means of regex:
- '(?m)^\s*(\w+)'
SQL Query method
Databases list("PostgreSQL Database") is obtained using sql query:
- "select datname from pg_database" , where each entry is a database name.
then, for each obtained database the pattern runs the following sql query:
- "select schemaname, tablename from pg_tables"
which obtains the list of all available schema names("PostgreSQL Database Schema") and tables names("PostgreSQL Database Table").
File method
The pattern also allows to obtain database list("PostgreSQL Database") from the file "<base_si.data_dir>/global/pg_database" by means of regex:
- '(?m)^"?(\w+)"?'
where <base_si.data_dir> is 'data_dir' attribute of "PostgreSQL Database Server" SI.
Additional Attributes
We report the size of the database with the db_size attribute on the Database node. This is obtained from database query SELECT pg_database_size('%db.instance%')
Pattern Trigger
PostgreDatabasesAndTables pattern triggers on each created or confirmed PostgreSQL Database Server Software Instance.
Detail keys
The detail key consists of the key of the parent PostgreSQL Database Server instance and the original database or table name, returned by the active command.
The pattern has been tested against live installations of the PostgreSQL Database Server product on Windows and Linux CentOS platforms.
[Go Back To Product Page | PostgreSQL RDBMS]
