PostgreSQL RDBMS

Related topics
Product name
Publisher page
  • [PostgreSQL|PostgreSQL]
Category
Relational Database Management Systems
Release
TKU 2024-Jun-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 componentOS typeVersioningPattern depth
DatabaseServerUnixActive (command), File, Path, PackageInstance-based or grouped on command-line args (data dependent)
WindowsWMI 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

PatternTrigger nodeAttributeConditionArgument
DatabaseServerDiscoveredProcesscmdmatchesregex '\bpostmaster$'
or
cmdmatchesregex '\bpostgres$'
or
cmdmatchesregex '(?i)\bpg_ctl\.exe$'
or

cmdmatchesregex '(?i)\bpostgres\.exe'

Simple identification mappings

The following components and processes are identified by using the combination of pattern definitions and simple identity mappings:

NameCommand
PostgreSQL Database Serverregex '\bpostmaster$'
EnterpriseDB Postgres Advanced Serverregex '\bedb\-postmaster$'
PostgreSQL Database Server Control processregex '(?i)\bpg_ctl\.exe$'
PostgreSQL Database Serverregex '\bpostgres:$'
EnterpriseDB Postgres Advanced Serverregex '\bedb\-postgres$'
PostgreSQL Database Serverregex '(?i)\bpostgres\.exe$'
PostgreSQL Scheduling Agentregex '(?i)\bpagent\.exe$'
Postgres Enterprise Managerregex '(?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.

NameCommandOS
Executed command:<abs_path_to_binary>/postmaster --versionUnix
Executed command:<abs_path_to_binary>\pg_ctl.exe --versionWindows

The output is parsed by using the following regular expression: pg_bin_path

NameCommandOS
regex

'(PostgreSQL)\s+(\d+\.\d+(?:\.\d+)*)'

Unix
regex

'(PostgreSQL)\s+(\d+\.\d+(?:\.\d+)*)'

Windows

File versioning

If the pattern is unable to fetch the version from a previous versioning method, the pattern attempts to read the PG_VERSION file in the <postgres_data_path>. It obtains this path from the -D option in the trigger process.

The pattern parses the contents of the PG_VERSION file by using 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:

CommandPlatform
regex "/ppas\-(\d+(?:\.\d+)*)/"Unix
regex '/PostgresPlus/(\d+(?:\.\d+)*)AS/'
regex '/edb/as(\d+(?:\.\d+)*)/'

regex '(?i)PostgreSQL\\(\d+(?:\.\d+)*)\\bin\\(?:pg_ctl|postgres)\.exe$' 

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 expressionPlatform
(?i)^Postgres Plus Advanced ServerWindows
(?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

postgresql.conf

Many additional attributes come from the postgresql.conf file. The pattern obtains the path to this from the "--config-file" or "-c config file" arguments in the trigger process.

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 "-p" or "-c port" option of the trigger process. If this fails, the pattern obtains information from the port entry in the postgresql.conf configuration file.

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.

Was this page helpful? Yes No Submitting... Thank you

Comments