Dariusz Dwornikowski: Automatic PostgreSQL config with Ansible
If for some reasons you can t use dedicated DBaaS for your PostgreSQL (like AWS RDS) then you need
to run your database server on a cloud instance. In these kind of setup, when you scale up or down
your instance size, you need to adjust PostgreSQL parameters according to the changing RAM size.
There are several parameters in PostgreSQL that highly depend on RAM size. An example is
Now we need the template, where are the calculations take place. RAM size will be taken from the
Ansible s fact called
You can now run the role every time you change the instance size, and the config will be changed
accordingly to the RAM size. You can extend the role and maybe add other constraints and change
And run it:
The complete role can be found in my github repo.
shared_buffers
for which a rule of thumb says that is should be set to 0.25*RAM.
In DBaaS, when you scale the DB instance up or down, parameters are adjusted for you by the cloud
provider, e.g. AWS RDS uses parameter groups for that reason, where particular parameters are
defined depending on the size of the RAM of the RDS instance.
So what can you when you do not have RDS or any other DBaaS? You can always keep several
configuration files on your instance, each for a different memory size, you can rewrite you config
every time you change the size of the instance or you can use Ansible role for that.
Our Ansible role will be very simple, we will have two tasks. One will change the PostgreSQL config,
the second one will just restart the database server:
---
- name: Update PostgreSQL config
template: src=postgresql.conf.j2 dest=/etc/postgresql/9.5/main/postgresql.conf
register: pgconf
- name: Restart postgresql
service: name=postgresql state=restarted
when: pgconf.changed
ansible_memtotal_mb
. Since it returns RAM size in MBs, we will stick to MBs.
We will define the following parameters, you can adjust them to your needs:
shared_buffers
, as 0.25*RAM size,work_mem
, asshared_buffers/max_connections
,maintenance_work_mem
, as RAM GBs times 64MB,effective_cache_size
, as 0.75*RAM size.
postgresql.conf.j2
are below:
max_connections = max_connections
shared_buffers = (((ansible_memtotal_mb/1024.0) round int)*0.25) int*1024 MB
work_mem = ((((ansible_memtotal_mb/1024.0) round int)*0.25)/max_connections*1024) round int MB
maintenance_work_mem = ((ansible_memtotal_mb/1024.0) round int)*64 MB
effective_cache_size = (((ansible_memtotal_mb/1024.0) round int)*0.75) int*1024 MB
max_connections
to you specific needs. An example playbook could look like:
---
hosts: my_postgres
roles:
- postgres-config
vars:
- max_connection: 300
$ ansible-playbook playbook.yml