# risotto.postgresql - Configure the PostgreSQL server

This repository contains the `risotto.postgresql` Ansible Collection.

PostgreSQL is the World's Most Advanced Open Source Relational Database.

This collection allows you to configure a PostgreSQL server and create user
accounts.

## Variables

### The group variable "postgresql" - Configure the PostgreSQL server

| Variable                                                                                                                   | Description                                                                                                                                                                                                                                                                                       | Default value   | Type                                                                                            | Validator                                               |
|----------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------|-------------------------------------------------------------------------------------------------|---------------------------------------------------------|
| **<a id="postgresql.max_connections" name="postgresql.max_connections">max_connections</a>**                               | The maximum number of concurrent connections.                                                                                                                                                                                                                                                     | 100             | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.authentication_timeout" name="postgresql.authentication_timeout">authentication_timeout</a>**          | The maximum allowed time to complete client authentication.<br/>In seconds.                                                                                                                                                                                                                       | 60              | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.autovacuum" name="postgresql.autovacuum">autovacuum</a>**                                              | Starts the autovacuum subprocess.                                                                                                                                                                                                                                                                 | true            | [`boolean`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.work_mem" name="postgresql.work_mem">work_mem</a>**                                                    | The maximum memory to be used for query workspaces.<br/>Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.                                                                                             | 4               | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.work_mem_unit" name="postgresql.work_mem_unit">work_mem_unit</a>**                                     | Unit of work_mem.                                                                                                                                                                                                                                                                                 | MB              | [`choice`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory`  | **Choices**: <br/>•&nbsp;kB<br/>•&nbsp;MB               |
| **<a id="postgresql.maintenance_work_mem" name="postgresql.maintenance_work_mem">maintenance_work_mem</a>**                | The maximum memory to be used for maintenance operations.<br/>Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.                                                                                         | 64              | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.maintenance_work_mem_unit" name="postgresql.maintenance_work_mem_unit">maintenance_work_mem_unit</a>** | Unit of maintenance_work_mem parameter.                                                                                                                                                                                                                                                           | MB              | [`choice`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory`  | **Choices**: <br/>•&nbsp;kB<br/>•&nbsp;MB               |
| **<a id="postgresql.wal_buffers" name="postgresql.wal_buffers">wal_buffers</a>**                                           | The number of disk-page buffers in shared memory for WAL.<br/>The amount of shared memory used for WAL data that has not yet been written to disk (The default setting of -1 selects a size equal to 1/32nd of shared_buffers, but not less than 64kB nor more than the size of one WAL segment). | -1              | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.max_wal_size" name="postgresql.max_wal_size">max_wal_size</a>**                                        | The WAL size that triggers a checkpoint.<br/>Maximum (soft limit) size to let the WAL grow during automatic checkpoints.                                                                                                                                                                          | 2               | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.max_wal_size_unit" name="postgresql.max_wal_size_unit">max_wal_size_unit</a>**                         | Unité de la limite douce du Write Ahead Log.                                                                                                                                                                                                                                                      | GB              | [`choice`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory`  | **Choices**: <br/>•&nbsp;GB<br/>•&nbsp;MB<br/>•&nbsp;kB |
| **<a id="postgresql.shared_buffers" name="postgresql.shared_buffers">shared_buffers</a>**                                  | The number of shared memory buffers used by the server.                                                                                                                                                                                                                                           | 128             | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.shared_buffers_unit" name="postgresql.shared_buffers_unit">shared_buffers_unit</a>**                   | Unit of shared_buffers.                                                                                                                                                                                                                                                                           | MB              | [`choice`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory`  | **Choices**: <br/>•&nbsp;MB<br/>•&nbsp;kB               |
| **<a id="postgresql.effective_cache_size" name="postgresql.effective_cache_size">effective_cache_size</a>**                | Sets the planner&#x27;s assumption about the total size of the data caches.<br/>Sets the planner&#x27;s assumption about the effective size of the disk cache that is available to a single query.                                                                                                | 4               | [`integer`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |                                                         |
| **<a id="postgresql.effective_cache_size_unit" name="postgresql.effective_cache_size_unit">effective_cache_size_unit</a>** | Unit of effective_cache_size.                                                                                                                                                                                                                                                                     | GB              | [`choice`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory`  | **Choices**: <br/>•&nbsp;MB<br/>•&nbsp;kB<br/>•&nbsp;GB |

### The group variable &quot;accounts&quot; - Accounts to the PostgreSQL server

| Variable                                                         | Description                | Type                                                                                                          | Validator                     |
|------------------------------------------------------------------|----------------------------|---------------------------------------------------------------------------------------------------------------|-------------------------------|
| **<a id="accounts.remotes" name="accounts.remotes">remotes</a>** | PostgreSQL client address. | [`domainname`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `multiple` `mandatory` | `unique`<br/>Type domainname. |

#### Account for *example*

> [!NOTE]
> 
> This family builds families dynamically.\
> **Path**: remote_*example*\
> **Identifiers**: the value of the variable "[PostgreSQL client address](#accounts.remotes)".

| Variable                                                                                                                             | Description                             | Type                                                                                              |
|--------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------|---------------------------------------------------------------------------------------------------|
| **<a id="accounts.remote_:::identifier:::.database" name="accounts.remote_:::identifier:::.database">remote_*example*.database</a>** | PostgreSQL database name for *example*. | [`string`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory`    |
| **<a id="accounts.remote_:::identifier:::.username" name="accounts.remote_:::identifier:::.username">remote_*example*.username</a>** | PostgreSQL username for *example*.      | [`UNIX user`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory` |
| **<a id="accounts.remote_:::identifier:::.password" name="accounts.remote_:::identifier:::.password">remote_*example*.password</a>** | PostgreSQL password for *example*.      | [`secret`](https://rougail.readthedocs.io/en/latest/variable.html#variables-types) `mandatory`    |

## Usage

### Example playbook with Rougail

Add to your structural file something like:

```yaml
%YAML 1.2
---
version: 1.1
my_postgresql:
  type: postgresql
my_accounts:
  type: accounts
...
```

> [!NOTE]
> 
> Do not forget to add Rougail structural file as Rougail types.

For example you can add an YAML user data with something like:

```yaml
---
my_accounts:
  remotes:                              # PostgreSQL client address
    - example.net
  remote_example_net:                   # Account for example.net
    database: example                   # PostgreSQL database name for example.net
    username: username                  # PostgreSQL username for example.net
    password: secrets                   # PostgreSQL password for example.net
```

Add to your playbook:

```yaml
---
- name: Configure the PostgreSQL server
  hosts: servers
  vars:
    postgresql: '{{ my_postgresql }}'
    accounts: '{{ my_accounts }}'
  roles:
    - role: risotto.postgresql
```

### Example playbook without Rougail

> [!NOTE]
> 
> The variables will not be properly validated without Rougail.

```yaml
---
- name: Configure the PostgreSQL server
  hosts: servers
  vars:
    accounts:
      remotes:                        # PostgreSQL client address
        - example.net
      remote_example_net:             # Account for example.net
        database: example             # PostgreSQL database name for example.net
        username: username            # PostgreSQL username for example.net
        password: secrets             # PostgreSQL password for example.net
  roles:
    - role: risotto.postgresql
```