AnsibleByExample

Automating PostgreSQL Configuration with Ansible Setting Maximum Connections

BeginnerPostgreSQLAnsibleAutomationDatabase Management
2024-01-017By Luca Berton

Lesson 1 of 15 in Foundations

Ansible Foundations to Production — full curriculum
  1. 1. Introduction to Ansible and Automation
  2. 2. Installing and Configuring Ansible
  3. 3. Running Ad-Hoc Commands
  4. 4. Writing Your First Playbook
  5. 5. Understanding and Using Modules
  6. 6. Variables and Facts
  7. 7. Conditionals and Loops
  8. 8. Handlers and Idempotency
  9. 9. Structuring Projects with Roles
  10. 10. Inventory Management
  11. 11. Using Ansible Vault for Secrets
  12. 12. Debugging and Troubleshooting
  13. 13. Real-World Automation Examples
  14. 14. Final Project: Deploy a Web Server
  15. 15. Bonus - What's Next with Ansible?
View path overview →

Introduction

PostgreSQL is one of the most popular open-source relational databases, powering applications from small startups to enterprise platforms. As your application scales, tuning PostgreSQL configuration becomes critical for performance. The max_connections setting is one of the first parameters administrators need to adjust — and automating this with Ansible ensures consistency across your entire database fleet.

This guide shows how to automate PostgreSQL configuration changes using Ansible's lineinfile module, covering max_connections and other essential tuning parameters.

Why Automate PostgreSQL Configuration?

Manually editing postgresql.conf across multiple servers is:

  • Error-prone — typos can crash PostgreSQL
  • Inconsistent — settings drift between servers over time
  • Slow — editing files on 10+ servers takes significant time
  • Unauditable — no record of who changed what and when

Ansible solves all of these problems by defining configuration as code.

Basic Playbook: Setting max_connections

The simplest playbook to set max_connections:

---
- name: Configure PostgreSQL max connections
  hosts: db_servers
  become: true
  vars:
    postgres_connections: "500"
    postgresql_conf: /etc/postgresql/16/main/postgresql.conf

  tasks:
    - name: Set max number of PostgreSQL connections
      ansible.builtin.lineinfile:
        dest: "{{ postgresql_conf }}"
        regexp: '^#?max_connections\s*=.*$'
        line: "max_connections = {{ postgres_connections }}"
      notify: Restart PostgreSQL

  handlers:
    - name: Restart PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: restarted

