postgresql+ansible: how to create tables?

Solution 1:

Usually it is an application's task to manage tables inside a given database(schema), that's why there are no modules to work with tables from Ansible.

You are correct that you should avoid shell module if native module for the task in question exists. But if there is no such module, shell is an OK choice.

You can check this answer https://stackoverflow.com/a/39117576/2795592 to get an idea of how to create idempotent script that creates something inside postgres.

Solution 2:

As of now (01/2020) there is a module available:

https://docs.ansible.com/ansible/latest/modules/postgresql_table_module.html
(but it depends on python package "psycopg2" or alternative "psycopg2-binary" that you have to install beforehand)

e.g.:

- name: Ensure db table for temp is present
  postgresql_table:
    db: myDatabase
    table: myTable
    columns:
    - someColumn TEXT COLLATE "C" NOT NULL
    - anotherColumn TEXT COLLATE "C" PRIMARY KEY
    - bla BYTEA PRIMARY KEY
    - CONSTRAINT  mykey PRIMARY KEY (anotherColumn, bla)

- name: Ensure temp-db-index is present
  postgresql_idx:
    db: myDatabase
    table: myTable
    columns: anotherColumn
    name: anotherColumn_idx