New structure to allow "tuning" during deploy without using pg_tune

[gclough]

This isn't 100% finished, but it's an example of how we can use Ansible to tune PostgreSQL without the use of pg_tune.

When it's complete, it will be a fix for #201

[ls-initiatives]

This looks promising.

Unless I'm mistaken, pgtune is supported only on old versions of PG so no-one should have to object. So if I may suggest, you could completely replace the pgtune calls with your proposal, and take over the existing variables. This would provide forward compatibility for the role users.

[gclough]

Thanks for the comments @ls-initiatives . I've left the existing pgtune in there, just in case someone was relying on the output for their existing servers. I didn't want an update to this playbook to suddenly change all of their tuning parameters, and cause a performance problem. This parameter set should be better... but I figured it was best to play it safe and not change the existing behaviour if at all possible.

Once we get past supporting PostgreSQL v9.3, then yes, that code can be removed and dumped.

[gclough]

I think there is a problem... as I was relying on "host_vars" to enable me to override the values defined in here, but I've just realised that the "include_vars" is very high in the precedence list, so anything in group/host_vars of the inventory is overridden.

That's a big problem, because if you don't agree with the settings as provided by the tuning script then you have no option to override them except with an "--extra-vars" parameter, which is pretty awful.

Another awful solution is to include the inventory vars as a part of the play:

- name: PostgreSQL | Include Variables | Inventory, host_vars
  include_vars: "{{ item }}"
  with_first_found:
    - files:
      - "{{ inventory_dir }}/host_vars/{{ ansible_fqdn }}/vars"
      skip: true

... but IMHO, that's even worse, as it permanently breaks the expected variable precedence.

@jlozadad , @UnderGreen @sebalix... do you have any ideas?

QUESTION: How can I set per-host or per-group variables, which will override the settings from the tuning include?

[ls-initiatives]

Host vars can be arranged in a number of different path (inside/alongside the inventory dir, with/without .yml, with/without subdirectories, as executable scripts...) so including the host vars this way will work only for a small subset of inventories.

[gclough]

@ls-initiatives ... that's what I figured. It was a horrible, HORRIBLE kludge to get things working. My problem is that:

• Values populated by the "include_vars" of "vars/tune/*.yml" override pretty much everything

... but I want values in host_vars to take precedence over these. The tuning values will give good defaults 99% of the time, but we need the ability to make tweaks on a per-server basis. I can't see any sensible way to do that, other than having a separate file that I can include. I've used my inventory in the meantime, but what's the best way to get what I want?

• Use a separate file to include all higher precedence variables, and include them after vars/tune?
• Use different variable names in vars/tune, and then set them with something like <variable> | default(<variable>_tune)
• Something else?

Help!

[gclough]

@sebalix or @UnderGreen ... do you have any suggestions?

[ls-initiatives]

Wouldn't it make sense to simply put the optimized values as defaults ? Maybe only keeping the memory percentage low to avoid OOMs.

This way users who have already customized their host_vars won't be impacted, and the others will benefit from better performance when they upgrade the role ?

[gilesw]

I second just having these values as defaults. I've had a similar problem with vars overriding lookups:-

https://github.com/ansible/ansible/issues/25384

[gclough]

Thanks @Is-initiatives and @gilesw It looks like I'll need to try and revisit this... but how can I implement this type of hierarchy?

role defaults (Basic boilerplate, most likely a copy/paste from the PostgreSQL defaults)

version specific defaults (Different PostgreSQL versions sometimes have different defaults, so we need to cover those)

group defaults (Maybe you want different global settings for dev, test, prod, locations, etc)

tuning tweaks (the main crux of this change) (These need to override all of the previous settings, as the tuning script could change pretty much anything)

server specific (A final "I need a specific setting for this server", overriding the tuning script... just in case it's not always 100% correct for a particular use-case)

The only way I've found to do that, is to "include_vars" everything in the order I want... which is pretty awful IMHO.

[gilesw]

This is a common problem with ansible at the moment. The requirement to have dynamic defaults for different use cases which are still overridable at the group level:-

https://github.com/ansible/ansible/pull/23738

We have to hope that this gets passed I think.

[gilesw]

These might act as a better set of defaults:-

https://github.com/jberkus/annotated.conf/blob/master/annotated.csv

[github-actions[bot]]

This pr 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!