PostgreSQL Metadata Storage
Relevant source files
The following files were used as context for generating this wiki page:
PostgreSQL serves as the primary metadata storage engine for the Percona Monitoring and Management (PMM) server. It is responsible for persisting the inventory of monitored entities, system settings, backup metadata, and the state of asynchronous jobs and actions.
The storage system is managed by the pmm-managed component, which handles database migrations, connection pooling, and data encryption for sensitive credentials. PMM requires a minimum PostgreSQL version of 14 to function as its internal metadata store.
Sources: managed/models/database.go:50-52, README.md
Database Architecture and Schema
The PMM metadata store uses a relational model to track the relationships between physical or virtual infrastructure and the monitoring services running on them. The schema is organized around four core entities: Nodes, Services, Agents, and Jobs.
Core Entity Relationships
The architecture follows a hierarchical structure where agents are associated with either specific services or nodes.
The diagram shows the relational structure where Nodes host Services, and both can be associated with Agents. Jobs and Backup Artifacts are linked to the specific Services they operate upon.
Sources: managed/models/database.go:87-160
Inventory Tables
| Table | Description | Key Fields |
|---|---|---|
nodes | Represents physical or virtual hosts. | node_id, node_type, address, machine_id |
services | Represents database instances (MySQL, MongoDB, PostgreSQL, etc.). | service_id, service_type, node_id, cluster |
agents | Represents monitoring exporters or the PMM Agent itself. | agent_id, agent_type, pmm_agent_id, runs_on_node_id |
action_results | Stores the outcome of short-lived queries like EXPLAIN or SHOW. | id, pmm_agent_id, output (bytea) |
Sources: managed/models/database.go:94-165
Migration System
PMM implements a custom version-based migration system defined within the Go source code. Migrations are executed sequentially during server startup to ensure the schema matches the application requirements.
Migration Logic
- The system checks the
schema_migrationstable to determine thecurrentVersion. - It iterates through the
databaseSchemaslice starting fromcurrentVersion + 1. - Each migration version contains a set of DDL statements (e.g.,
CREATE TABLE,ALTER TABLE). - Migrations are wrapped in a single PostgreSQL transaction to ensure atomicity.
This flowchart represents the sequential execution of schema updates during the initialization of the pmm-managed component.
Sources: managed/models/database.go:83-755, managed/models/database.go:815-845
Data Security and Encryption
The metadata storage handles sensitive information, including database passwords and cloud provider access keys. PMM utilizes an internal encryption layer to protect specific columns before they are written to disk.
Encrypted Columns
The system identifies specific columns in the agents table for automatic encryption:
usernameandpasswordagent_passwordaws_options(Access/Secret keys)azure_optionsmongo_options,mysql_options,postgresql_options
Sources: managed/models/database.go:66-81
Job and Backup Management
PostgreSQL tracks long-running operations and their associated data through a dedicated set of tables introduced in later schema versions (v30+).
Asynchronous Jobs
The jobs table (formerly job_results) tracks the execution of complex tasks like backups and restores.
- Jobs: Stores the status (
done), job type, and structured result data (JSONB). - Job Logs: Stores stdout/stderr output in chunks, enabling real-time log streaming through the UI.
Backup Metadata
Metadata for database backups is distributed across:
backup_locations: Configuration for S3 buckets or local filesystems.artifacts: Records of specific backup files, including their vendor, data model, and cluster membership.restore_history: Audit trail of restore operations linking artifacts to target services.
Sources: managed/models/database.go:417-434, managed/models/database.go:489-514, managed/models/database.go:564-573
Monitored PostgreSQL Metrics
PMM monitors its own internal PostgreSQL metadata storage (and external PostgreSQL instances) using specific metric collectors defined in the API and visualized in dashboards.
Key Storage Metrics
The system collects detailed Write-Ahead Log (WAL) and Buffer usage metrics to ensure the health of the metadata store:
| Metric Group | Source Parameters | Description |
|---|---|---|
| Shared Buffers | pg_stat_bgwriter_buffers_alloc | Efficiency of shared memory buffer allocation. |
| Checkpoints | checkpoint_timeout, max_wal_size | Timing and frequency of data synchronization to disk. |
| WAL Retention | wal_keep_size, wal_segment_size | Disk space consumed by transaction logs. |
| QAN Metrics | m_shared_blks_hit_sum_per_sec | Block cache performance for Query Analytics. |
Sources: api/qan/v1/qan.proto:115-125, managed/data/checks/postgresql_wal_retention_check.yml:10-15, dashboards/dashboards/PostgreSQL/PostgreSQL_Checkpoints_Buffers_WAL_Usage.json:115-150
Action Parameters
When PMM-Agent interacts with PostgreSQL for diagnostics, it uses the following request structures:
PostgreSQLShowCreateTableParams: Used to retrieve table definitions.PostgreSQLQuerySelectParams: Used to execute specific diagnostic queries.PostgreSQLShowIndexParams: Used to analyze indexing state.
Sources: api/agent/v1/agent.pb.go:1210-1250
Summary
The PostgreSQL Metadata Storage is the backbone of PMM's operational state. By utilizing a versioned migration system and integrated encryption, it provides a robust and secure environment for managing database monitoring inventory. Its relational structure allows PMM to correlate nodes, services, and monitoring agents, while its job tracking tables enable complex asynchronous operations like global database backups and restores.