I added a new database to my demo platform: Postgres. This code helps me provision Ansible Postgres on Mac for demo purposes or simple functional testing, and it is an extension of previous work I shared: https://valerieparhamthompson.com/posts/string-search/.

The script does a postgres install via Homebrew for Mac M1 and starts it up, then creates the database, user, etc. needed for the demo. Finally, it populates using my “million table” sql.

Most of this uses the Community.Postgres Ansible module found here: https://docs.ansible.com/ansible/latest/collections/community/postgresql/index.html

For the full code, including inventory and group_vars files, see https://github.com/dataindataout/xtest_ansible.

Install Postgres

Assumes you are on Mac. This was the cleanest way I found to install on Mac M1.

- name: Ensure Postgres 14 and dependencies are installed
  hosts: pg1
  tasks:
    - name: Install postgres via homebrew
      community.general.homebrew:
        name: postgresql@14
        path: /opt/homebrew/bin
        state: present
    - name: Install psycopg2
      pip:
        name: psycopg2-binary
        state: present

Start Postgres

I would like to refactor this to parameterize some of the directories.

- name: Start Postgres
  hosts: pg1
  tasks:
    - name: Find out if pg has been initialized
      ansible.builtin.stat:
        path: "/opt/homebrew/var/postgresql@14/pg_hba.conf"
      register: postgres_data
    - name: Initialize PostgreSQL if needed
      shell: "{{ pg_executable_dir }}/pg_ctl initdb --pgdata=/opt/homebrew/var/postgresql@14"
      when: not postgres_data.stat.exists
    - name: Restart postgres
      shell: "{{ pg_executable_dir }}/pg_ctl restart --pgdata=/opt/homebrew/var/postgresql@14 --log=/opt/homebrew/var/postgresql@14/server.log"

Create Ansible Postgres database, user, etc.

Because this is a local demo installation, and not a production installation, I am using my local user instead of the ‘postgres’ user. The ansible_user_id variable is your local user found when gathering facts.

- name: Create postgres assets
  hosts: pg1
  tasks:
    - name: "Create app database"
      postgresql_db:
        state: present
        login_user: "{{ ansible_user_id }}"
        db: postgres
        name: "{{ db_name }}"

    - name: "Create db user"
      postgresql_user:
        state: present
        login_user: "{{ ansible_user_id }}"
        db: postgres
        name: "{{ db_user }}"
        password: "{{ db_password }}"

    - name: "Grant db user access to app db"
      postgresql_privs:
        login_user: "{{ ansible_user_id }}"
        db: postgres
        type: database
        database: "{{ db_name }}"
        roles: "{{ db_user }}"
        grant_option: no
        privs: all

    - name: "Allow md5 connection for the db user"
      postgresql_pg_hba:
        dest: "/opt/homebrew/var/postgresql@14/pg_hba.conf"
        contype: host
        databases: all
        method: md5
        users: "{{ db_user }}"
        create: true

    - name: Restart postgres
      shell: "{{ pg_executable_dir }}/pg_ctl restart --pgdata=/opt/homebrew/var/postgresql@14 --log=/opt/homebrew/var/postgresql@14/server.log"

Populate data using Ansible Postgres

If this was a larger population script, or if I was loading from a database dump, I’d do something else, but this was a simple way to run the handful of queries to populate the generated million table.

- name: Populate data on postgres instance
  hosts: pg1
  tasks:
    - name: Load sql script
      community.postgresql.postgresql_query:
        login_user: "{{ ansible_user_id }}"
        db: "{{ db_name }}"
        query:
          - create table if not exists milliontable(name varchar(10), age integer)
          - truncate table milliontable
          - insert into milliontable (name, age) select substr(md5(random()::text), 1, 10), (random() * 70 + 10)::integer from generate_series(1, 1000000)