Automating PostgreSQL installation using Ansible

Following simple ansible playbook allows you to install the PostgreSQL on CentOS/RHEL, clean up the data directory, initialize the DB, Add the PostgreSQL as a service, enable local login, create SQL user, schema and DB.

I have parameterized following configurations which sits on group_vars/all.yml

remote_ssh_user: your_ssh_user
postgres_repo: https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
sql_user: your_sql_user
sql_passwd: passwd_you_want_to_setup
sql_schema: schema_name_to_be_added

Following is my main.yml for the postgres role which sits on roles/postgres/tasks/main.yml

- name: Install downlaod/file plugins
  yum: name={{ item }} state=present
  become: true
  with_items:
        - wget
        - python-psycopg2

- name: add repository  (CentOS6/CentOS7)
  yum: name="{{postgres_repo}}" state=present

- name: install postgres server 9.6
  yum: name=postgresql96-server state=present 

- name: Clean the data directory
  file:
    state: absent
    path: /var/lib/pgsql/9.6/data/

- name: initialize the DB
  command: /usr/pgsql-9.6/bin/postgresql96-setup initdb

- name: Add to startup
  command: systemctl enable postgresql-9.6.service

- name: start the service
  command: systemctl start postgresql-9.6.service

- name: allow local login
  lineinfile:
    path: /var/lib/pgsql/9.6/data/pg_hba.conf
    line: 'host    all             all             127.0.0.1/32            md5'
    regexp  : '^host( )*all( )*all( )*127\.0\.0\.1\/32( )*ident*'
    state: present

- name: allow local login
  lineinfile:
    path: /var/lib/pgsql/9.6/data/pg_hba.conf
    line: 'host    all             all             ::1/128            md5'
    regexp  : '^host( )*all( )*all( )*::1/128 ( )*ident*'
    state: present

- name: Re-start the service
  command: systemctl restart postgresql-9.6.service

- postgresql_user:
    name: "{{sql_user}}"
    password:  "{{sql_passwd}}"
  become: true
  become_user: postgres

- postgresql_schema:
    name: "{{sql_schema}}"
    owner: "{{sql_user}}"
  become: true
  become_user: postgres

- postgresql_db:
    name: "{{sql_schema}}"
    owner: "{{sql_user}}"
  become: true
  become_user: postgres

I can now call the postgres role from my play book which is saved as install_postgresql.yml

# role for postgres
- hosts: localhost
  remote_user: "{{remote_ssh_user}}"
  roles:
     - { role: postgres } 

Runnig your playbook

ansible-playbook install_postgresql.yml