[suggestion] Better postgresql configuration management

[t0k4rt]

Hello, First thanks for your ansible projet ! I noticed that postgresql configuration changes between version can be really painful to manage. In order to better manage postgresql current and new versions, we could use postgresql to generate our yml configuration file.

For example, if you use this command on a postgresql 10 instance

psql -U postgres -t postgres -c "
    select 
        name 
        || ': \"{{postgresql_' 
        || name::text 
        || ' | default(None)}}\"' from pg_settings;" > postgresql_params_10.yml

This would generate a file containing exact parameters name for this given postgresql version. This would create 'None' values with this specific parameter name scheme : postgresql_{{param name}}.

Then to generate a proper postgresql.conf file we could use something like this template:

{% for key,value in postgresql_conf.items() %}
{% if value%}
{% if value is string %}
{{key}} = '{{value}}'
{% else if value is number %}
{{key}} = '{{value}}'
{% else %}
{{key}} = {{ value | ternary('on', 'off') }}
{% endif %}
{% endif %}
{% endfor %}

Using None values would allow generate a conf file with parameters that would only be set by the user in a yml file.

In your configure.yml, you would have to do something like this:

- include_vars: postgresql_params_{{postgresql_version}}.yml
- name: PostgreSQL | Update configuration - pt. 2 (postgresql.conf)
  template:
    src: "postgresql.conf.j2" # same template for all postgresql versions !
    dest: "{{postgresql_conf_directory}}/postgresql.conf{% if postgresql_pgtune %}.untuned{% endif %}"
    owner: "{{ postgresql_service_user }}"
    group: "{{ postgresql_service_group }}"
    mode: 0640
  register: postgresql_configuration_pt2

this would have these advantages:

• easier postgresql parameters generation
• improve reliability when including new postgresql version and generating parameters for a specific version
• one postgresql.conf template for all postgresql versions

What do you think about this ?

[gclough]

Thanks for taking an interest in the project @t0k4rt.

IMHO I do prefer to have the end-result as a file with the full comments as it comes from the default install. If the output of this suggestion is a file with only the key/value pairs that are not defaults, with no other comments, then whilst I think it makes the role cleaner, it would make it harder for people that are reading the resulting postgresql.conf.

Is there a simple way to achieve both? I don't think there is, because the config file changes between each version. If it's possible, then I'd be happy to learn another way...

[t0k4rt]

it's possible to achieve both The only problem with default params is type casting with jinja which can lead to get default values with the wrong type The line should be commented when we use default value so that it gives the user a hint without risking having the wrong type (I'm not sure this point is clear).

I was able to generate this kind of file with a postgresql request

postgresql_conf:
- key: authentication_timeout
  default_value: 60
  value: "{{postgresql_authentication_timeout | default(None)}}"
  unit: s
  comment: Sets the maximum allowed time to complete client authentication. Default value: 60
- key: autovacuum
  default_value: on
  value: "{{postgresql_autovacuum | default(None)}}"
  unit: 
  comment: Starts the autovacuum subprocess. Default value: on
- key: autovacuum_analyze_scale_factor
  default_value: 0.1
  value: "{{postgresql_autovacuum_analyze_scale_factor | default(None)}}"
  unit: 
  comment: Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. Default value: 0.1

Then with this template:

{% for item in postgresql_conf %}
## {{ item.comment }}
{% if not item.value %}
{% if item.default_value is string %}
# {{item.key}} = '{{item.default_value}}'
{% else if item.value is number %}
# {{item.key}} = {{item.default_value}}{{item.unit}}
{% else %}
# {{item.key}} = {{ item.default_value | ternary('on', 'off') }}
{% endif %}
{% else %}
{% if item.value is string %}
{{item.key}} = '{{item.value}}'
{% else if item.value is number %}
{{item.key}} = {{item.value}}{{item.unit}}
{% else %}
{{item.key}} = {{ item.value | ternary('on', 'off') }}
{% endif %}
{% endif %}
{% endfor %}

(not tested)

this could generate something like this when a value is not set (None):

## Sets the maximum allowed time to complete client authentication. Default value: 60
# authentication_timeout: 60s

and this when a value is set:

## Minimum number of tuple inserts, updates, or deletes prior to analyze. Default value: 50
autovacuum_analyze_threshold: 40

So the postgresql.conf would almost look normal, param comment would still be here with default value.

this can be tweaked to get to something that suits you more.

[gclough]

Interesting. I'm a bit slammed on other work for the next 3 weeks, but I'll definitely take a look. Thanks.

[t0k4rt]

no problems, I'll try to submit a PR with a proof of concept

[github-actions[bot]]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

[github-actions[bot]]

This issue has been closed due to inactivity. If you feel this is in error, please reopen the issue or file a new issue with the relevant details.