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.
Add a comment
|
2 Answers
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
1 Comment
Anthony Petrillo
Thank you. I'm new to Ansible and find myself thinking in older forms, such as querying the database directly. I appreciate you pointing this out so I can learn more ways to use Ansible.
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.
1 Comment
Zeitounator
It's good you found how to turn on autocommit if you need it in other situation. Meanwhile, this is probably a good example of the xy problem. Your real question IMO is "How to create a postgresql database from ansible". And I have given an answer to that. Side note: this should also teach you that you should read docs before diving in SO.