Key points:

  • The regexp matches both commented (#max_connections) and uncommented lines
  • The handler restarts PostgreSQL only when the configuration actually changes
  • Using a variable for the config path supports different PostgreSQL versions

Understanding max_connections

The max_connections parameter controls how many simultaneous client connections PostgreSQL accepts. The default is typically 100.

How to Choose the Right Value

FactorConsideration
Available RAMEach connection uses ~5-10 MB of memory
Connection poolingWith PgBouncer, you need fewer PostgreSQL connections
Application needsWeb apps often need 200-500; batch processing may need fewer
Superuser reservedsuperuser_reserved_connections (default 3) are subtracted

Formula: max_connections = (Available RAM - shared_buffers - OS needs) / per_connection_memory

For a server with 16 GB RAM:

max_connections = (16384 MB - 4096 MB - 2048 MB) / 10 MB ≈ 1024

In practice, using connection pooling with PgBouncer, you rarely need more than 200-300 actual PostgreSQL connections.

Complete PostgreSQL Tuning Playbook

A production-ready playbook that configures multiple PostgreSQL parameters:

---
- name: Configure PostgreSQL performance settings
  hosts: db_servers
  become: true
  vars:
    postgresql_version: "16"
    postgresql_conf: "/etc/postgresql/{{ postgresql_version }}/main/postgresql.conf"
    # Connection settings
    postgres_max_connections: "300"
    postgres_superuser_reserved: "3"
    # Memory settings
    postgres_shared_buffers: "4GB"
    postgres_work_mem: "64MB"
    postgres_maintenance_work_mem: "512MB"
    postgres_effective_cache_size: "12GB"
    # WAL settings
    postgres_wal_buffers: "64MB"
    postgres_checkpoint_completion_target: "0.9"
    # Query planner
    postgres_random_page_cost: "1.1"
    postgres_effective_io_concurrency: "200"

  tasks:
    - name: Configure connection settings
      ansible.builtin.lineinfile:
        dest: "{{ postgresql_conf }}"
        regexp: "^#?{{ item.key }}\\s*=.*$"
        line: "{{ item.key }} = {{ item.value }}"
      loop:
        - { key: "max_connections", value: "{{ postgres_max_connections }}" }
        - { key: "superuser_reserved_connections", value: "{{ postgres_superuser_reserved }}" }
      notify: Restart PostgreSQL

    - name: Configure memory settings
      ansible.builtin.lineinfile:
        dest: "{{ postgresql_conf }}"
        regexp: "^#?{{ item.key }}\\s*=.*$"
        line: "{{ item.key }} = {{ item.value }}"
      loop:
        - { key: "shared_buffers", value: "{{ postgres_shared_buffers }}" }
        - { key: "work_mem", value: "{{ postgres_work_mem }}" }
        - { key: "maintenance_work_mem", value: "{{ postgres_maintenance_work_mem }}" }
        - { key: "effective_cache_size", value: "{{ postgres_effective_cache_size }}" }
      notify: Restart PostgreSQL

    - name: Configure WAL settings
      ansible.builtin.lineinfile:
        dest: "{{ postgresql_conf }}"
        regexp: "^#?{{ item.key }}\\s*=.*$"
        line: "{{ item.key }} = {{ item.value }}"
      loop:
        - { key: "wal_buffers", value: "{{ postgres_wal_buffers }}" }
        - { key: "checkpoint_completion_target", value: "{{ postgres_checkpoint_completion_target }}" }
      notify: Restart PostgreSQL

    - name: Configure query planner
      ansible.builtin.lineinfile:
        dest: "{{ postgresql_conf }}"
        regexp: "^#?{{ item.key }}\\s*=.*$"
        line: "{{ item.key }} = {{ item.value }}"
      loop:
        - { key: "random_page_cost", value: "{{ postgres_random_page_cost }}" }
        - { key: "effective_io_concurrency", value: "{{ postgres_effective_io_concurrency }}" }
      notify: Restart PostgreSQL

  handlers:
    - name: Restart PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: restarted

    - name: Reload PostgreSQL
      ansible.builtin.systemd:
        name: postgresql
        state: reloaded

Using the postgresql_set Module

For more robust PostgreSQL configuration, use the community.postgresql.postgresql_set module which modifies settings through SQL:

- name: Set max_connections via SQL
  community.postgresql.postgresql_set:
    name: max_connections
    value: "300"
  become: true
  become_user: postgres
  notify: Restart PostgreSQL

This approach:

  • Validates the parameter name and value
  • Reports whether the setting actually changed
  • Indicates if a restart is required

Dynamic Configuration Based on Server Resources

Calculate settings based on available memory:

- name: Calculate PostgreSQL settings based on RAM
  ansible.builtin.set_fact:
    postgres_shared_buffers: "{{ (ansible_memtotal_mb * 0.25) | int }}MB"
    postgres_effective_cache_size: "{{ (ansible_memtotal_mb * 0.75) | int }}MB"
    postgres_work_mem: "{{ ((ansible_memtotal_mb * 0.25) / 300) | int }}MB"
    postgres_max_connections: "{{ 300 if ansible_memtotal_mb >= 8192 else 100 }}"

- name: Apply calculated settings
  ansible.builtin.lineinfile:
    dest: "{{ postgresql_conf }}"
    regexp: "^#?{{ item.key }}\\s*=.*$"
    line: "{{ item.key }} = {{ item.value }}"
  loop:
    - { key: "shared_buffers", value: "{{ postgres_shared_buffers }}" }
    - { key: "effective_cache_size", value: "{{ postgres_effective_cache_size }}" }
    - { key: "work_mem", value: "{{ postgres_work_mem }}" }
    - { key: "max_connections", value: "{{ postgres_max_connections }}" }
  notify: Restart PostgreSQL

Configuration File Paths by OS

DistributionPostgreSQL Config Path
Ubuntu/Debian/etc/postgresql/{version}/main/postgresql.conf
RHEL/CentOS/Rocky/var/lib/pgsql/{version}/data/postgresql.conf
Amazon Linux/var/lib/pgsql/{version}/data/postgresql.conf
Arch Linux/var/lib/postgres/data/postgresql.conf

Handle this in your playbook:

- name: Set PostgreSQL config path
  ansible.builtin.set_fact:
    postgresql_conf: >-
      {{ '/etc/postgresql/' ~ postgresql_version ~ '/main/postgresql.conf'
         if ansible_os_family == 'Debian'
         else '/var/lib/pgsql/' ~ postgresql_version ~ '/data/postgresql.conf' }}

Verifying Configuration Changes

After applying changes, verify they took effect:

- name: Verify max_connections setting
  community.postgresql.postgresql_query:
    query: "SHOW max_connections"
  become: true
  become_user: postgres
  register: max_conn_result

- name: Display current max_connections
  ansible.builtin.debug:
    msg: "Current max_connections: {{ max_conn_result.query_result[0].max_connections }}"

Common Mistakes

Forgetting to Restart PostgreSQL

Most PostgreSQL settings (including max_connections and shared_buffers) require a restart, not just a reload. Use handlers to ensure the restart happens.

Setting max_connections Too High

Setting max_connections = 5000 without adequate RAM will cause PostgreSQL to refuse to start or run out of memory:

FATAL: could not map anonymous shared memory: Cannot allocate memory

Not Matching the Regex

If your regexp doesn't match the existing line, lineinfile will append the setting, creating a duplicate. Always match both commented and uncommented variants:

regexp: '^#?\s*max_connections\s*=.*$'

Best Practices

  1. Use connection pooling — PgBouncer or pgpool-II reduce the need for high max_connections
  2. Size shared_buffers to 25% of RAM — The standard recommendation for dedicated database servers
  3. Test changes in staging first — Wrong memory settings can prevent PostgreSQL from starting
  4. Use handlers, not inline restarts — Handlers run once at the end, avoiding multiple restarts
  5. Back up postgresql.conf before changes — Add a backup task before modifying the config
  6. Use variables for all values — Makes it easy to customize per environment via group_vars

Conclusion

Automating PostgreSQL configuration with Ansible transforms database tuning from a manual, error-prone process into a repeatable, version-controlled operation. Starting with max_connections and expanding to memory settings, WAL configuration, and query planner parameters ensures your PostgreSQL instances are consistently optimized across your entire infrastructure.

The key is to combine lineinfile for simple text changes with proper handlers for service restarts, and to calculate settings dynamically based on each server's available resources.

All Tutorials