PostgreSQL Database & State

Relevant source files

The following files were used as context for generating this wiki page:

• lib/extensions/ar_adapter/ar_dba.rb
• lib/task_helpers/development/replication.rb
• lib/manageiq/environment.rb
• spec/lib/tasks/evm_application_spec.rb
• spec/models/miq_server/log_management_spec.rb

PostgreSQL serves as the primary relational data store and state management engine for the ManageIQ platform. It tracks the configuration, performance metrics, and operational state of all managed resources across hybrid IT environments. The system utilizes advanced PostgreSQL features such as pglogical for multi-region replication and provides deep introspective tools for database administration (DBA) tasks directly through the application's ActiveRecord adapters.

The database architecture is designed to handle high-concurrency workloads by monitoring client connections, table bloat, and index performance. It also supports automated maintenance routines like vacuuming and reindexing to ensure long-term stability and performance.

Database Administration & Introspection

ManageIQ extends the standard ActiveRecord::ConnectionAdapters::PostgreSQLAdapter to include comprehensive DBA capabilities. These extensions allow the system to programmatically query internal PostgreSQL catalogs (such as pg_stat_activity, pg_stat_all_tables, and pg_class) to retrieve real-time health and performance data.

Connection & Process Monitoring

The system monitors active database connections and individual backend processes (SPIDs). It can identify client addresses, wait events, and currently executing queries.
Sources: [lib/extensions/ar_adapter/ar_dba.rb:11-25](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/extensions/ar_adapter/ar_dba.rb#L11-L25)

Bloat and Performance Analysis

A significant portion of the DBA logic is dedicated to detecting "bloat" in tables and indexes—space that is occupied by deleted or obsolete rows but not yet reclaimed. This analysis uses complex calculations involving reltuples, relpages, and block_size to determine wasted_size and percent_bloat.
Sources: [lib/extensions/ar_adapter/ar_dba.rb:43-60](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/extensions/ar_adapter/ar_dba.rb#L43-L60), [lib/extensions/ar_adapter/ar_dba.rb:141-155](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/extensions/ar_adapter/ar_dba.rb#L141-L155)

Database Statistics Summary

The following table summarizes the key metrics collected through the table_statistics and table_size methods:

Metric	SQL Source Column	Description
Table Scans	seq_scan	Number of sequential scans initiated on the table.
Index Scans	idx_scan	Number of index scans initiated on the table.
Rows Live	n_live_tup	Number of live rows currently in the table.
Rows Dead	n_dead_tup	Number of dead rows (to be reclaimed by vacuum).
Last Vacuum	last_vacuum	Timestamp of the last manual vacuum.
Wasted Bytes	Calculated	Estimate of bytes wasted due to table bloat.

Sources: [lib/extensions/ar_adapter/ar_dba.rb:305-325](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/extensions/ar_adapter/ar_dba.rb#L305-L325), [lib/extensions/ar_adapter/ar_dba.rb:358-368](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/extensions/ar_adapter/ar_dba.rb#L358-L368)

Database State and Lifecycle

The lifecycle of the database within a ManageIQ environment involves initial provisioning, seeding of primordial data, and ongoing migration to match software updates.

Deployment States

The application determines its deployment state based on the presence of a seeded server and the current migration version of the database.

Environment Management

The ManageIQ::Environment module handles the physical setup of database files and configurations during installation or updates.

• Configuration: Copying config/database.pg.yml to config/database.yml.
• Lifecycle Tasks: db:create, db:migrate, and db:seed.
  Sources: [lib/manageiq/environment.rb:46-56](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/manageiq/environment.rb#L46-L56), [lib/manageiq/environment.rb:114-128](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/manageiq/environment.rb#L114-L128)

Multi-Region Replication

ManageIQ supports a distributed architecture where multiple "Remote" regions replicate data to a single "Global" region using PostgreSQL logical replication (pglogical).

Replication Setup Flow

The replication process involves creating publications on remote nodes and subscriptions on the global node.

Replication Parameters

Replication setup requires specific connection parameters to establish the bridge between regions.

• PG_USER: Default "root"
• PG_PORT: Default "5432"
• DB_PREFIX: "development_replication"
  Sources: [lib/task_helpers/development/replication.rb:11-15](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/task_helpers/development/replication.rb#L11-L15)

Log Management and Data Persistence

Database logs and state are critical for troubleshooting. The system manages PostgreSQL log patterns and allows for their collection and upload to central depots.

Log Patterns

The system identifies database-specific logs for collection, specifically targeting:

• *.conf files in the data directory.
• Files within the log/ subdirectory of the PostgreSQL data directory.
• ManageIQ-specific PostgreSQL configurations in /etc/manageiq/postgresql.conf.d/*.
  Sources: [spec/models/miq_server/log_management_spec.rb:79-88](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/spec/models/miq_server/log_management_spec.rb#L79-L88)

Conclusion

The PostgreSQL Database & State management in ManageIQ is a robust system that combines standard ORM practices with low-level database administration. By exposing bloat metrics, connection states, and replication health, the platform provides administrators with the tools necessary to maintain a performant and reliable data layer across complex hybrid environments.
Sources: [lib/extensions/ar_adapter/ar_dba.rb:436-460](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/lib/extensions/ar_adapter/ar_dba.rb#L436-L460), [spec/lib/tasks/evm_application_spec.rb:12-20](https://github.com/datacosmos-br/manageiq/blob/9e90a9f9edc058160915323117bc7e9cdcda0636/spec/lib/tasks/evm_application_spec.rb#L12-L20)