jira_remove_stale_node.yml

---
 
- name: Check if this is a new cluster that has not been initialized
  stat:
    path: "{{ atl_product_home_shared }}/export/indexsnapshots"
  register: snapshotfolder 
 
- name: Remove offline jira nodes from clusternode table
  postgresql_query:
    login_host: "{{ atl_db_host }}"
    port: "{{ atl_db_port }}"
    login_user: "{{ atl_db_root_user }}"
    login_password: "{{ atl_db_root_password }}"
    db: "{{ atl_jdbc_db_name }}"
    query: DELETE FROM clusternode WHERE node_state='OFFLINE'
  when: snapshotfolder.stat.exists  
 
- name: Remove no heartbeat record jira node from clusternode table
  postgresql_query:
    login_host: "{{ atl_db_host }}"
    port: "{{ atl_db_port }}"
    login_user: "{{ atl_db_root_user }}"
    login_password: "{{ atl_db_root_password }}"
    db: "{{ atl_jdbc_db_name }}"
    query: DELETE FROM clusternode AS HB WHERE HB.node_id NOT IN (SELECT node_id from clusternodeheartbeat)
  when: snapshotfolder.stat.exists  
 
- name: Remove offline jira nodes from clusternodeheartbeat table
  postgresql_query:
    login_host: "{{ atl_db_host }}"
    port: "{{ atl_db_port }}"
    login_user: "{{ atl_db_root_user }}"
    login_password: "{{ atl_db_root_password }}"
    db: "{{ atl_jdbc_db_name }}"
    query: DELETE FROM clusternodeheartbeat WHERE heartbeat_time=0
  when: snapshotfolder.stat.exists  
 
- name: Find jira nodes which heartbeat timestamp are older than delete_threshold_hours
  postgresql_query:
    login_host: "{{ atl_db_host }}"
    port: "{{ atl_db_port }}"
    login_user: "{{ atl_db_root_user }}"
    login_password: "{{ atl_db_root_password }}"
    db: "{{ atl_jdbc_db_name }}"
    query: SELECT node_id FROM clusternodeheartbeat WHERE %(current_epoch_time_ms)s - heartbeat_time > %(delete_threshold_ms)s
    named_args:
      current_epoch_time_ms: "{{ current_epoch_time_sec | int * 1000}}"
      delete_threshold_ms: "{{ delete_threshold_hours | int * 3600 * 1000 }}"
  register: old_heartbeat_nodes
  when: snapshotfolder.stat.exists
 
- name: Delete jira nodes which heartbeat timestamp are older than delete_threshold_hours from clusternode table
  postgresql_query:
    login_host: "{{ atl_db_host }}"
    port: "{{ atl_db_port }}"
    login_user: "{{ atl_db_root_user }}"
    login_password: "{{ atl_db_root_password }}"
    db: "{{ atl_jdbc_db_name }}"
    query: "DELETE FROM clusternode WHERE node_id = '{{ item.node_id }}'"
  with_items: "{{ old_heartbeat_nodes.query_result }}"
  when: snapshotfolder.stat.exists and old_heartbeat_nodes.rowcount > 0
 
- name: Delete jira nodes which heartbeat timestamp are older than delete_threshold_hours from clusternodeheartbeat table
  postgresql_query:
    login_host: "{{ atl_db_host }}"
    port: "{{ atl_db_port }}"
    login_user: "{{ atl_db_root_user }}"
    login_password: "{{ atl_db_root_password }}"
    db: "{{ atl_jdbc_db_name }}"
    query: "DELETE FROM clusternodeheartbeat WHERE node_id = '{{ item.node_id }}'"
  with_items: "{{ old_heartbeat_nodes.query_result }}"
  when: snapshotfolder.stat.exists and old_heartbeat_nodes.rowcount > 0
 
- name: Delete jira nodes which heartbeat timestamp are older than delete_threshold_hours from replicatedindexoperation table
  postgresql_query:
    login_host: "{{ atl_db_host }}"
    port: "{{ atl_db_port }}"
    login_user: "{{ atl_db_root_user }}"
    login_password: "{{ atl_db_root_password }}"
    db: "{{ atl_jdbc_db_name }}"
    query: "DELETE FROM replicatedindexoperation WHERE node_id = '{{ item.node_id }}'"
  with_items: "{{ old_heartbeat_nodes.query_result }}"
  when: snapshotfolder.stat.exists and old_heartbeat_nodes.rowcount > 0