PostgreSQL RDBMS
- Product name
- Publisher page
-
- [PostgreSQL|PostgreSQL]
- Category
- Relational Database Management Systems
- Release
- TKU 2023-Oct-1
- More information
- Publisher link
Important
Starting from TKU December 2023, the PostgreSQL pattern no longer creates SoftwareCluster nodes. What PostgreSQL considers to be a cluster is different to what BMC Discovery considers to be a cluster. You can still find instance information on the SoftwareInstance.
Product description
PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, Unix (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.
Software pattern summary
| Product component | OS type | Versioning | Pattern depth |
|---|---|---|---|
| DatabaseServer | Unix | Active (command), File, Path, Package | Instance-based or grouped on command-line args (data dependent) |
| Windows | WMI Query, Active (command), File, Path, Package |
Platforms supported by the pattern
The current pattern identifies an instance of PostgreSQL Server running on both Unix/Linux and Microsoft Windows platforms.
Identification
Software instance triggers
| Pattern | Trigger node | Attribute | Condition | Argument |
|---|---|---|---|---|
| DatabaseServer | DiscoveredProcess | cmd | matches | regex '\bpostmaster$' |
| or | ||||
cmd | matches | regex '\bpostgres$' | ||
| or | ||||
cmd | matches | regex '(?i)\bpg_ctl\.exe$' | ||
| or | ||||
cmd | matches | regex '(?i)\bpostgres\.exe' | ||
Simple identification mappings
The following components and processes are identified by using the combination of pattern definitions and simple identity mappings:
| Name | Command |
|---|---|
| PostgreSQL Database Server | regex '\bpostmaster$' |
| EnterpriseDB Postgres Advanced Server | regex '\bedb\-postmaster$' |
| PostgreSQL Database Server Control process | regex '(?i)\bpg_ctl\.exe$' |
| PostgreSQL Database Server | regex '\bpostgres:$' |
| EnterpriseDB Postgres Advanced Server | regex '\bedb\-postgres$' |
| PostgreSQL Database Server | regex '(?i)\bpostgres\.exe$' |
| PostgreSQL Scheduling Agent | regex '(?i)\bpagent\.exe$' |
| Postgres Enterprise Manager | regex '(?i)\bpem\.exe$' |
Versioning
Version information for the product is currently collected by using one of three possible methods. The pattern executes the methods in order based on their accuracy, depth and reliability. When a result is obtained, the method lower in precedence is not attempted. In order of precedence the methods are as follows:
WMI versioning
(Windows only) The pattern attempts to obtain version information by executing the following WMI query against the root\CIMV2 namespace:
SELECT Version FROM CIM_DataFile where Name='<trigger_process_cmd>'
Active versioning — command execution
We have been able to identify one method to actively version this product. It provides a decent level of reliability on both the platforms and does not require special permissions.
Common functionality
The first step for active versioning is to determine whether the path of the trigger process is an absolute path. This is a mandatory condition under which active versioning is attempted. The pattern performs the check in the following ways:
- (Unix) Regular expression used to match against the trigger process command:
'^/' - (Windows) Regular expression used to match against the trigger process command:
'^\w:'
Command execution
The Unix specific version command is running postmaster command with the full path to it and the "--version" parameter.
The Windows specific version command is running the pg_ctl.exe command with the full path to it and the "--version" parameter.
| Name | Command | OS |
|---|---|---|
| Executed command: | <abs_path_to_binary>/postmaster --version | Unix |
| Executed command: | <abs_path_to_binary>\pg_ctl.exe --version | Windows |
The output is parsed by using the following regular expression: pg_bin_path
| Name | Command | OS |
|---|---|---|
| regex |
| Unix |
| regex |
| Windows |
File versioning
If the pattern in unable to fetch the version from a previous versioning method, the pattern attempts to read the PG_VERSION file in the <postgres_data_path> folder and parse its contents against the following regular expression:
(\d+(?:\.\d+)*)
Path versioning
If the pattern is unable to fetch the version from a previous versioning method, the pattern attempts to parse the process' command line to see if it can identify any installation version from the path by using the following regular expression:
| Command | Platform |
|---|---|
regex "/ppas\-(\d+(?:\.\d+)*)/" | Unix |
regex '/PostgresPlus/(\d+(?:\.\d+)*)AS/' | |
regex '/edb/as(\d+(?:\.\d+)*)/' | |
| Windows |
regex '(?i)\\PostgresPlus\\(\d+(?:\.\d+)*)AS\\' |
Normally, this approach returns a version unless the path was altered much during installation.
Package versioning
If the pattern is unable to extract the version from a previous versioning method, it attempts to query the package management system. The pattern supplies the package query with a set of the following regular expressions:
| Regular expression | Platform |
|---|---|
(?i)^Postgres Plus Advanced Server | Windows |
(?i)\bpostgres(?:ql)?\b | |
^postgresql-server$ | Unix |
^ppas\d+\-server(?:\-core|\-devel)?$ |
Normally, this approach returns a version if PostgreSQL was installed from a package on Windows or Unix.
Additional attributes
bind_address
This attribute is first tried to be extracted from the postgresql.conf file from the "listen_addresses =" section (the pattern obtains the path to this file from the -c option in the trigger process).
If the first approach fails or if listen_addresses is assigned to localhost or IP 127.0.0.1, then the pattern attempts to find a target value through the traverse to NetworkConnectionList on the BMC Helix Discovery appliance. The port value is used for such traverse.
data_dir
The pattern obtains the Postgres database path from the -D option in the trigger process.
instance
The pattern obtains instance information through one of the following methods:
- Parse the trigger process arguments with the regular expression
(?i)(.+):.*writer - Parse the arguments of a child of the trigger process with the same regular expression.
(For example, a process of "postgres: MyInstance: writer process" means an instance name of MyInstance).
Until TKU December 2023, the PostgreSQL pattern grouped SoftwareInstances with the same instance in a SoftwareCluster. This model was incorrect and hence deprecated.
Port
The pattern obtains port information from the port or -p option of the trigger process.
If this fails, the pattern obtains information from the port entry in the postgresql.conf configuration file (the pattern obtains the path to this file from the -c option in the trigger process).
State
The pattern attempts to get the state of the database: production or standby.
First, the pattern attempts to run the <abs_path_to_binary>/pg_controldata <data_dir> command. A returned value of “Database cluster state: in production” means a production database. A returned value of “Database cluster state: in archive recovery” means a standby database.
If this fails, the pattern runs a “select pg_is_in_recovery()” database query. A returned value of false means a production database. A returned value of true means a standby database.
This information is reported on the state attribute on the SoftwareInstance.
Application model produced by the software pattern
Product architecture
Following its startup, the PostgreSQL server launches a number of worker processes that handle multiple DB server functions.
Software pattern model
The pattern triggers on the postmaster or pg_ctl process (or edb-postmaster or edb-postgres in case of an EnterpriseDB installation) because these processes show the running state of PostgreSQL.
The pattern then attempts to determine data_dir and the port that the database query is listening on and uses all these as part of the SI key. If the key cannot be obtained, the pattern creates a grouped SI by using the trigger process command-line arguments.
The pattern creates association links with processes related to the trigger process (child processes started by the main process).
The current Windows pattern trigger assumes that PostgreSQL is running as a service (expected in production environment).
Obtaining detailed PostgreSQL database, schema and table information
A separate pattern has been created to query the PostgreSQL Server to obtain a database list and (optionally) database table details. For more information about this pattern, see PostgreSQL RDBMS - Database Detail Pattern.
Subject matter expertise
A Subject Matter Expert's input is welcome on any other potential approaches not discussed on improving the product versioning coverage of PostgreSQL RDBMS, especially with regard to EnterpriseDB installations.
Testing
The pattern is tested against the live installations on the Solaris, Unix, and Windows operating systems.
Open issues
There are no known open issues with this pattern.
Comments
Log in or register to comment.