# Ansible idempotency **URL:** \ **Category:** Get Help\ **Tags:** git, gcp, ansible\ **Created:** [September 12, 2024, 12:00pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462 "2024-09-12T12:00:33Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Chandra\_Mukherjee](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chandra_mukherjee/32/4685_2.png) [@Chandra\_Mukherjee](https://forum.ansible.com/u/Chandra_Mukherjee)\ **Post date:** [September 12, 2024, 12:00pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/1 "2024-09-12T12:00:33Z")
I have a use case to execute DDL scripts on a GCP cloud SQL instance using Ansible. Below is the source code of the playbook: - name: Check postgreSQL version hosts: localhost gather\_facts: no become: yes The code is working fine but i want to execute the playbook for only those files which were modified in the git commit. How do i achieve this? ---
**Author:** ![Denney-tech](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/denney-tech/32/1556_2.png) [@Denney-tech](https://forum.ansible.com/u/Denney-tech)\ **Post date:** [September 12, 2024, 2:52pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/2 "2024-09-12T14:52:53Z")
Please try to format your posts with code blocks to help with readability of your code. Use triple backticks: ``` to start and end a code block. You can also override formatting by explicitly specifying the language on the same line as the first triple backtick. * * * First, I want to mention that you can simplify your playbook a little bit by consolidating the apt installs to one task: ```yaml - name: Check postgreSQL version hosts: localhost gather_facts: no become: yes tasks: - name: Install required packages apt: name: - python3 - python3-pip - libpq-dev - python3-psycopg2 ``` * * * Now, you want to execute this playbook anytime that there’s a new git commit? You need to figure out how to trigger the ansible-playbook with git webhooks. If you’re using AWX, it would be straightforward to simply call a Job Template via git webhook. Or do you mean that the \*.sql scripts are in git, and you only want to run scripts that were recently modified? _If that’s the case_, then you might be able to use the `age` parameter in `find:` to run only scripts under a certain age (less than the frequency you run the playbook in a cronjob). An important mention here is that the `age_stamp` parameter defaults to `mtime` (modified time), but also has options for creation time and access time. Continuing your play: ```yaml - name: List SQL files to be executed find: paths: DDL/ file_type: file recurse: yes patterns: “*.sql” age: "-1d" register: files_matched - name: Connect to SQL instance ignore_errors: true loop: “{{ files_matched.files|flatten(levels=1) }}” loop_control: label: “{{ item.path }}” community.postgresql.postgresql_script: db: “{{ db_name }}” login_host: “{{ ansible_host }}” login_password: “{{ db_password }}” login_user: “{{ db_user }}” port: “5432” path: “{{ item.path }}” encoding: UTF-8 when: files_matched.files | length > 0 ``` I don’t know how old you want to consider your files, so I went with “less than 1 day old” (negative values are less than that age, positive values are greater than). I also added a when condition to make sure we only try to run the scripts if we find any valid matches. ---
**Author:** ![Chandra\_Mukherjee](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chandra_mukherjee/32/4685_2.png) [@Chandra\_Mukherjee](https://forum.ansible.com/u/Chandra_Mukherjee)\ **Post date:** [September 12, 2024, 3:29pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/3 "2024-09-12T15:29:55Z")
Hi Danny, I want to execute the playbook for only those .sql files which are modified in the git commit, not based on file age. ---
**Author:** ![Denney-tech](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/denney-tech/32/1556_2.png) [@Denney-tech](https://forum.ansible.com/u/Denney-tech)\ **Post date:** [September 12, 2024, 4:08pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/4 "2024-09-12T16:08:48Z")
I understand that, but I’m not sure how to approach explicitly getting files modified in git. What if multiple commits have been made between syncs? Do you only care about what was modified in the most recent _commit_? Or do you care about _all_ modified files in _all_ commits since the last sync? How do we compile that information sensibly through git? Is it possible? Probably, but that’s beyond my technical expertise. That said, if a file is modified by git, then it will be modified on disk. So if we go by modified file time, this would mean the same scripts could be run multiple times within a certain period, but it would catch _only_ recently git modified files (regardless of commit). Anything older would be skipped, so you would have some degree of compromise between running absolutely all scripts everytime, and only git modified files. ---
**Author:** ![Denney-tech](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/denney-tech/32/1556_2.png) [@Denney-tech](https://forum.ansible.com/u/Denney-tech)\ **Post date:** [September 12, 2024, 4:15pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/5 "2024-09-12T16:15:09Z")
And you can shorten the interval too. Can you guarantee that the playbook will run within a few minutes of each git sync? Then narrow the age window to within a few minutes. You’ll need to have some tolerance so that recently modified files are picked up in a large enough window of time to be run, but simultaneously within a narrow enough window that modified files in previous runs aren’t erroneously picked up again. ---
**Author:** ![Chandra\_Mukherjee](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chandra_mukherjee/32/4685_2.png) [@Chandra\_Mukherjee](https://forum.ansible.com/u/Chandra_Mukherjee)\ **Post date:** [September 12, 2024, 6:47pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/6 "2024-09-12T18:47:21Z")
I will add this playbook in the github actions so it will be immediately executed once the changes are pushed to the branch, however defining the age window will re-execute few of the files if commits are done in a short period of time. I want to ensure the playbook runs only for modified .sql files. Any way to do that? ---
**Author:** ![Denney-tech](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/denney-tech/32/1556_2.png) [@Denney-tech](https://forum.ansible.com/u/Denney-tech)\ **Post date:** [September 12, 2024, 7:06pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/7 "2024-09-12T19:06:44Z")
If it runs every commit via github actions… then maybe you can do something like this: ```auto - name: List SQL files to be executed shell: cmd: git diff-tree HEAD~1 HEAD -r --name-only | grep DDL | grep \.sql$ register: files_matched failed_when: false - name: Connect to SQL instance ignore_errors: true loop: “{{ files_matched.stdout_lines }}” community.postgresql.postgresql_script: db: “{{ db_name }}” login_host: “{{ ansible_host }}” login_password: “{{ db_password }}” login_user: “{{ db_user }}” port: “5432” path: “{{ item }}” encoding: UTF-8 when: files_matched.stdout_lines | length > 0 ``` The idea is to get only the changed filenames between the two most recent commits, and filter them just like before. You may need to specify the working directory or otherwise adjust the shell command a bit. I don’t have enough experience with GitHub Actions to know if there’s any gotchas to worry about or if there’s any “magic” that could be leveraged from the GitHub runners to make this better. ---
**Author:** ![system](https://us1.discourse-cdn.com/ansible/original/1X/1882d9a01da5d8996a862359b0913cc568a39b84.svg) [@system](https://forum.ansible.com/u/system)\ **Post date:** [October 12, 2024, 7:07pm UTC](https://forum.ansible.com/t/ansible-idempotency/8462/8 "2024-10-12T19:07:14Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.