Use CMDB as Config Inventory

Use a PostgreSQL metabase as a dynamic configuration source instead of static YAML files.

Overview

A CMDB (Configuration Management Database) stores configuration in database tables.

By default, Pigsty uses static pigsty.yml as the Ansible inventory.

A static file is simple. Larger deployments can need finer control, concurrent editing, and external integration beyond one file’s practical limits.

The sample pg-meta.meta database baseline defines the CMDB schema.

How It Works

CMDB replaces the static inventory with an executable script that returns JSON, as Ansible supports. Enabling CMDB creates inventory.sh:

#!/bin/bash
psql ${METADB_URL} -AXtwc 'SELECT text FROM pigsty.inventory;'

Whenever Ansible reads the inventory, this script queries pigsty.inventory in PostgreSQL and returns JSON.

The architecture is:

Data Model

The CMDB database schema is defined in files/cmdb.sql, with all objects in the pigsty schema.

Core Tables

Table Structure Details

Cluster Table pigsty.group

CREATE TABLE pigsty.group (
    cls     TEXT PRIMARY KEY,        -- Cluster name, primary key
    ctime   TIMESTAMPTZ DEFAULT now(), -- Creation time
    mtime   TIMESTAMPTZ DEFAULT now()  -- Modification time
);

Host Table pigsty.host

CREATE TABLE pigsty.host (
    cls    TEXT NOT NULL REFERENCES pigsty.group(cls),  -- Parent cluster
    ip     INET NOT NULL,                               -- Host IP address
    ctime  TIMESTAMPTZ DEFAULT now(),
    mtime  TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (cls, ip)
);

Global Variables Table pigsty.global_var

CREATE TABLE pigsty.global_var (
    key   TEXT PRIMARY KEY,           -- Variable name
    value JSONB NULL,                 -- Variable value (JSON format)
    mtime TIMESTAMPTZ DEFAULT now()   -- Modification time
);

Group Variables Table pigsty.group_var

CREATE TABLE pigsty.group_var (
    cls   TEXT NOT NULL REFERENCES pigsty.group(cls),
    key   TEXT NOT NULL,
    value JSONB NULL,
    mtime TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (cls, key)
);

Host Variables Table pigsty.host_var

CREATE TABLE pigsty.host_var (
    cls   TEXT NOT NULL,
    ip    INET NOT NULL,
    key   TEXT NOT NULL,
    value JSONB NULL,
    mtime TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (cls, ip, key),
    FOREIGN KEY (cls, ip) REFERENCES pigsty.host(cls, ip)
);

Core Views

These views expose configuration data:

pigsty.inventory converts database configuration to Ansible inventory JSON:

SELECT text FROM pigsty.inventory;

Utility Scripts

Use these three scripts to manage the CMDB:

inventory_load

Parse a YAML configuration file and import it into the CMDB:

bin/inventory_load                     # Load default pigsty.yml to default CMDB
bin/inventory_load -p /path/to/conf.yml  # Specify configuration file path
bin/inventory_load -d "postgres://..."   # Specify database connection URL
bin/inventory_load -n myconfig           # Specify configuration name

The script performs these operations:

1. Clears existing data in the pigsty schema
2. Parses the YAML configuration file
3. Writes global variables to the global_var table
4. Writes cluster definitions to the group table
5. Writes cluster variables to the group_var table
6. Writes host definitions to the host table
7. Writes host variables to the host_var table

Environment Variables

• PIGSTY_HOME: Pigsty installation directory, defaults to ~/pigsty
• METADB_URL: Database connection URL, defaults to service=meta

inventory_cmdb

Switch Ansible to use CMDB as the configuration source:

bin/inventory_cmdb

The script performs these operations:

1. Creates dynamic inventory script ${PIGSTY_HOME}/inventory.sh
2. Modifies ansible.cfg to set inventory to inventory.sh

The generated inventory.sh contains:

#!/bin/bash
psql ${METADB_URL} -AXtwc 'SELECT text FROM pigsty.inventory;'

inventory_conf

Switch back to a static YAML configuration file:

bin/inventory_conf

