DEV Community

Cover image for PostgreSQL 16 Installation with Ansible (and Custom Configs)
Athreya aka Maneshwar
Athreya aka Maneshwar

Posted on

PostgreSQL 16 Installation with Ansible (and Custom Configs)

#programming #webdev #beginners #devops

Hi there! I'm Maneshwar. Right now, I’m building LiveAPI, a first-of-its-kind tool that helps you automatically index API endpoints across all your repositories. LiveAPI makes it easier to discover, understand, and interact with APIs in large infrastructures.

Need to automate PostgreSQL 16 installation and make sure it ships with your exact postgresql.conf and pg_hba.conf? Here’s how to set it up like a dev who’s tired of ssh and vim.

Directory Structure

We’re working with a typical Ansible role structure. Your roles/db folder looks like this:

roles/db/
├── tasks/
│   ├── main.yml
│   └── install_postgres.yml
├── templates/
│   ├── pg_hba.conf.j2
│   └── postgresql.conf.j2
Enter fullscreen mode Exit fullscreen mode

Inventory

hosts.ini example:

[db]
my-postgres-host ansible_host=192.168.0.10 ansible_user=ubuntu
Enter fullscreen mode Exit fullscreen mode

db.yml (Entry point) 

- name: Setup PostgreSQL
  hosts: db
  become: yes
  roles:
    - db
Enter fullscreen mode Exit fullscreen mode

roles/db/tasks/main.yml 

---
- include_tasks: install_postgres.yml
Enter fullscreen mode Exit fullscreen mode

roles/db/tasks/install_postgres.yml 

---
- name: Update apt cache
  apt:
    update_cache: yes

- name: Install postgresql-common
  apt:
    name: postgresql-common
    state: present

- name: Run pgdg script
  command: /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
  args:
    creates: /etc/apt/sources.list.d/pgdg.list

- name: Update apt cache after adding PGDG
  apt:
    update_cache: yes

- name: Install PostgreSQL 16 and contrib
  apt:
    name:
      - postgresql-16
      - postgresql-contrib-16
    state: present

- name: Set custom postgresql.conf
  template:
    src: postgresql.conf.j2
    dest: /etc/postgresql/16/main/postgresql.conf
    owner: postgres
    group: postgres
    mode: 0644
  notify: Restart PostgreSQL

- name: Set custom pg_hba.conf
  template:
    src: pg_hba.conf.j2
    dest: /etc/postgresql/16/main/pg_hba.conf
    owner: postgres
    group: postgres
    mode: 0640
  notify: Restart PostgreSQL
Enter fullscreen mode Exit fullscreen mode

roles/db/handlers/main.yml 

---
- name: Restart PostgreSQL
  service:
    name: postgresql
    state: restarted
Enter fullscreen mode Exit fullscreen mode

roles/db/templates/postgresql.conf.j2

Minimal working config (customize as needed):

listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 128MB
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql.log'
Enter fullscreen mode Exit fullscreen mode

roles/db/templates/pg_hba.conf.j2

Allow local and password-based remote access:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
Enter fullscreen mode Exit fullscreen mode

Run it 

ansible-playbook -i hosts.ini db.yml
Enter fullscreen mode Exit fullscreen mode

Bonus Tip

To reset the password for postgres user after install:

- name: Set postgres user password
  become_user: postgres
  shell: psql -c "ALTER USER postgres WITH PASSWORD '{{ postgres_password }}';"
Enter fullscreen mode Exit fullscreen mode

Define postgres_password in roles/db/vars/main.yml.

That’s it — your infra now auto-installs PostgreSQL 16 with config hardcoded to your needs.

LiveAPI helps you get all your backend APIs documented in a few minutes.

With LiveAPI, you can generate interactive API docs that allow users to search and execute endpoints directly from the browser.

LiveAPI Demo

If you're tired of updating Swagger manually or syncing Postman collections, give it a shot.

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.