CODEWITHSUNDEEP
databasepostgresqlansibletransactions
Anthony Petrillo
Anthony Petrillo

Reputation: 515

ansible cannot execute SQL CREATE DATABASE CREATE DATABASE cannot run inside a transaction block

There are several entries that CREATE DATABASE cannot run inside a transaction block which give the answer autocommit needs to be on. However, they do not reference ansible which is what I was looking for.

Upvotes: 0

Views: 925

Answers (2)

Zeitounator
Zeitounator

Reputation: 44760

There is a specific postgresql_db module that will take care of your db creation (or removal/dump/restoration) and will manage idempotency out of the box.

- name: Create database if needed
  postgresql_db:
    name: "{{ dbname }}"
    state: present
  become: yes
  become_user: postgres

Upvotes: 2

Anthony Petrillo
Anthony Petrillo

Reputation: 515

I found in the anisble documentation there is a way to turn autocommit on such as:

- name: create database if needed
  postgresql_query:
    autocommit: yes
    query: |
      CREATE DATABASE {{ dbname }}; 
  become: yes
  become_user: postgres

I thought this would be helpful for people like me who tend to look at stack overflow first when searching for help. Note: {{ dbname }} is a variable. You could also use a literal.

Upvotes: 1

Related Questions