The script modifies ansible.cfg to set inventory back to pigsty.yml.

Usage Workflow

First-time CMDB Setup

1. Initialize the CMDB schema. Pigsty installation usually does this automatically:

psql -f ~/pigsty/files/cmdb.sql

2. Load configuration into the database:

bin/inventory_load

3. Switch to CMDB mode:

bin/inventory_cmdb

4. Verify configuration:

ansible all --list-hosts          # List all hosts
ansible-inventory --list          # View complete inventory

Query Configuration

After you enable CMDB, use SQL to query the configuration:

-- View all clusters
SELECT cls FROM pigsty.group;

-- View all hosts in a cluster
SELECT ip FROM pigsty.host WHERE cls = 'pg-meta';

-- View global variables
SELECT key, value FROM pigsty.global_var;

-- View cluster variables
SELECT key, value FROM pigsty.group_var WHERE cls = 'pg-meta';

-- View all PostgreSQL clusters
SELECT cls, name, pg_databases, pg_users FROM pigsty.pg_cluster;

-- View all PostgreSQL instances
SELECT cls, ins, ip, seq, role FROM pigsty.pg_instance;

-- View all database definitions
SELECT cls, datname, owner, encoding FROM pigsty.pg_database;

-- View all user definitions
SELECT cls, name, login, superuser FROM pigsty.pg_users;

Modify Configuration

Use SQL to change the configuration directly:

-- Add new cluster
INSERT INTO pigsty.group (cls) VALUES ('pg-new');

-- Add cluster variable
INSERT INTO pigsty.group_var (cls, key, value)
VALUES ('pg-new', 'pg_cluster', '"pg-new"');

-- Add host
INSERT INTO pigsty.host (cls, ip) VALUES ('pg-new', '10.10.10.20');

-- Add host variables
INSERT INTO pigsty.host_var (cls, ip, key, value)
VALUES ('pg-new', '10.10.10.20', 'pg_seq', '1'),
       ('pg-new', '10.10.10.20', 'pg_role', '"primary"');

-- Modify global variable
UPDATE pigsty.global_var SET value = '"new-value"' WHERE key = 'some_param';

-- Delete cluster (cascades to hosts and variables)
DELETE FROM pigsty.group WHERE cls = 'pg-old';

Configuration changes are immediately available. You do not need to reload or restart a service.

Switch Back to Static Configuration

Switch back to a static configuration file:

bin/inventory_conf

Advanced Usage

Export Configuration

Export CMDB configuration to YAML format:

psql service=meta -AXtwc "SELECT jsonb_pretty(jsonb_build_object('all', jsonb_build_object('children', children, 'vars', vars))) FROM pigsty.raw_config;"

Or use the ansible-inventory command:

ansible-inventory --list --yaml > exported_config.yml

Configuration Auditing

Track configuration changes using the mtime field:

-- View recently modified global variables
SELECT key, value, mtime FROM pigsty.global_var
ORDER BY mtime DESC LIMIT 10;

-- View changes after a specific time
SELECT * FROM pigsty.group_var
WHERE mtime > '2024-01-01'::timestamptz;

Integration with External Systems

CMDB uses standard PostgreSQL. You can integrate it with these systems:

• Web Management Interface: Expose configuration data through a REST API, such as PostgREST.
• CI/CD Pipelines: Read and write database data directly from deployment scripts.
• Monitoring & Alerting: Generate monitoring rules from configuration data.
• ITSM Systems: Synchronize data with enterprise CMDB systems.

Considerations

1. Data Consistency: After you change configuration, run the corresponding Ansible playbooks again to apply it to the environment.

2. Backup: CMDB configuration data is critical. Back it up regularly.

3. Permissions: Set appropriate CMDB database access permissions to prevent accidental changes.

4. Transactions: Make batch configuration changes in a transaction. This permits rollback if an error occurs.

5. Connection Pooling: inventory.sh opens a new connection each time it runs. Consider connection pooling if Ansible runs frequently.

Summary

Use CMDB for large cluster deployments, complex queries, external integration, or fine-grained access control. PostgreSQL stores the configuration and provides database functions to manage it.