Pattern Goal

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:

  • ignore_databases := [ "postgres", "template0", "template1"]; - allows to ignore a list of databases
  • ignore_schemas := [ "pg_catalog", "information_schema"]; - allows to ignore a list of schemas
  • model_schemas_and_tables := true; - allows to retrieve tables for each schema
  • adminuser := ""; -User defined in PostreSQL Client Authentication Configuration File '<data_dir>/pg_hba.conf' with TYPE='local', DATABASE='all' and METHOD='trust'

Platforms Supported by the Pattern

The pattern identifies databases and tables of PostgreSQL Database Server or EnterpriseDB Postgres Advanced Server running on Windows and UNIX platforms.

Identification

Software Instance Triggers

PatternTrigger NodeAttributeConditionArgument
PostgreDatabasesAndTablesSoftwareInstance created, confirmedtypein"PostgreSQL Database Server", "EnterpriseDB Postgres Advanced Server"

Application Model Produced by Software Pattern

The ProstgreSQL database, schema and table information is 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

Active method

The pattern tries to run command which generates the dump of all PostgreSQL databases structures (without data) by means of command:

  • '"<base_si.bin_dir>/pg_dumpall" -U <adminuser> -s -x'

or if <base_si.bin_dir> doew not exist, then default value for UNIX host is used:

  • '/usr/bin/pg_dumpall -U <adminuser> -s -x'

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:

# TYPE

DATABASEUSERADDRESSMETHOD
hostallpostgres127.0.0.1/32trust

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 "SQL Database Schema" which is used for obtaining schema name (schema_name)

  • regex '^\s+=\s+(\w+)'
    if no schema_name was ontained then default "public" schema name is used.

The tables list("SQL Database Table") are extracted from each schema chunk by regex:

  • regex '(?i)CREATE TABLE\s+(\S+)\s+\('

Note

Table and Schema modeling can be disabled (leaving only databases modelling) by switching to 'false' correspondent Configuration option in the pattern.

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("SQL Database Schema") and tables names("SQL 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.

Pattern Trigger

PostgreDatabasesAndTables pattern triggers on the each created or confirmed PostgreSQL Database Server Software Instance.

Detail keys

Detail key consists of the key of the parent PostgreSQL Database Server instance and original database or table name, returned by active command.

Subject Matter Expertise

Subject Matter Expert input will be welcome on any other potential approaches not discussed to improve the depth of PostgreSQL Database Server - Database Detail Pattern.

Testing

The pattern has been tested against live installations of the PostgreSQL Database Server product on Windows and Linux CentOS platforms.

Open Issues

There are no known open issues with this pattern.

 


Created by: Vadym Ratniuk 30 Jan 2012
Reviewed by: Viktor Moyseyenko 21 Feb 2012

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