2

I'm finding lots of examples of how to manage posgresql with ansible (Linux), however, all of them contain the most basic things (install package, start service, create db, create user), but not the most important thing: create the tables.

I have a SQL file with the tables for my database but I don't know how to properly use it as an input in my ansible role. I'm hoping I don't need to use the shell module to just execute the SQL, because as far as I understand that's a no-no in the ansible world (especially because we may need a way first to check if the tables have already been created in a previous run).

How to do this? Any sample out there? Cheers

3

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.

|improve this answer|||||
1

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.