Ansible: Populating variables with results from a database query

In a recent post I explained the use of command substitution in Ansible, focusing on the stdout and stderr attributes when registering variables and using their contents for server configuration. In this post I share an example of populating Ansible variables from a database query that provides status information that could be used to change a setting or remediate a specific condition.

Querying the database

Here we query a PostgreSQL master database that has an associated hot standby to determine its replication synchronisation status. The SQL is as follows:

select sync_state from pg_stat_replication;

The playbook

If the status is “async” the playbook debug task will fail and echo the current status in a debug message “The standby database is in async replication mode”.

---
- hosts: primary1

pre_tasks:
 - assert: { that: "{{item}} is defined" }
 with_items:
 - db_name
 - db_port

tasks:

- name: Perform database replication sync status check
 shell: >
 psql -p {{db_port}} -d {{db_name}} -U postgres -c "select sync_state from pg_stat_replication;"
 become: true
 become_user: postgres
 register: rep_sync_state

# - debug: var=rep_sync_state

- debug: msg="The standby database is in {{ item[2] }} replication mode"
 with_nested: "{{ rep_sync_state.stdout_lines }}"
 failed_when: "'async' in rep_sync_state.stdout"

Note the use of the “with_nested” parameter to initialise an array, which form the rows returned from the query. In this case, we require the 3rd element ( [2] ) to populate our dynamic variable {{ item }}.

The runtime output

The playbook runtime output is displayed below with the query results highlighted in red.

hot-standby-sync_state2

The debug output

The debug output of the rep_sync_state variable (- debug: var=rep_sync_state ) from the above example displays a wealth of information with all available attributes, as shown below.

hot-standby-sync_state

One step further

Taking the concept one step further, we can query more than 1 table, column or attribute to initialise a two-dimensional array. Then use the data to populate variables and display additional status information as shown in the example below.

hot-standby-sync_state3

The playbook

In order to read each element of the array, we must use the following syntax that includes the split function where we specify the delimiter, in this case a pipe ( | ), plus the required element ( [0] ).

{{ item.split('|')[0] }}
---
- hosts: primary1

pre_tasks:
 - assert: { that: "{{item}} is defined" }
 with_items:
 - db_name
 - db_port

tasks:

- name: Perform database replication sync status check
 shell: >
 psql -p {{db_port}} -d {{db_name}} -U postgres -c "select sync_state, state from pg_stat_replication;"
 become: true
 become_user: postgres
 register: rep_sync_state

- debug: var=rep_sync_state.stdout_lines

- debug: msg="The standby database is in {{ item.split('|')[0] }} mode with {{ item.split('|')[1] }} replication"
 with_items: "{{ rep_sync_state.stdout_lines[2] }}"
 failed_when: "'async' in rep_sync_state.stdout"

PostgreSQL

Fortunately the PostgreSQL psql utility has an “expanded output”  ( \x ) feature that formats the output of a query having multiple fields into 1 record. This provides excellent readability in the stdout debug messages and negates the need to select individual elements from the array as previously described. The playbook and runtime output are shown below.

The playbook

---
- hosts: primary1

 pre_tasks:
 - assert: { that: "{{item}} is defined" }
 with_items:
 - db_name
 - db_port

 tasks:

 - name: Perform database replication sync status check
 shell: "{{ item }}"
 with_items:
 - echo "\x" > /tmp/tmp.sql
 - echo "select * from pg_stat_replication;" >> /tmp/tmp.sql
 become: true
 become_user: postgres

 - shell: cat /tmp/tmp.sql | psql -p {{db_port}} -d {{db_name}} -U postgres
 become: true
 become_user: postgres
 register: rep_sync_state

 - file: dest=/tmp/tmp.sql state=absent

 - debug: var=rep_sync_state.stdout_lines

The runtime output

hot-standby-sync_state4

If anyone has a more elegant way of populating variables or sending multiple commands to a PostgreSQL database without writing a new Ansible module, then please leave a comment.

[contact-form][contact-field label=’Name’ type=’name’ required=’1’/][contact-field label=’Email’ type=’email’ required=’1’/][contact-field label=’Website’ type=’url’/][contact-field label=’Comment’ type=’textarea’ required=’1’/][/contact-form]

One thought on “Ansible: Populating variables with results from a database query”

Leave a Reply

Your email address will not be published. Required fields are marked *