# CMDB

> Use PostgreSQL as config inventory
---

Pigsty allows you to use a **database (CMDB)** as a dynamic configuration source instead of a static configuration file.
You can use the built-in PostgreSQL as [config inventory](/docs/config/inventory) for config management.

With Postgres CMDB, configuration is organized in structured relational tables, which can be easily queried and manipulated using SQL.
This allows for easier integration with other systems and tools.



------

## How does it work?

Ansible allows you to use a [**dynamic inventory**](https://docs.ansible.com/ansible/latest/inventory_guide/intro_dynamic_inventory.html) script to generate the inventory config on-the-fly.

The idea is to replace static `pigsty.yml` in [`ansible.cfg`](https://github.com/pgsty/pigsty/blob/v3.7.0/ansible.cfg) with a dynamic shell script [`inventory.sh`](https://github.com/pgsty/pigsty/blob/v3.7.0/bin/inventory_cmdb#L30)


```bash {title="~/pigsty/ansible.cfg"}
---
inventory = pigsty.yml
+++
inventory = inventory.sh
```

The content of `inventory.sh` is very simple, it will query the PostgreSQL CMDB and retrieve config.

```bash {title="~/pigsty/inventory.sh"}
psql ${METADB_URL} -AXtwc 'SELECT text FROM pigsty.inventory;'
```

**Util Scripts for CMDB**

- [`bin/inventory_load`](https://github.com/pgsty/pigsty/blob/v3.7.0/bin/inventory_load): Loads YAML config file into the CMDB
- [`bin/inventory_cmdb`](https://github.com/pgsty/pigsty/blob/v3.7.0/bin/inventory_cmdb): Use CMDB as config inventory (`meta`.`pigsty`)
- [`bin/inventory_conf`](https://github.com/pgsty/pigsty/blob/v3.7.0/bin/inventory_conf): Use YAML file as config inventory (`pigsty.yml`)


------

## CMDB Schema

The CMDB baseline schema is shipped with pigsty: [`files/cmdb.sql`](https://github.com/pgsty/pigsty/blob/v3.7.0/files/cmdb.sql)
And most of the default config templates will use it as example baseline. Which means it can be used by default.

```yaml
all:
  children:
    pg-meta:
      hosts:
        10.10.10.10: { pg_seq: 1, pg_role: primary }
      vars:
        pg_cluster: pg-meta
        pg_databases:
          - name: meta
            baseline: cmdb.sql  # <--- use this as database schema baseline
```


--------

## Load Config Data

CMDB is empty by default, load config file into the CMDB with the [`bin/inventory_load`](https://github.com/pgsty/pigsty/blob/v3.7.0/bin/inventory_load) script.

Run `bin/inventory_load` without arguments will load the default `pigsty.yml` into the default CMDB.

```bash
usage: inventory_load [-h] [-p PATH] [-d CMDB_URL]

load config arguments

optional arguments:
  -h, --help            show this help message and exit
  -p PATH, --path PATH  config path, ${PIGSTY_HOME}/pigsty.yml by default
  -d DATA, --data DATA  postgres cmdb pgurl, ${METADB_URL} by default
```

Use `-p` to specify the config file path, and `-d` to specify the CMDB URL.

```bash
bin/inventory_load
bin/inventory_load -p conf/demo.yml
bin/inventory_load -p conf/ha/full.yml -d postgresql://dbuser_meta:DBUser.Meta@10.10.10.10:5432/meta
```


--------

## Switch Inventory

You can switch to dynamic CMDB inventory with:

```bash
bin/inventory_cmdb
```

Which essentially changes the `inventory` parameter in the `ansible.cfg` to use the `inventory.sh` script.