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
Hi,
I am in two minds of what to write about Doha. My job has been vastly simplified by a friend when he shared with me 

Here is my monthly update covering a large part of what I have been doing in the free software world (
The last day of TUG 2016, or at least the last day of talks, brought four one-hour talks from special guests, and several others, where many talks told us personal stories and various histories.
Excellent life music in a bit schick/sophisticated atmosphere was a good finish for this excellent day. With Herb from MacTeX and his wife we killed two bottles of red wine, before slowly tingling back to the hotel.
A great finishing day of talks.

After the closing of the session, one part of our group went off to the baseball match, while another group dived into a Japanese-style Izakaya where we managed to kill huge amounts of sake and quite an amount of food. The photo shows me after the first bottle of sake, while just seeping on an intermediate small amount of genshu (kind of strong undiluted sake) before continuing to the next bottle.
An interesting and stimulating first day of TUG, and I am sure that everyone was looking forward to day 2.
Version 0.0.5 of
I write free software and I have some users. My primary support
channels are over email and IRC, which means I do not have direct
access to the system where my software runs. When one of my users has
a problem, we go through one or more cycles of them reporting what
they see and me asking them for more information, or asking them to
try this thing or that thing and report results. This can be quite
frustrating.
I want, nay, need to improve this. I've been thinking about this for a
while, and talking with friends about it, and here's my current ideas.
First idea: have a script that gathers as much information as
possible, which the user can run. For example, log files, full
configuration, full environment, etc. The user would then mail the
output to me. The information will need to be anonymised suitably so
that no actual secrets are leaked. This would be similar to Debian's
package specific reportbug scripts.
Second idea: make it less likely that the user needs help solving
their issue, with better error messages. This would require error
messages to have sufficient explanation that a user can solve their
problem. That doesn't necessarily mean a lot of text, but also code
that analyses the situation when the error happens to include things
that are relevant for the problem resolving process, and giving error
messages that are as specific as possible. Example: don't just fail
saying "write error", but make the code find out why writing caused an
error.
Third idea: in addition to better error messages, might provide
diagnostics tools as well.
A friend suggested having a script that sets up a known good set of
operations and verifies they work. This would establish a
known-working baseline, or smoke test, so that we can rule things like
"software isn't completely installed".
Do you have ideas? Mail me (liw@liw.fi) or tell me on identi.ca (@liw)
or Twitter (@larswirzenius).

This series has focused on new projects. I could have said more about
significant work that didn't involve starting new projects. A big example was
when I added
A while back, I found myself in need of two webservers that would terminate TLS (with different rules). I wanted to run some custom code I d written (which uses TLS peer authentication), and also nginx on port 443.
The best way I figured out how to do this was to write a tool to sit on port 443, and parse TLS Client Hello packets, and dispatch to the correct backend depending on the SNI name.
SNI, or Server Name Indication allows the client to announce (yes over cleartext!) what server it s looking for, similar to the HTTP Host header. Sometimes, like in the case above, the Host header won t work, since you ve already done a TLS handshake by the time you figure out who they re looking for.
I also spun the Client Hello parser out into its own importable package, just in case someone else finds themselves in this same boat.
The code s up on
My experiences with Amazon reviewing have been somewhat unusual. A review of a






