Ansible playbook for PostgreSQL setup

postgresql.yml

---
- name: install PostgreSQL
  apt: pkg={{ item }} update_cache=yes state=present
  sudo: yes
  tags: postgres
  with_items:
  - postgresql-{{ postgresql_version }}
  - postgresql-client-{{ postgresql_version }}
  - postgresql-contrib-{{ postgresql_version }}
  - libpq-dev

- name: pin PostgreSQL dependencies (libpq-dev) version to prevent unintended upgrades
  shell: echo "libpq-dev hold" | dpkg --set-selections
  sudo: yes
  tags: postgres

- name: Configure PostgreSQL. Set listen_addresses.
  lineinfile: dest=/etc/postgresql/{{ postgresql_version }}/main/postgresql.conf
    regexp="listen_addresses =" line="listen_addresses = '*'" state=present
  notify: restart postgresql
  sudo: yes
  tags: postgres

- name: Configure PostgreSQL. Set max_connection.
  lineinfile: dest=/etc/postgresql/{{ postgresql_version }}/main/postgresql.conf
    regexp="max_connections =" line="max_connections = 1000" state=present
  notify: restart postgresql
  sudo: yes
  tags: postgres

- name: Configure PostgreSQL. Set max_connection.
  lineinfile: dest=/etc/postgresql/{{ postgresql_version }}/main/postgresql.conf
    regexp="ssl =" line="ssl = false" state=present
  notify: restart postgresql
  sudo: yes
  tags: postgres

- name: Configure PostgreSQL. Set autovacuum.
  lineinfile: dest=/etc/postgresql/{{ postgresql_version }}/main/postgresql.conf
    regexp="autovacuum =" line="autovacuum = on" state=present
  notify: restart postgresql
  sudo: yes
  tags: postgres

- name: Configure PostgreSQL. Set hosts in pg_hba.conf
  template: src=pg_hba.conf.j2 dest=/etc/postgresql/{{ postgresql_version }}/main/pg_hba.conf
    backup=yes
  sudo: yes
  tags: postgres
  notify: restart postgresql

- name: Install psycopg2
  pip: name=psycopg2
  sudo: yes
  tags: postgres

- name: Create PostgreSQL DB
  postgresql_db: name={{ postgresql_db_name }}
  sudo: yes
  sudo_user: postgres
  tags: postgres

- name: Create PostgreSQL user and grant access
  postgresql_user: db={{ postgresql_db_name }} user={{ postgresql_db_user }} password={{ postgresql_db_user_password }}
  sudo: yes
  sudo_user: postgres
  tags: postgres

- name: forward mail to root
  lineinfile: 'dest=/etc/aliases regexp="^postgres: root" line="postgres: root"'
  when: no_email is not defined
  sudo: yes
  tags: postgres
  notify:
  - restart postfix
  - reload mail aliases

- name: Put DB restore script into home
  template: src=pg_restore_last_backup.sh dest=~/pg_restore_last_backup.sh mode=0755
  tags: postgres

thank you for sharing this.