This playbook installs PostgreSQL using distro-aware package handling and applies a minimal service baseline with current best practices for DevOps environments.
---
- name: Install and configure PostgreSQL
hosts: postgres
become: true
vars:
postgresql_version: "18"
app_packages_debian:
- "postgresql-{{ postgresql_version }}"
- "postgresql-client-{{ postgresql_version }}"
- "postgresql-contrib-{{ postgresql_version }}"
app_packages_redhat:
- "postgresql{{ postgresql_version }}-server"
- "postgresql{{ postgresql_version }}-contrib"
# Database configuration variables
postgresql_databases:
- name: appdb
owner: appuser
postgresql_users:
- name: appuser
password: "{{ appuser_db_password }}" # Use Ansible Vault
priv: "ALL"
pre_tasks:
- name: Update apt cache if needed (Debian/Ubuntu)
apt:
update_cache: yes
cache_valid_time: 3600
when: ansible_os_family == "Debian"
tasks:
- name: Install PostgreSQL packages on Debian family
apt:
name: "{{ app_packages_debian }}"
state: present
update_cache: true
when: ansible_os_family == "Debian"
notify: restart postgresql
- name: Install PostgreSQL packages on RedHat family
dnf:
name: "{{ app_packages_redhat }}"
state: present
when: ansible_os_family == "RedHat"
notify: restart postgresql
- name: Initialize PostgreSQL database on RHEL-family systems
command: /usr/pgsql-{{ postgresql_version }}/bin/postgresql-{{ postgresql_version }}-setup initdb
args:
creates: /var/lib/pgsql/{{ postgresql_version }}/data/postgresql.conf
when: ansible_os_family == "RedHat"
notify: restart postgresql
- name: Enable and start PostgreSQL service
systemd:
name: postgresql
state: started
enabled: true
daemon_reload: yes
- name: Ensure PostgreSQL is running
command: pg_isready
register: pg_status
until: pg_status.rc == 0
retries: 5
delay: 5
changed_when: false
- name: Create application database
become: yes
become_user: postgres
postgresql_db:
name: "{{ item.name }}"
owner: "{{ item.owner }}"
loop: "{{ postgresql_databases }}"
notify: restart postgresql
- name: Create application user
become: yes
become_user: postgres
postgresql_user:
name: "{{ item.name }}"
password: "{{ item.password }}"
priv: "{{ item.priv }}"
state: present
loop: "{{ postgresql_users }}"
no_log: true # Prevents password from appearing in logs
- name: Validate binary availability
command: psql --version
register: app_version
changed_when: false
failed_when: false
- name: Show version/check output
debug:
msg: "PostgreSQL version: {{ app_version.stdout }}"
handlers:
- name: restart postgresql
systemd:
name: postgresql
state: restarted
For more complex setups, use a dedicated role structure:
---
# File: roles/postgresql/tasks/main.yml
- name: Include OS-specific variables
include_vars: "{{ ansible_os_family }}.yml"
- name: Import setup tasks
import_tasks: setup.yml
- name: Import configuration tasks
import_tasks: config.yml
- name: Import security tasks
import_tasks: security.yml
- name: Import database creation tasks
import_tasks: databases.yml
roles/postgresql/
├── defaults/main.yml
├── files/
├── handlers/
│ └── main.yml
├── meta/
│ └── main.yml
├── tasks/
│ ├── config.yml
│ ├── databases.yml
│ ├── main.yml
│ ├── security.yml
│ └── setup.yml
├── templates/
│ ├── postgresql.conf.j2
│ └── pg_hba.conf.j2
└── vars/
├── Debian.yml
└── RedHat.yml
# Create encrypted variables file
ansible-vault create roles/postgresql/vars/secrets.yml
# Edit the encrypted file
ansible-vault edit roles/postgresql/vars/secrets.yml
# Example content of secrets.yml:
# appuser_db_password: "secure_password_here"
# postgres_admin_password: "admin_password_here"
Run the playbook with vault password:
ansible-playbook -i inventory postgresql-playbook.yml --ask-vault-pass
# File: inventory/production/hosts.ini
[postgres_servers]
db1.example.com
db2.example.com
[postgres_servers:vars]
postgresql_version="18"
postgresql_max_connections=200
postgresql_shared_buffers="2GB"
# Add to your playbook for rolling updates
serial: 1 # Update one host at a time
Include validation tasks to ensure proper setup:
- name: Validate PostgreSQL service status
systemd:
name: postgresql
state: started
enabled: yes
check_mode: yes
register: service_status
- name: Check PostgreSQL connectivity
become: yes
become_user: postgres
command: pg_isready
register: db_connectivity
until: db_connectivity.rc == 0
retries: 10
delay: 5
changed_when: false