119 lines
14 KiB
Markdown
119 lines
14 KiB
Markdown
|
# 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/>• kB<br/>• 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/>• kB<br/>• 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/>• GB<br/>• MB<br/>• 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/>• MB<br/>• kB |
|
||
|
|
| **<a id="postgresql.effective_cache_size" name="postgresql.effective_cache_size">effective_cache_size</a>** | Sets the planner's assumption about the total size of the data caches.<br/>Sets the planner'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/>• MB<br/>• kB<br/>• GB |
|
||
|
|
|
||
|
|
### The group variable "accounts" - 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
|
||
|
|
```
|